Our reports typically have an @paramStart and @paramEnd as a datetime parameter to our SQL. In one very long query, we found that the report took many minutes, possibly an hour or more, to run. Running the query through SQL Server Management Studio 2008 was approximately 20 seconds. We eventually added the following code to the RDL file: DECLARE @localStartTime datetime DECLARE @localEndTime datetime SELECT @localStartTime = @paramStart, @localEndTime = @paramEnd And replaced all other references of @paramStart and @paramEnd to their local equivalent. With only that change, the query went from mega-minutes to seconds. This appears to have something to do with dynamic SQL handling datetime variables versus fixed strings as we can repeat this somewhat in SSMS by changing our @paramStart with a ‘9/29/10’ and @paramEnd with ‘9/30/10’. Any clue why we are having this issue? Is there a better way for us to work around the issue? Anybody else experiencing slowdowns with datetime parameters? Thanks, Terry Steyaert

Hi Terry, Above all, i would suggest you query the ExecutionLog2 view (TimeDataRetrieval TimeProcessing,TimeRendering) on the report server database to analyze most of time spent when report rendering. See http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx for more details on how to analyze one report performance issue. Let's say that if much time was spent on the TimeDataRetrieval, then you can try to tune the dataset query performance. If this is not doable, try to preload the report cache on certain schedule using Data-driven subscription to improve this report processing performance. See http://technet.microsoft.com/en-us/library/ms155927.aspx on ssrs report cache, and http://technet.microsoft.com/en-us/library/ms155876.aspx for some ways to preload report cache. thanks, Jerry