August 12, 2011 at 6:50 am
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.
August 12, 2011 at 8:10 am
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
August 14, 2011 at 4:56 am
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
August 15, 2011 at 6:31 am
To me it looks like an implicit convertion problem, but to be shure I need to know the data definitions
August 16, 2011 at 1:53 am
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