SSRS - SQL runs in 1 minute but Reporting Services tuns for 30 minutes
Home > IT Answers > SQL Server > SSRS - SQL runs in 1 minute but Reporting Ser...
Cvillavicencio
35 pts.
0
Q:
SSRS - SQL runs in 1 minute but Reporting Services tuns for 30 minutes
SSRS, SQL, SQL performance, SQL Server Reporting Services
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?

 

ASKED: Sep 14 2009  4:15 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46810 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Sep 14 2009  5:52 PM GMT by Mrdenny   46810 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Cvillavicencio   35 pts.  |   Sep 14 2009  8:08PM GMT

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.

 

Cvillavicencio   35 pts.  |   Sep 15 2009  2:12PM GMT

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.

 

mrdenny   46810 pts.  |   Sep 16 2009  2:57AM GMT

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.

 
0