Performance issue with larger resultsets SQL Server

Question

I currently have a stored procedure in SQL Server where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records are going to be returned for every filter a user can enable. The stored procedure isn't the issue, I transformed the select statement from te stored procedure to a regular select statement which looks like:

DECLARE @contentRootId int = 900589
DECLARE @RealtorIdList varchar(2000) = ';880;884;1000;881;885;'
DECLARE @publishSoldOrRentedSinceDate int = 8
DECLARE @isForSale BIT= 1
DECLARE @isForRent BIT= 0   
DECLARE @isResidential BIT= 1   
--...(another 55 variables)...

--Table to be returned
DECLARE @resultTable TABLE
(
    variableName varchar(100),
    [value] varchar(200)
)

-- Create table based of inputvariable. Example: turns ';18;118;' to a table containing two ints 18 AND 118 
DECLARE @RealtorIdTable table(RealtorId int) 
INSERT INTO @RealtorIdTable SELECT * FROM dbo.Split(@RealtorIdList,';') option (maxrecursion 150)

INSERT INTO @resultTable ([value], variableName) 
SELECT [Value], VariableName FROM( 
    Select count(*) as TotalCount, 
        ISNULL(SUM(CASE WHEN reps.ForRecreation = 1 THEN 1 else 0 end), 0) as ForRecreation,
        ISNULL(SUM(CASE WHEN reps.IsQualifiedForSeniors = 1 THEN 1 else 0 end), 0) as IsQualifiedForSeniors,
        --...(A whole bunch more SUM(CASE)...
    FROM TABLE1 reps
    LEFT JOIN temp t on
            t.ContentRootID = @contentRootId 
            AND t.RealEstatePropertyID = reps.ID
        WHERE 
            (EXISTS(select 1 from @RealtorIdTable where RealtorId = reps.RealtorID))
            AND (@SelectedGroupIds IS NULL OR EXISTS(select 1 from @SelectedGroupIdtable where GroupId = t.RealEstatePropertyGroupID))
            AND (ISNULL(reps.IsForSale,0) = ISNULL(@isForSale,0)) 
            AND (ISNULL(reps.IsForRent, 0) = ISNULL(@isForRent,0)) 
            AND (ISNULL(reps.IsResidential, 0) = ISNULL(@isResidential,0))  
            AND (ISNULL(reps.IsCommercial, 0) = ISNULL(@isCommercial,0))  
            AND (ISNULL(reps.IsInvestment, 0) = ISNULL(@isInvestment,0))  
            AND (ISNULL(reps.IsAgricultural, 0) = ISNULL(@isAgricultural,0))
            --...(Around 50 more of these WHERE-statements)...
            ) as tbl

    UNPIVOT ( 
        [Value]
        FOR [VariableName] IN(
        [TotalCount],
        [ForRecreation],
        [IsQualifiedForSeniors],
        --...(All the other things i selected in above query)...
        )
    ) as d

    select * from @resultTable

The combination of a RealtorId and ContentRootID gives me a set default set of X amount of records. When I choose a combination which gives me ~4600 records, the execution time is around 250ms. When I execute the sattement with a combination that gives me ~600 record, the execution time is about 20ms.

I would like to know why this is happening. I tried removing all SUM(CASE in the select, I tried removing almost everything from the WHERE clause, and I tried removing the JOIN. But I keep seeing the huge difference between the resultset of 4600 and 600.

EDIT: I also tried to use a temporary table for @resultTable instead of the table variable.

EDIT2: The execution plan of the one with many results: https://gist.githubusercontent.com/anonymous/8c91631b2ecf6e42d787583da20b2a7e/raw/f8097765814486b35e8e8ba90ea0a0611703690c/gistfile1.txt and with few results: https://gist.githubusercontent.com/anonymous/734cc06417204b1c2bf24d6eff075c67/raw/87ab011776d71659e27cdd58fc25c3414d6c5309/gistfile1.txt I don't think the plans differ too much

Answer 1

Looking at the execution plan XML, there are a few things sticking out that may be the cause of your issue.

1. You are selecting everything back to the client. There are inherent wait times with transferring data over a network. The larger query is transferring almost 8x more data.

2. The larger query has a spill to TempDB. This means that parts of the query are using disk instead of memory. This will clearly slow it down much more than the smaller query.

3. You are running the under the compatibility level for SQL Server 7.0, so the fact that your arithabort setting is different for each execution can be causing issues. See here - http://www.sommarskog.se/query-plan-mysteries.html.

Regarding #3, you are either not running these the same way, or you are changing session level settings in between runs. You need to run these exactly the same way to get execution plans that can be compared accurately.