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

Question

With the addition of parens in the subquery, results were returned immediately in the app.

This runs slow when called by RS2005 in a vb.net / aspx web app:

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 @StartDate and @EndDate
    and c.schoolid = @SchoolID
    and std.GradeID=@GradeLevel
GROUP BY 
    c.TeacherID, sb.behaviorID, b.BehaviorName, u.lname, u.FName, 
    std.GradeID, gl.GradeLevel
ORDER BY 
    u.LName, sb.behaviorID

This runs fast:

select a.teacherid, a.teacher,a.bxcount, a.behaviorid,a.behaviorname,a.gradeid, a.gradelevel
from (
    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 @StartDate and @EndDate
        and c.schoolid = @SchoolID
        and std.GradeID=@GradeLevel
    group by c.TeacherID, sb.behaviorID, b.BehaviorName, u.lname, u.FName, std.GradeID, gl.GradeLevel
    ) a
    order by a.teacher, a.behaviorid

These run at the same speed in a query windows in SQL Server Management Studio. Why the difference? Thanks.

Answer 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;