Using Variables increases Scans and Logical Reads

  • I can't understand this. I have a query and if I hard code a number for a EntityType in the query, this is the IO statistics

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'FranchiseAgreement'. Scan count 1, logical reads 157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Address'. Scan count 1, logical reads 5485, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'County'. Scan count 1, logical reads 126, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'State'. Scan count 0, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Country'. Scan count 0, logical reads 488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'City'. Scan count 0, logical reads 630, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ARTaxMasterfile'. Scan count 4, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ELMasterfile'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    nothing too bad. Now all I am going to do is replace the hard coded number with a variable for readability

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ELMasterfile'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    ***Table 'FranchiseAgreement'. Scan count 539932, logical reads 1750032, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Address'. Scan count 1, logical reads 5485, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'County'. Scan count 0, logical reads 481, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'State'. Scan count 0, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Country'. Scan count 0, logical reads 488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'City'. Scan count 0, logical reads 630, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'ARTaxMasterfile'. Scan count 4, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    DECLARE @BalanceIdEL SMALLINT

    SET @BalanceIdEL = 14

    PRINT 'Version 5'

    SELECT Stores.*, Tax.ARTaxMasterFileId

    FROM

    (-- Get The Addres of Each Lease

    SELECT

    E.LeaseNum

    ,E.LeaseDate

    ,A.CountryCode

    ,A.StateProvCode

    ,A.County

    ,A.City

    FROM

    ELMasterFile E

    INNER JOIN

    FranchiseAgreement F

    ON E.EntityId = F.FranchiseID

    AND E.EntityType = 7

    INNER JOIN

    Address A

    ON F.FranchiseNum = A.EntityId

    AND F.SatelliteNum = A.SatelliteNum

    AND A.EntityType = 5 -- Store

    AND A.AddressType = 3 -- Store Address

    ) Stores

    INNER JOIN

    -- Get the Taxes at each Level, Country, State, County and City

    (SELECT A.*, C.CountryCode, '' AS StateProvCode, '' AS CountyName, '' AS CityName

    FROM ARTaxMasterFile A

    INNER JOIN

    COMMON.dbo.Country C

    ON A.ARTaxLevelTypeId = C.CountryId

    where BalanceId = @BalanceIdEL

    and ARTaxLevelType = 1

    UNION ALL

    SELECT A.*, S.CountryCode, S.StateProvCode AS StateProvCode, '' AS CountyName, '' AS CityName

    FROM ARTaxMasterFile A

    INNER JOIN

    COMMON.dbo.State S

    ON A.ARTaxLevelTypeId = S.StateId

    where BalanceId = @BalanceIdEL

    and ARTaxLevelType = 2

    UNION ALL

    SELECT A.*, C.CountryCode, C.StateProvCode AS StateProvCode, C.CountyName AS CountyName, '' AS CityName

    FROM ARTaxMasterFile A

    INNER JOIN

    COMMON.dbo.County C

    ON A.ARTaxLevelTypeId = C.CountyId

    where BalanceId = @BalanceIdEL

    and ARTaxLevelType = 3

    UNION ALL

    SELECT A.*, Country.CountryCode, State.StateProvCode AS StateProvCode, County.CountyName AS CountyName, City.CityName AS CityName

    FROM ARTaxMasterFile A

    INNER JOIN

    COMMON.dbo.City City

    ON A.ARTaxLevelTypeId = City.CityId

    INNER JOIN

    COMMON.dbo.County County

    ON City.CountyId = County.CountyId

    INNER JOIN

    COMMON.dbo.State State

    ON City.StateId = State.StateId

    LEFT OUTER JOIN

    COMMON.dbo.Country Country

    ON City.CountryId = Country.CountryId

    where BalanceId = @BalanceIdEL

    and ARTaxLevelType = 4) Tax

    ON (Stores.CountryCode = Tax.CountryCode

    AND Tax.StateProvCode = ''

    AND Tax.CountyName = ''

    AND Tax.CityName = '')

    OR

    (Stores.CountryCode = Tax.CountryCode

    AND Stores.StateProvCode = Tax.StateProvCode

    AND Tax.CountyName = ''

    AND Tax.CityName = '')

    OR

    (Stores.CountryCode = Tax.CountryCode

    AND Stores.StateProvCode = Tax.StateProvCode

    AND Stores.County = Tax.CountyName

    AND Tax.CityName = '')

    OR

    (Stores.CountryCode = Tax.CountryCode

    AND Stores.StateProvCode = Tax.StateProvCode

    AND Stores.County = Tax.CountyName

    AND Stores.City =Tax.CityName)

    WHERE Tax.StartDate <= GETDATE()

    AND Tax.StartDate > Stores.LeaseDate

    The funniest thing is the parameter is not used on that table

    If I take out the Variable BalanceIdEL, and replace it with 14, the query is 2x faster.

  • http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Just remember that if you turn the query into a stored procedure and you use parameters, you're more likely to get the plan similar to when you hard code the values than the plan where they are not hard coded. But, that's completely dependent on the statistics of the data in question.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • To me it looks like an implicit convertion problem, but to be shure I need to know the data definitions

  • could you post the execution plans for both so we can determine whether it is in fact an implicit conversion issue?

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply