Why is Reporting Services report vastly slower than its query?
- by Telos
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