sql server reporting services: I'm using Profiler to report a user issue that a report is running slow. The report uses a sproc passing parms. RS executes this through sp_executesql, which is fine, and i'm testing my QA execution using the same sp_executesql statement that RS is using (for consistency in testing).

The problem is this:
The sproc executes through QA in 266 ms.
Through the report, it executes in 6390.

I've identified the statement in the sproc that uses the bulk of the processing. It is the main query, and again, through QA, it runs in 250 ms, leaving 16 ms for other processing in the sproc. However, through RS, the same query takes 6359, leaving 31 ms for other processing. The data is only returning 5 rows, as it should.

Locks are not an issue as the sproc contains nolocks on every table. I am using the default, supplied interface of RS using a simple one page report.

Has anyone seen this or know why it's occuring?

Have you looked at the query plans to see if they are identical in the two
cases? You can add Show plan xxx to the SQL Profiler trace, it will show you
all the steps including how long it took for each one...

Also, are you measuring single user executions in both cases?

For general advice on RS perf take a look at
http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx

--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.


[quoted text, click to view]
The report uses a sproc passing parms. RS executes this through
sp_executesql, which is fine, and i'm testing my QA execution using the same
sp_executesql statement that RS is using (for consistency in testing).
[quoted text, click to view]
processing. It is the main query, and again, through QA, it runs in 250 ms,
leaving 16 ms for other processing in the sproc. However, through RS, the
same query takes 6359, leaving 31 ms for other processing. The data is only
returning 5 rows, as it should.
[quoted text, click to view]