April 3, 2007 at 6:44 am
I hav a query
declare @ProductCode Char(10) declare @CountryCode Char(10) set @productCode='AP' set @countrycode='JP' Select distinct cl.Code, cl.Name From SalesCountryDestination_lnk scd(NoLock) Inner Join DestinationArticle_lnk da(NoLock) On da.SalesCountryDestination_Id = scd.SalesCountryDestination_Id Inner Join Article a(NoLock) On a.Article_Id = da.Article_Id Inner Join Program_lkp p(NoLock) On a.ProgramCode = p.Code Inner Join Country_lkp cl(nolock) On cl.Code = scd.destinationcode where p.ProductCode = @ProductCode And scd.SalesCountryCode = @CountryCode and a.IsCourseArticle = 1 Order by cl.Name Asc
which gives result perfectly with high read counts in program_lkp table as shown..
(0 row(s) affected) 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 'DestinationArticle_lnk'. Scan count 1, logical reads 234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Program_lkp'. Scan count 0, logical reads 12200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Article'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesCountryDestination_lnk'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Country_lkp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected)
when i remove the distinct clause in the above query the reads are reduced as shown...
(0 row(s) affected) 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 'DestinationArticle_lnk'. Scan count 1, logical reads 217, physical reads 0, read-ahead reads 13, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Article'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Program_lkp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesCountryDestination_lnk'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Country_lkp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected)
can anyone pls help me to solve this... this query is used almost 3000 times a day... it would be helpful if it is optimised...!!!
April 4, 2007 at 12:48 am
Try this:
SELECT cl.Code, cl.Name
FROM Country_lkp cl (nolock)
INNER JOIN SalesCountryDestination_lnk scd (NoLock)
ON cl.Code = scd.destinationcode
AND scd.SalesCountryCode = @CountryCode
INNER JOIN DestinationArticle_lnk da (NoLock)
ON scd.SalesCountryDestination_Id = da.SalesCountryDestination_Id
INNER JOIN Article a (NoLock)
ON da.Article_Id = a.Article_Id
AND a.IsCourseArticle = 1
INNER JOIN Program_lkp p (NoLock)
ON a.ProgramCode = p.Code
AND p.ProductCode = @ProductCode
GROUP BY cl.Code, cl.Name
ORDER BY cl.Name ASC
Andy
April 4, 2007 at 10:41 am
I would be interested in knowing how the following performs:
SELECT CL.Code, CL.[Name]
FROM Country_lkp CL
WHERE EXISTS (
SELECT *
FROM SalesCountryDestination_lnk SCD
WHERE SCD.destinationcode = CL.Code
AND SCD.SalesCountryCode = @CountryCode
AND EXISTS (
SELECT *
FROM DestinationArticle_lnk DA
WHERE DA.SalesCountryDestination_Id = SCD.SalesCountryDestination_Id
AND EXISTS (
SELECT *
FROM Article A
WHERE A.Article_Id = DA.Article_Id
AND A.IsCourseArticle = 1
AND EXISTS (
SELECT *
FROM Program_lkp p
WHERE P.Code = A.ProgramCode
AND P.ProductCode = @ProductCode
)
)
)
)
ORDER BY [Name]
[Edit] I am assuming you already have indexes on all the foreign keys.
April 5, 2007 at 12:11 am
Thank you so much Ken... ur code is workin perfect...
and i got very less reads as...
(0 row(s) affected) 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 '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 'SalesCountryDestination_lnk'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Country_lkp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy