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?
View Complete Post