SSRS – SQL runs in 1 minute but Reporting Services tuns for 30 minutes

35 pts.
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?


Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Cvillavicencio
    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.
    35 pointsBadges:
  • Cvillavicencio
    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.
    35 pointsBadges:
  • Denny Cherry
    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.
    68,745 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: