Report running very slow compared to Query Analyzer - high TimeDataRetrieval
Hi, Ihave a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!! I have checked the execution log on the RS using the below sql: Code Snippet use ReportServer Select * from ExecutionLog with (nolock) order by TimeStart DESC It shows that i havea large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout: TimeDataRetrieval TimeProcessing TimeRenderingSourceStatus ByteCountRowCount 601309 2227 3 1 rsSuccess4916 0 The weird thing is that when i run it in query analyzer, i get about 400 records in4 seconds !! I dont understand what RS is doing to take up so much time like this to retrieve data. The report is very simple - it basically returns the records straight out into a table. The only thing I somewhatsuspected wasa parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't. Any help would be greatly appreciated.
June 26th, 2007 7:12am

I am having this same issue. In SSMS, the stored procedure will run in about 3 seconds and returns about 300 rows. In Visual Studio, on the same machine, it takes over 4 minutes. Anyone have any idea why this might be happening? Thanks!
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2007 9:35pm

Try setting nocount on in the query or stored procedure. I was having the same problem but after put this line "Set nocount on" at the top of my procedure it RS reders much, much faster.
July 17th, 2007 8:39pm

Whether your ReportServer is running under the port 80. Try using some other port !!!
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2007 9:19pm

Hi Trace, yes i already have "set nocount on" in my stored proc and agree it is important. Mark
July 24th, 2007 11:10am

I was having the same issue with one of my stored procedures. I found this link very helpful http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/a61d6b3fd1a32b08?output=gplain . Originally my stored procedure looked like:PROCEDURE dbo.usp_MMReport_TEST (@WeekEnding as datetime = null)AS...if @WeekEnding is null begin -- the convert removes the time from the date set @WeekEnding = getdate()end...My stored procedurenow:PROCEDURE dbo.usp_MMReport_TEST (@pWeekEnding as datetime = null) AS...declare @WeekEnding as datetimeif @pWeekEnding is null begin -- the convert removes the time from the date set @WeekEnding = getdate()endelse set @WeekEnding = @pWeekEnding ...
Free Windows Admin Tool Kit Click here and download it now
July 31st, 2007 10:12pm

Does your reports have Parametrers?Look to see if "Parameter Sniffing" is causing the problem.... Here is the link to learn more about Parameter Sniffing. http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html I experienced same in the past, though I was not able to answer as why it takes fewer seconds to execute in SQL compared to SSRS but I was able to reduce the execution time with this parameter sniffing. In our case report was taking 45000MS after tuning it came down to 2000MS. But stillthis does not answer as why it takes lesser time when executed directly compared to executing it in SSRS.
July 31st, 2007 10:37pm

Tried changing the port number but it had no effect.
Free Windows Admin Tool Kit Click here and download it now
August 1st, 2007 6:39am

Hey Mark, Did you read the post http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/a61d6b3fd1a32b08?output=gplainmentioned in this thread about parameter sniffing? To state the potential issue a little more broadly, it's possible that SQL Server is trying to set up a cached query plan based on what it considers to be "typical" use of your sproc -- and the way RS is passing that parameter may not look anything like how it expected when it set up that plan. (BTW: I don't think it counts to do it in the Query Analyzer, I'm not sure but those interactivetests may not be included in what might be used to cache the query plan at all). I suggested some other things to read on this topic http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1827775&SiteID=1, which might help you as well. The idea is to do one of two things: [ ] 1. make sure that Query Analyzer doesn't use the cached plan by forcing it to recompile -- you can do this a couple of ways -- to force "dynamic optimization" or, [ ] 2. if it is going to use the cached plan, to make sure that the cached plan is appropriate to your expected use. Sometimes it's really impossible to do #2 especially with reporting queries where the params are exposed to interactive users. It's better to force recompilation. The price of the extra time to live without the cached plan is minimal compared to what you can save by forcing SQL Server to put its best effort into re-optimizing for each fresh set of params. HTH, >L<
August 1st, 2007 8:15am

Hi Lisa, I tried changing the stored proc to eliminate the possibility of parameter sniffing causing the problem. I assigned the parameters to internal variables, killed the stored proc and recreated it so that any cached exec plans were removed. The result was no different and as I expected running it in Query Analyser was far worse, now taking over a minute to execute instead of 4secs. I really dont think this is an issue of parameter sniffing - why would the execution plan be different between SQL Query Analyser and RS? One thing i did notice while doing this however was even though I specify the date parameters in the RS report as AU format (in the XML) and the dates appear in the date picker boxes in AU format (ie. dd/mm/yyyy), looking at the Paramters field in the ExecutionLog it shows the dates in the querystring as US format (ie. mm/dd/yyy) - being sStartDate=07/23/2007 00:00:00&sEndDate=08/01/2007 00:00:00. My stored proc has set dateformatdmy so the conversion should work properly but maybe there are some conversion issues here between SQL and RS??? Thanks for your help - the link about parameter sniffing you sent was good to know. Mark
Free Windows Admin Tool Kit Click here and download it now
August 1st, 2007 11:10am

Hi Mark, I didn't say it was an issue of parameter sniffing, and I didn't point you towards the good article about that <s>. I asked you whether you had read the article, described to you earlier in the thread. What I said was that itmight bean issue of appropriate and dynamic query optimization, versus a cached query plan. Parameter sniffing is only one of many ways that SQL Server tries to figure out how to optimize a query. I thought the article did a good job of discussing some of what goes on during this process, and that by absorbing that you could think of ways to handle the wider issue that would be appropriate to your code. I also pointed you to a thread in which I discussed this in more detail. While there are truly many issues with the date controls in the default parameter interface and how they behave, I don't think that what you're experiencing with the date format is implicated in the performance issue. I guess it *might* be if (say) there is a non-default param value in your proc but your report parameter allows nulls. Let's say the report server database has a different date default than your production database in your system. I have never sat down and tested this scenario and I guess it might cause problems, So we'll look at that first. Whether the date format is at faultis really easy to prove one way or the other, and once proven can be pretty easy to fix: Create a test parameter that is of string type. SQL Server is really good about dynamic conversion of string dates to date types as you probably know. place values (formatted however you want) into this version of the parameter instead of your date control parameter. Use valid dates, in both formats. Does your performance change? Do you get the right results but bad performance with US format date strings versus right results with good performance with AU date values? Do you get the WRONG results with US format date strings, or with AU date strings? Try additional permutations involving casting/converting in your proc. FWIW, go ahead in the Query Analyzer and put the SAME values you see in the log into your tests there (in US format, I mean). Do you get the CORRECT results? Does performance change? Hold this thought and see below. Another thing you can do is look at the query plan as presented by the Query Analyzer when you present the arguments different ways. See #7 above -- try presenting the date in different formats. Does the plan show the same path and the same indexes in use each time, or are some not possible because of the way the date has to be converted? When this happens, the issue isusually that the conversion must be done for each line compared in the filter. You can fix this really easily in most cases by cast/convert in the procedure so that yourSELECT line is only doing that work once. If the plans are different, and perhaps the indexes used are different and you *can't* fix it by fixing the date before the SELECT, do you need to add an index, or perhaps rebuild one? Moving on from the date format, as I said before it may be really important NOT to go by your interactive Query Analyzer performance because what you do there may not be used by SQL Server in determining a query plan to cache and re-use. In caseyou haven't read the other discussion that I pointed you to at (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1827775&SiteID=1), think about using RECOMPILE or other options that will force a more dynamic investigation of the query. >L<
August 1st, 2007 6:06pm

Hi Lisa, Sorry for my late reply. I did look at your post and tried the RECOMPILE option unfortunately without much success. I did find a workaround to my problem. My report ran on Monday mornings and this is the only day during the week that i had problems running the report. The server has a maintenance schedule on Sunday nights to optimise the DB and truncate logs. I re scheduled my report to run an hour BEFORE the optimisations occured on Sunday night and the report runs fine. I only discovered this because the System Admin decided to change the optimisation schedule to run every night and my report didnt run every day when that happened. I'm still not sure why this is happening but i am a little relieved that my report is now working in its new server pre-optimisation schedule Not sure if things will continue like this but it has bought me some time at least. Mark
Free Windows Admin Tool Kit Click here and download it now
September 24th, 2007 3:37am

Hi Mark, That's pretty interesting; thank you for sharing. I don't suppose that either your job was the FIRST task *after* the optimisations completed? Maybe the delay was the caused by some startup behavior that occurs because the maintenance task shuts stuff down ? Glad it's working for you now, anyway. >L<
September 24th, 2007 6:34am

Hi Lisa, Actually I suspected the same thing but I ran the report a number of times on the Monday morning and afternoon and could not get it to work. Yet the next day, Tue morning the report would run - i am trully baffled and have definitely put this one into the X files. Thanks so much for all your assistance Lisa - it has been an educational journey . If anything ever comes up that sheds more light on this in future i will post it. Cheers, Mark
Free Windows Admin Tool Kit Click here and download it now
September 24th, 2007 6:45am

Hello Mark, Did you find out as to waht was causing this problem ? WE have similar issue on our side. Thanks
April 3rd, 2011 3:43pm

Hello Mark, Did you find out as to what was causing this problem ? WE have similar issue on our side. Thanks
Free Windows Admin Tool Kit Click here and download it now
April 3rd, 2011 3:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics