48 Second Query Takes 40+ minutes To Display in Report Manager

I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
limitation, but here's the situation.
I have a report based on a single query that accepts 3 parameters:
1) Company (0/1)
2) Month (Date)
3) Retrieve either YTD or Monthly values (0/1)
Executing the query in Query Analyzer using parameters that would be the
worst case scenario from a processing stand point, it takes 48 seconds to
execute, and returns 2,054 rows (by 15 columns). Using parameters for the
best case scenario, it takes about 2-4 seconds, and returns 38 rows.
I can easily execute the best case scenario in SRS to display the report,
and it takes about 5-7 seconds to render as a web page. I continue to
time-out on the worst case scenario report, even after setting the timeout
value to 40 minutes in Site Settings in Report Manager.
Help! We've recommended the SRS architecture to the customer as a solution
over Crystal Reports. It now appears that large (even though it's only 2000
rows) result sets using 2+ groupings is causing SRS to seize in rendering the
report.
Any ideas or suggestions as to how to troubleshoot this?
Thanks!please post ddl, your query etc to give us an idea on how to replicate your
problem. you don't give us much to go on. returning 2,000 + rows is not an
issue in reporting services.
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK, thanks Mike. I'll include the RDL file, the script for the source tables,
and the query. If you offer any tips for trouble shooting this, that would be
great. I can't believe that a 48 second query takes 40 minutes with SRS.
Link To Download Here (very small file):
http://home.comcast.net/~wcsmith/KM/SRS.zip
Thanks!!!
"mike" wrote:
> please post ddl, your query etc to give us an idea on how to replicate your
> problem. you don't give us much to go on. returning 2,000 + rows is not an
> issue in reporting services.
> "Smit-Dog" wrote:
> > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > limitation, but here's the situation.
> >
> > I have a report based on a single query that accepts 3 parameters:
> >
> > 1) Company (0/1)
> > 2) Month (Date)
> > 3) Retrieve either YTD or Monthly values (0/1)
> >
> > Executing the query in Query Analyzer using parameters that would be the
> > worst case scenario from a processing stand point, it takes 48 seconds to
> > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> >
> > I can easily execute the best case scenario in SRS to display the report,
> > and it takes about 5-7 seconds to render as a web page. I continue to
> > time-out on the worst case scenario report, even after setting the timeout
> > value to 40 minutes in Site Settings in Report Manager.
> >
> > Help! We've recommended the SRS architecture to the customer as a solution
> > over Crystal Reports. It now appears that large (even though it's only 2000
> > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > report.
> >
> > Any ideas or suggestions as to how to troubleshoot this?
> >
> > Thanks!|||Hi SmitDog,
Could you please try something? Hard code the values of the parameters in
your query. See if this improves the performance. If it does, please send
me a good repro and I will find out why this is happening. I tried to repro
in house but not been able to yet.
--
| Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
Manager
| thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==| X-WBNR-Posting-Host: 68.61.30.192
| From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
| Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
| Date: Fri, 28 Jan 2005 08:41:05 -0800
| Lines: 27
| Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I don't know if I've quickly hit a SQL Reporting Services (SRS)
architecture
| limitation, but here's the situation.
|
| I have a report based on a single query that accepts 3 parameters:
|
| 1) Company (0/1)
| 2) Month (Date)
| 3) Retrieve either YTD or Monthly values (0/1)
|
| Executing the query in Query Analyzer using parameters that would be the
| worst case scenario from a processing stand point, it takes 48 seconds to
| execute, and returns 2,054 rows (by 15 columns). Using parameters for the
| best case scenario, it takes about 2-4 seconds, and returns 38 rows.
|
| I can easily execute the best case scenario in SRS to display the report,
| and it takes about 5-7 seconds to render as a web page. I continue to
| time-out on the worst case scenario report, even after setting the
timeout
| value to 40 minutes in Site Settings in Report Manager.
|
| Help! We've recommended the SRS architecture to the customer as a
solution
| over Crystal Reports. It now appears that large (even though it's only
2000
| rows) result sets using 2+ groupings is causing SRS to seize in rendering
the
| report.
|
| Any ideas or suggestions as to how to troubleshoot this?
|
| Thanks!
||||BTW, here is the query I'm using as a benchmark. You won't have the data, but
it basically takes 1-3 seconds when using Company = 1 (returning about 40
rows), and about 50 seconds when going against Company = 0 (returning about
2000 rows) in Query Analyzer.
Company = 1 in Report Manager renders in about 5-7 seconds. Company = 0
takes about 45 minutes. FWIW, this is on a 1.3GHz laptop with 512MB RAM,
using SQL Server 2000 (SP3).
Also, the big difference is that Company = 1 only has 59,000 records it's
going against, whereas Company=0 has 2,000,000 records it has to query
against.
exec sp_GetKeyJobSiteIndicatorsData 0, '2004-06-01', 0
"Smit-Dog" wrote:
> OK, thanks Mike. I'll include the RDL file, the script for the source tables,
> and the query. If you offer any tips for trouble shooting this, that would be
> great. I can't believe that a 48 second query takes 40 minutes with SRS.
> Link To Download Here (very small file):
> http://home.comcast.net/~wcsmith/KM/SRS.zip
> Thanks!!!
> "mike" wrote:
> > please post ddl, your query etc to give us an idea on how to replicate your
> > problem. you don't give us much to go on. returning 2,000 + rows is not an
> > issue in reporting services.
> >
> > "Smit-Dog" wrote:
> >
> > > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > > limitation, but here's the situation.
> > >
> > > I have a report based on a single query that accepts 3 parameters:
> > >
> > > 1) Company (0/1)
> > > 2) Month (Date)
> > > 3) Retrieve either YTD or Monthly values (0/1)
> > >
> > > Executing the query in Query Analyzer using parameters that would be the
> > > worst case scenario from a processing stand point, it takes 48 seconds to
> > > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> > >
> > > I can easily execute the best case scenario in SRS to display the report,
> > > and it takes about 5-7 seconds to render as a web page. I continue to
> > > time-out on the worst case scenario report, even after setting the timeout
> > > value to 40 minutes in Site Settings in Report Manager.
> > >
> > > Help! We've recommended the SRS architecture to the customer as a solution
> > > over Crystal Reports. It now appears that large (even though it's only 2000
> > > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > > report.
> > >
> > > Any ideas or suggestions as to how to troubleshoot this?
> > >
> > > Thanks!|||Hey Brad,
I posted a link to the basic files involved a couple of posts up. It's
missing the data, and that may be the deal-breaker in reproducing the
problem. I can supply a link to a DB backup if that would help, or perhaps
you can see something in my query, table structure, or RDL that would cause
this severe degradation in performance.
I'll try hardcoding the parameters and see if that helps.
""Brad Syputa - MS"" wrote:
> Hi SmitDog,
> Could you please try something? Hard code the values of the parameters in
> your query. See if this improves the performance. If it does, please send
> me a good repro and I will find out why this is happening. I tried to repro
> in house but not been able to yet.
> --
> | Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
> Manager
> | thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==> | X-WBNR-Posting-Host: 68.61.30.192
> | From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
> | Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
> | Date: Fri, 28 Jan 2005 08:41:05 -0800
> | Lines: 27
> | Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I don't know if I've quickly hit a SQL Reporting Services (SRS)
> architecture
> | limitation, but here's the situation.
> |
> | I have a report based on a single query that accepts 3 parameters:
> |
> | 1) Company (0/1)
> | 2) Month (Date)
> | 3) Retrieve either YTD or Monthly values (0/1)
> |
> | Executing the query in Query Analyzer using parameters that would be the
> | worst case scenario from a processing stand point, it takes 48 seconds to
> | execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> | best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> |
> | I can easily execute the best case scenario in SRS to display the report,
> | and it takes about 5-7 seconds to render as a web page. I continue to
> | time-out on the worst case scenario report, even after setting the
> timeout
> | value to 40 minutes in Site Settings in Report Manager.
> |
> | Help! We've recommended the SRS architecture to the customer as a
> solution
> | over Crystal Reports. It now appears that large (even though it's only
> 2000
> | rows) result sets using 2+ groupings is causing SRS to seize in rendering
> the
> | report.
> |
> | Any ideas or suggestions as to how to troubleshoot this?
> |
> | Thanks!
> |
>|||One other weird thing that may be related...
Every few days, when running the query in QA using parameters for a worst
case scenerio, it would take 30-35 mintues to run the query. For some reason
it went from under a minute to 30x longer. To find the bottleneck, I
commented out the grouping of CASE statements that would SUM values based on
the code, and used hardcoded values instead. This immediately took the query
(again, in QA) from 30 minutes back down to 20 seconds or so. I slowly went
back to the SP and began to add back the CASE statements one by one, trying
to determine which one was causing the bottleneck. As I added them back, the
query time jumped back up to 48 seconds, very acceptable and suddendly back
to where it was before. Even after adding back all CASE statements, the query
was back down to 48 seconds.
I have no idea as to why this "fixes" the query, and the resulting code is
exactly the same. Perhaps when it "recompiles" it, it's internal algorithm is
fixed? Sorry this is so weird, but it is outside my area of expertise.
FWIW, these are the CASE statements and hardcoded values I toggle between to
"fix" the query.
**************************
sum ( case when ( seg1_code >= 6100 and seg1_code <=6145 ) then Balance end)
as BalanceAdditionalLabor,
sum ( case when ( seg1_code >= 6310 and seg1_code <=6400 ) then Balance end)
as BalanceControllables,
sum ( case when ( seg1_code >= 6001 and seg1_code <= 6008) then Balance end)
as BalanceDirectLabor,
sum ( case when ( ( ( seg1_code >= 6701 and seg1_code <=6785 ) or seg1_code
in ( 6002, 8000, 8005 ) ) ) then Balance end) as BalanceFixed,
sum ( case when ( seg1_code =6201 ) then Balance end) as BalanceMaterials,
sum ( case when ( seg1_code = 6204 ) then Balance end) as BalanceRepairs,
sum ( case when ( seg1_code >= 5001 and seg1_code <= 5999 ) then Balance
end) as BalanceSales,
sum ( case when ( seg1_code =5002 ) then Balance end) as BalanceSpecial,
sum ( case when ( seg1_code = 5100 ) then Balance end) as BalanceSubContractor
--BalanceAdditionalLabor=1,
--BalanceControllables=1,
--BalanceDirectLabor=1,
--BalanceFixed=1,
--BalanceMaterials=1,
--BalanceRepairs=1,
--BalanceSales=1,
--BalanceSpecial=1,
--BalanceSubContractor=1
****************************
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK... I've done some more testing, and this may not be related to SRS, but
rather some weirdness going on with the query itself.
It appears that I need to comment out a set of CASE statements, substitute
them with hardcoded values, execute the query a few times, un-comment the
CASE statements, then the query takes 48 seconds to run instead of 45 minutes.
I have gone through this little exercise dozens of times, and it magically
"fixes" the query everytime. Again, no perceived performance problem when the
query is run against the smaller company (much smaller result set - 40
records), just when going against the company that returns a large record set
(2400 records).
It's almost as if the query processor gets tangled in a funk, and going
through the comment/un-commenting of the CASE statments allows it to
recompile cleanly/correctly.
Unless someone has a clue as to what would be causing this weirdness, or can
see something glaringly wrong with the query, I'm to the point where I
re-write the query to get rid of the case statements altogether.
Any ideas or suggestions?
Thanks!
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||... And I've found out that once I execute the query using Company = 1, then
go back and execute it using Company = 2, the stored procedure is "broke"
again, and takes 40+ minutes to execute. I have to edit the SP, comment out
the CASE statetments, execute it using hardcoded values instead, go back and
un-comment the CASE statements, then using Company = 0 takes 45 seconds.
What the heck is going on here?
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!