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

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.