Performance issue with larger resultsets MSSQL

Question

I currently have a stored procedure in MSSQL where I execute a SELECT-statement multiple times based on the variables I give the stored procedure. The stored procedure counts how many results 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 Realtor- and contentID 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.

Check about parameter sniffing and add `OPTION(RECOMPILE)`. Also you can update a stat on your tables. BTW please provide execution plan for both situations — Devart, Jul 26 '16 at 08:20
I'm not sure what you mean by "Also you can update a stat on your tables". I tried parameter sniffing, it helped, but not by a whole lot. I'll check OPTION(RECOMPILE) — user2939331, Jul 26 '16 at 08:30
`ISNULL(reps.IsForSale,0) = ISNULL(@isForSale,0)` - these `isnull`s are problem too. You'd better rewrote it to `(@isforsale is null or isforsale=@isforsale)` + recompile — Ivan Starostin, Jul 26 '16 at 08:54
Changing the ISNULL didn't do much. Recompile did nothing with the larger resultset, but made the smaller one slower. — user2939331, Jul 26 '16 at 09:57
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 — user2939331, Jul 28 '16 at 07:36

score 1 · Answer 1 · edited May 23 '17 at 12:24

1

Table variables can perform worse when the number of records is large. Consider using a temporary table instead. See When should I use a table variable vs temporary table in sql server?

Also, consider replacing the UNPIVOT by alternative SQL code. Writing your own TSQL code will give you more control and even increase performance. See for example PIVOT, UNPIVOT and performance

edited May 23 '17 at 12:24

Community

1
1

answered Jul 26 '16 at 08:54

tomislav_t

527
2
9

I already tried to remove all Tables with Hard data, but the query doesn't go much faster if i do that. – user2939331 Jul 26 '16 at 09:05
Can you execute SET STATISTICS TIME,IO ON just before running the query and upload the output? This will give us an idea of where the performance problems are coming from. – tomislav_t Jul 26 '16 at 09:50
http://codepaste.net/efxenf This is the result which show in "Messages" – user2939331 Jul 26 '16 at 10:02
this is not the full-fledged answer, it just contains links. You may either to use the comments next time (when you'll have enough reputation), or to elaborate with the code snippets, instead of just providing the dry external links. See the best practices here: http://stackoverflow.com/help/how-to-answer – Farside Jul 26 '16 at 10:16
1

What I see in the statistics output is: 1. Consider replacing the EXISTS in the WHERE clause by an INNER JOIN on the @RealtorIdTable variable. 2. Define RealtorId as PRIMARY KEY. 3. You have not included the part of the query that deals with RealEstatePropertySummaries, note that in that part you have two times 14905 logical reads which can be quite a lot. 4. Observe that the CPU time is much lower then the elapsed time (344 ms < 1269 ms). This could indicate that SQL Server does not start any parallel processing. And this could be caused by the usage of the table variable. – tomislav_t Jul 26 '16 at 10:39
Using a Temp-table instead of @resultTable didn't do anything – user2939331 Jul 26 '16 at 11:51

Performance issue with larger resultsets MSSQL

1 Answers1