Why does the 2nd T-SQL query run much faster than the first when called by Reporting Services 2005 in a web-app

Solution 1:

You may have come across a query that has an issue with parameter sniffing, which has to do with how Sql Server tries to optimise your query execution plan but in cases when Reporting Services is involved completely messes it up and makes it run incredibly slowly.

I had a case with a report that had two complex queries of around 150 lines each but which ran in 7 seconds in my development environment - the entire report took less than 10 seconds. However, when deployed to the production SSRS server the report took more than 7 minutes and often timed out making the report unrunnable.

Most information about this issue talks about it in relation to stored procedures. Don't dismiss this because you are not using stored procedures (like I did for a long time); it is very relevant to straight Sql queries as well.

So the difference you are seeing is that Sql Server is creating two very different execution plans as the two queries are structured differently.

Fortunately, the solution is very simple: put the parameters into internal variables and use these in your query instead. I did this with my report and the production report went back to 10 seconds like the development version did in Visual Studio.

To bypass parameter sniffing for your first query you would make it look like this:

BEGIN
    -- Use internal variables to solve parameter sniffing issues
    DECLARE @StartDateInternal AS DATETIME;
    DECLARE @EndDateInternal AS DATETIME;
    DECLARE @SchoolIDInternal AS INT;
    DECLARE @GradeLevelInternal AS INT;

    -- Copy the parameters into the internal variables
    SET @StartDateInternal = @StartDate;
    SET @EndDateInternal = @EndDate;
    SET @SchoolIDInternal = @SchoolID;
    SET @GradeLevelInternal = @GradeLevel;

    -- Now use the internal variables in your query rather than the parameters
    SELECT 
        c.TeacherID, u.FName + ' ' + u.lname as Teacher, count(sb.behaviorID) as BxCount, 
        sb.behaviorID, b.BehaviorName, std.GradeID, gl.GradeLevel
    FROM 
        StudentBehaviors sb
    join 
        Classes c on sb.classid = c.classid
    join 
        StudentDetails std on sb.studentID = std.StudentID and std.RecordIsActive=1
    join 
        users u on c.TeacherID = u.UserID
    join 
        Behaviors b on sb.behaviorID = b.BehaviorID
    join 
        GradeLevels gl on std.GradeID = gl.GradeLevelID
    WHERE 
        sb.classdate between @StartDateInternal and @EndDateInternal
        and c.schoolid = @SchoolIDInternal
        and std.GradeID = @GradeLevelInternal
    GROUP BY 
        c.TeacherID, sb.behaviorID, b.BehaviorName, u.lname, u.FName, 
        std.GradeID, gl.GradeLevel
    ORDER BY 
        u.LName, sb.behaviorID;

END;