3

I have a query that takes roughly 2 minutes to run. It's not terribly complex in terms of parameters or anything, and the report itself doesn't do any truly extensive processing. Basically just spits the data straight out in a nice format. (Actually one of the reports doesn't format the data at all, just returns a flat table meant to be manipulated in excel.)

It's not returning a massive set of data either.

Yet the report takes upwards of 30 minutes to run.

What could cause this?

This is SSRS 2005 against a SQL 2005 database btw.

EDIT: OK, I found that with the addition of WITH (NOLOCK) in the report it takes the same time as the query does through SSMS. Why would the query be handled differently if it's coming from reporting services (or visual studio on my local machine) than if coming from SSMS on my local machine? I saw the query running in Activity Monitor a couple times in SLEEP_WAIT mode, but not blocked by anything...

EDIT2: The connection string is:

Data Source=SERVERNAME;Initial Catalog=DBName

CodeRedick
  • 7,346
  • 7
  • 46
  • 72
  • Just a suspicion but I would suspect that the datasource/ado.net "driver" is the issue. I'm interested in any other comments about this as well as I've had similar experiences. – Mozy Mar 03 '10 at 23:32
  • What does your connection string look like for the data source on the report? – Even Mien Mar 05 '10 at 14:03
  • 1
    Possibly related? http://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs Although your NOLOCK experience could suggest not. – John Fouhy Aug 12 '13 at 04:58

4 Answers4

0

Is it definitely the query taking a long time to run, or is the processing being done by the server that is slow? Some reports call queries multiple times. For instance, if you have a subreport inside a of a paging list control, each page of that report calls the query separately. So maybe there's something the report is doing with the data causing the delay?

brentlightsey
  • 2,026
  • 1
  • 19
  • 23
  • The report only calls the query once... and as I said the report doesn't do much processing of the data. Once I added WITH NOLOCK to the query the report ran in under 2 minutes... – CodeRedick Mar 05 '10 at 17:21
0

How large is the data set that is returned by your query? If it is very large the majority of the time that is taken on the report server could be related to the time it takes the report to render. To be sure you could look at the ExecutionLog table on the report server to see if the TimeRendering is a large number in comparison to the overall execution time.

Greg Lorenz
  • 89
  • 1
  • 7
0

I think that this is not uncommon, but we looked into similar issues.

From memory, one thing that we did notice was that our subreport had parameters, and we've configured the "possible values" to be queried from the database.

I think that every time the subreport runs, SSRS re-queries the possible values of the parameters (& runs any other queries in your report even if you don't use the results).

In this case, once we were happy the subreport was working OK, we removed the queries for vaidating the parameter values and allowed "any value", assuming the parent report would not feed us bad parameter values.

Andy Joiner
  • 5,932
  • 3
  • 45
  • 72
0

A tad late to the party, but for anybody from the future having a similar problem.

Parameter sniffing

If a stored procedure with parameters is being used, it might be due to a phenomenon called 'parameter sniffing'.

In short, the first time a stored procedure is executed from SSRS an execution plan, based on the specified parameter values, is determined. This execution plan is then stored and used every time the stored procedure is executed from SSRS. Even though this execution plan might not be optimal for any future parameter values.

For an excellent and more extensive explanation have a look at: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Other questions

Also have a look at this similar question: Fast query runs slow in SSRS

lgespee
  • 131
  • 1
  • 4