SSRS – SQL runs in 1 minute but Reporting Services tuns for 30 minutes
I have a report whose dataset query that takes approximately 1 minute to run in SQL Management Server. That same query in a Reporting Services report takes aver 30 minutes to run. And it is inconsistent in its behavior since sometimes it will never finish running and error out. When it does run and it is rendered. At the first user interaction it errors out regardless.
1) Has anyone run into an issue where the difference between SQL and Reporting Services is this huge?
Software/Hardware used:
1) Has anyone run into an issue where the difference between SQL and Reporting Services is this huge?
Software/Hardware used:
ASKED:
September 14, 2009 4:15 PM
UPDATED:
September 16, 2009 2:57 AM
Answer Wiki:
Run SQL Profiler and see if the query is actually taking longer to run, or if the wait time is all with SSRS creating the report.
When SSRS runs the procedure the ANSI settings than will be different from the ones that SSMS uses which can cause a different execution plan to be generated.
To see all answers submitted to the Answer Wiki: View Answer History.
MR Denny, Thanks for your suggestino. We tried that and found the query to be just as fast. Actually we created some indexes and increased the performance to 10 seconds but it appears to be in teh rendering of the report where it takes forever.
Specifically what we have is a tablix with two levels of grouping before we get to the details.
The report runs of 3 parameters. Beginning and Endding Date for transactions and a multivalue parameter of entities to selct from.
All the entities and transactinos for the complete date range would produce a maximun of 27000 rows to be grouped and sorted.
Well, apparently the issue is truly in the rendering of the data.
We can process all the transaction records for the 16 years for al lentities involved in under one minute with straight SQL.
But as far as allowing enough time to render the tablix grouped report we can only process about two years worth of data before the time expires and the report errors out.
Its just surprising that rendering the report would take that long. Thanks for your input.
The bigger the report, the longer the SSRS engine takes to generate reports. SSRS isn’t the fastest thing in the world when it comes to generating large reports.
If possible schedule the reports to run at night.
You can adjust the timeout in IIS so that IIS doesn’t timeout.