0

I have two reports in SSRS 2008, Dashboard and Drillthrough.

Dashboard contains many datasets (all stored procedures), and takes about 4-5 seconds to run.

Clicking on an aggregated value in one of the tables in Dashboard takes the user to Drillthrough, which has one dataset - a stored procedure accepting two parameters (int and char(1), which are passed from Dashboard), which runs very quickly in SSMS.

The Drillthrough dataset is large, averaging around 10,000 rows, which are displayed in a table. The report is configured to have 200 rows per page, and so can have a lot of pages.

The problem:

When I click a link in Dashboard, nothing happens for about a minute. There are several issues I have with this:

  1. The fact that the screen does not immediately switch to the 'Report is being generated' screen means confusion for the user, who sees no response (in cases where the report is embedded in a web page). Is this normal behaviour?

  2. The Drillthrough query itself runs very quickly in SSMS, therefore, why is it taking so long on the Report Server? Where is the hold up likely to be? (I read up on 'parameter sniffing' in relation to this, but as the query runs quickly in SSMS, it seems that my problem wouldn't be due to issues around that.)

Chris Mack
  • 5,148
  • 2
  • 12
  • 29
  • 2
    could be many things, for example network related issues or rendering. To narrow down what you can do is run a simple report that just return the dataset without graphs or anything and see if it still takes forever to run. – Jayvee Jul 13 '17 at 09:52
  • 1
    Thanks, I took your advice and rebuilt the report gradually from its most basic form. It turned out I had some long running queries generating available values for parameters. – Chris Mack Jul 13 '17 at 13:35

0 Answers0