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 3, 2007 at 8:31 am
Hi,
Unfortunately this is the problem you get when using distinct - the generated resultset needs to be examined for duplicates in order satisfy the DISTINCT clause. It's a bit difficult to look at the query and try and offer advice in terms of how to optimise it. It helps to have a good understanding of the relationships between the tables in order to be able to do so.
I'd suggest you have a look at the execution plans and try and figure out what's hurting your performance. You might be able to improve things with indexing but it's worthwhile investigating the possibilty of rewriting your query to not use distinct, if at all possible.
April 3, 2007 at 8:35 am
1) You should always post table create statements as well as some sample data so we on the forums can have a better chance of helping out.
2) Since removing the DISTINCT gives you a better query plan, use that to your advantage. Simply wrap the non-distinct into a derived table, then select distinct from that, like this:
declare @ProductCode Char(10)
declare @CountryCode Char(10)
set @productCode='AP'
set @countrycode='JP'
Select distinct t.Code, t.Name
from (
SELECT 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) as t
Order by t.Name Asc
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply