problem in usin distinct clause... can any1 help me..??

Question

Post reply

problem in usin distinct clause... can any1 help me..??

kerobin jesu

Mr or Mrs. 500

Points: 544

April 3, 2007 at 6:44 am

#177551

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...!!!

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

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

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.

kerobin jesu Mr or Mrs. 500 Points: 544 More actions · Answer 3

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)