August 28, 2009 at 2:36 pm
I have a report that is ridiculously slow. The report calls a stored procedure that executes on a log shipped database. If I connect to the database in Management Studio and execute the procedure, it returns about 60 rows in around 10 seconds (sometimes more sometimes less). When I try to execute the stored procedure in BIDS to build a dataset, BIDS will hang for > 10 minutes. The result set is the same 60 or so rows. The report definition doesn't have any fancy groups or summations; it only displays the data set.
I've been using Reporting Services to build reports for about a year, but I don't know anything about configuring or administrating it. Is there something I don't know about that might be going on between the reportserver and the server that the stored procedure is running on (reportserver is on a separate server and SQL Server instance than the procedure)
I've tuned the heck out of the procedure to get it down to where it is, and I can't for the life of me figure out why the performance is so poor on this report. Other reports executing against the same log shipped database do not have this problem. It seems to be just this procedure and report.
If anyone has any insight they could lend, I would appreciate it.
August 28, 2009 at 6:38 pm
If Reporting Services is having problem with a query returning only 60 rows then you may be using local temp tables which it does not handle very well. If you are not using temp table then you may need to create a view for the data you need which will change your point of access and may improve execution plan. You could also run it on profiler to find out what is slowing it down in SSRS.
Kind regards,
Gift Peddie
August 31, 2009 at 7:33 am
I would agree...profiler on the 60 records in 10 seconds...that's a ton of time for that small of a dataset.
September 1, 2009 at 4:09 pm
Alter your stored procedure and put
With Recompile
I also faced same problem, it is solved with recompile the stored pocedure
September 1, 2009 at 4:20 pm
SQLBisu (9/1/2009)
Alter your stored procedure and putWith Recompile
I also faced same problem, it is solved with recompile the stored pocedure
I would be REALLY careful with this, this is useful if the stats on the underlying tables are going to change a lot between runs. SQL now has to rebuild the query plan EVERY time the sproc runs and there is a performance penalty involved in that.
As far as his problem, I'm thinking you should run a trace on the SQL Server and see what the server is doing for those 10 minutes. I am having a hard time believing this is a SQL Server problem. I'm thinking something wierd is going on with BIDS, the trace should be able to help you tell when it is actually sending the query and how long that query is taking. Lets start with some imperical data instead of throwing out wild guesses and conjecture.
CEWII
September 2, 2009 at 6:56 am
In the sp we are doing a bunch of statistics related aggregations on user and date, so it was repeatedly querying the same tables, and many times the same records to perform the aggregations. I think there are 8 in all. We added some indexing that really should have been in place sooner, and that helped some. Then we built a temp table in the proc to hold the subset of records that we repeatedly aggregate and built the aggregations on that temp table. The proc runs in < 30 seconds now.
There were a couple of things I didn't know about SQL Server that were tripping me up.
One was the way it builds the execution plans. I assumed that the execution plan for a stored procedure would be the same as the execution plan for the same queries in an analyzer window. How wrong I was.
Two was data caching. Subsequent runs of the proc were quick, but the first run was crazy slow. The report was slow because the data cache was clean of relevent data at runtime.
The additional indexes and the temp table have greatly improved the performance of the report.
Thanks for the posts!
September 2, 2009 at 3:35 pm
Have you created indexes on the temp table as well?
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply