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
            (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 ( 
        FOR [VariableName] IN(
        --...(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

  • How many rows are in the table? Jul 27 '16 at 8:55
  • Have you looked at the execution plan? Jul 27 '16 at 8:56
  • I did look at the execution plan, but I am not very familiar with execution plans. There are 71769 records in the Database. I can upload the Execution plan XML if you want? Jul 27 '16 at 9:54
  • Do you have proper indexes applied and further change ISNULL options to somewhat "WHERE (@isForSale IS NULL OR reps.IsForSale= @isForSale)" Jul 27 '16 at 10:02
  • I tried changing the ISNULL to that yesterday, but that didn't change anything noticable. But i will consider using that for better readability. I have multiple indexes set at the table where i count from, the only ones relevant would be a nonclustered on RealtorId and a clustered primary key on ID. Jul 27 '16 at 10:23

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.