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