SQL Server Performance Forum – Threads Archive

Why is this so slow?

Can anybody explain the reason why the following two queries differ so drastically? The only difference is that addition of a variable in place of a static value. Version 1 : SELECT SUM(TD.NettBillings) AS Bills
, SUM(TD.UnitWeeks) AS Weeks
, TC.Param_Value
, TC.Param_Mask
FROM Trawler_TariffData AS TD
INNER JOIN Trawler_Criteria_Masked AS TC ON TD.BitSearch1 & 939524096 = TC.Param_Mask
AND TC.Param_Name = ‘WeekName’
AND TC.Season = TD.Season
WHERE TD.Season = ’05’
ANDPriorSeason = 0
GROUP BY TC.Param_Value
, TC.Param_Mask
ORDER BY TC.Param_Mask Run Time – 3 Seconds.
Version 2 : DECLARE @MASK BIGINT
SET @MASK = 939524096 SELECT SUM(TD.NettBillings) AS Bills
, SUM(TD.UnitWeeks) AS Weeks
, TC.Param_Value
, TC.Param_Mask
FROM Trawler_TariffData AS TD
INNER JOIN Trawler_Criteria_Masked AS TC ON TD.BitSearch1 & @MASK = TC.Param_Mask
AND TC.Param_Name = ‘WeekName’
AND TC.Season = TD.Season
WHERE TD.Season = ’05’
ANDPriorSeason = 0
GROUP BY TC.Param_Value
, TC.Param_Mask
ORDER BY TC.Param_Mask Run Time – 23 Seconds Hmmmm……..

Can you post the execution plans? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Okay, so here we have the details we seemed to be missing in the other thread … [8D] The difference you’re seeing is not unknown – SQL Server doesn’t seem to like to use local variables for filtering. It might improve if you create a stored procedure with a proper parameter. This might be a weird suggestion, but seeing that you already have a storage table for search bitmasks – why not normalize that by creating a child table with the individual INT values? Include proper indexes and it may well improve performance.

]]>