Query -- 11 seconds in SSMS but times out after 30 minutes in

We're having a weird problem -- a particular report will time out
after 30 minutes running in Reporting Services. However, if we grab
the query from Profiler and run it in SSMS, it runs in about 11
seconds. The profiler trace shows an error of 2 - Abort.
The query itself is found below -- I won't include DDL because each of
the items in the FROM clause is a view that is very long.
Any explanation as to why the query would take so long when running
from reporting services?
Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
3790). Running on Windows Server 2003 Enterprise x64 SP2.
There are some things I'd like to clean up after seeing it, but
nonetheless it still runs pretty quick in SSMS.
SELECT
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
WHEN 3 THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
, e.SHORT_NAME, e.SSN
, SUM(bck.COST)
, e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
FROM
ahr_custom.sls.nocorp_ckreg ck
inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no = ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no = bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
inner join ahr_custom.sls.nocorp_beneplan b on b.company = bck.company and b.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_employee e on e.company = ck.company and e.emp_no = p.emp_no
inner join ahr_custom.sls.nocorp_empbene eb on bck.company = eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_clients c on ck.company = c.company and c.client_no = e.client_no1
WHERE
bck.PLAN_NO IN (20676)
AND p.COMPANY IN ('OK','CA','C2','CO','TX')
and Left(CONVERT(varchar,ck.ck_date,120),10)
between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
+cast(month(getdate()-20) as varchar(2)),2))+ '-01'
and LEFT( CONVERT(varchar,
ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
GROUP BY
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
, e.SHORT_NAME, e.SSN
,e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
LEFT( CONVERT(varchar,e.terminated, 120), 10) endHow many rows of data is returned?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:ac49df7d-bd90-4f6d-b7c8-44012e8e9bb3@.w56g2000hsf.googlegroups.com...
> We're having a weird problem -- a particular report will time out
> after 30 minutes running in Reporting Services. However, if we grab
> the query from Profiler and run it in SSMS, it runs in about 11
> seconds. The profiler trace shows an error of 2 - Abort.
> The query itself is found below -- I won't include DDL because each of
> the items in the FROM clause is a view that is very long.
> Any explanation as to why the query would take so long when running
> from reporting services?
> Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
> 3790). Running on Windows Server 2003 Enterprise x64 SP2.
> There are some things I'd like to clean up after seeing it, but
> nonetheless it still runs pretty quick in SSMS.
> SELECT
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
> WHEN 3 THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
> b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
> , e.SHORT_NAME, e.SSN
> , SUM(bck.COST)
> , e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
> else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
> FROM
> ahr_custom.sls.nocorp_ckreg ck
> inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no => ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
> inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no => bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
> inner join ahr_custom.sls.nocorp_beneplan b on b.company => bck.company and b.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_employee e on e.company => ck.company and e.emp_no = p.emp_no
> inner join ahr_custom.sls.nocorp_empbene eb on bck.company => eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_clients c on ck.company => c.company and c.client_no = e.client_no1
> WHERE
> bck.PLAN_NO IN (20676)
> AND p.COMPANY IN ('OK','CA','C2','CO','TX')
> and Left(CONVERT(varchar,ck.ck_date,120),10)
> between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
> +cast(month(getdate()-20) as varchar(2)),2))+ '-01'
> and LEFT( CONVERT(varchar,
> ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
> GROUP BY
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
> THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
> , e.SHORT_NAME, e.SSN
> ,e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
> LEFT( CONVERT(varchar,e.terminated, 120), 10) end|||On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How many rows of data is returned?
502 rows|||OK, number of rows is not a problem. My guess is that you are having an
issue with the query plan being different.
First, can you put this in a stored procedure and then call that from both
places and see if that makes a difference?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:90230c5c-f7c7-4592-8b44-83ce3a381729@.s12g2000prg.googlegroups.com...
> On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How many rows of data is returned?
> 502 rows|||> First, can you put this in a stored procedure and then call that from both
> places and see if that makes a difference?
Wow -- good suggestion! That seemed to fix it.
Any ideas why in the world would a different query plan be generated
from Reporting Services than from SSMS? It seems like either A)
Profiler isn't giving me the right query that's being executed via
Reporting Services or B) different query plans are being generated for
the same SQL statement depending on the calling application. Neither
of those make any sense to me.
Thanks again for your help.|||I have seen this with other people but with stored procedures not SQL. If a
stored procedure acts differently then you can add a With Recompile to fix
the problem. I really don't understand how the same SQL can act different
but that is what I thought was happening to you.
Bruce
<stephen.lee.moore@.gmail.com> wrote in message
news:2cd5adad-6a86-414b-9840-2b3b05e58c31@.j44g2000hsj.googlegroups.com...
>> First, can you put this in a stored procedure and then call that from
>> both
>> places and see if that makes a difference?
> Wow -- good suggestion! That seemed to fix it.
> Any ideas why in the world would a different query plan be generated
> from Reporting Services than from SSMS? It seems like either A)
> Profiler isn't giving me the right query that's being executed via
> Reporting Services or B) different query plans are being generated for
> the same SQL statement depending on the calling application. Neither
> of those make any sense to me.
> Thanks again for your help.