question

limitha avatar image
limitha asked

Any ideas on why the SSRS report would run so slow in this particular environment?

I have an SSRS report that calls out to a stored procedure. If I run the stored procedure directly from a query window, it will return in under 2 seconds. However, the same query run from an 2005 SSRS report takes up to 5 minutes to complete. This is not just happening on the first run, it happens every time. Additionally, I don't see this same problem in other environments.
sql-server-2005sql-serverssrs-2005
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The ***exact*** same question on SO ( http://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs), with an accepted solution?
0 Likes 0 ·
@Kev Riley: wow, that's bizar! First I thought OP posted on several forums but the one on SO is from 2010!
0 Likes 0 ·
Love the "just put RECOMPILE on it" answer. Stuff like that is so scary.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
It sounds like a possible bad parameter sniffing situation. Different parameter values on different systems with different sets of statistics will lead to variable performance on some queries. I would check that the statistics are up to date on all the servers, first. If necessary, update them with a full scan. If that doesn't resolve the issue, compare the execution plans to understand what is occurring on each server. If the data sets between servers are different, that might be the answer right there.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.