SQL Server Performance

Why is this so slow?

Discussion in 'T-SQL Performance Tuning for Developers' started by marlong, Sep 1, 2005.

  1. marlong New Member

    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........
  2. FrankKalis Moderator

  3. Adriaan New Member

    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.

Share This Page