Query Tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Tuning

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-28 : 02:12:29

Hi,

Mine Below Query takes considerable time at the time of execution.
Can any one help me, what is the other way to write this query?

Declare @p_Mkt_View_Id int
Set @p_Mkt_View_Id = 17

Select Distinct Customer_id From Active_Product_Cust_Dtl
Where Product_Group_Code in
(Select Distinct Product_Group_Code From Products
Where Product_code in (
select Distinct ProductId from pit
where pitid in (select pitid from marketviewdef
where mktviewid = @p_Mkt_View_Id)))

Thanks
Prashant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 02:17:04

change like this & try:-

Select Distinct Customer_id 
From Active_Product_Cust_Dtl a
inner join  Products p
ON a.Product_Group_Code=p.Product_Group_Code 
inner join pit pi
on pi.ProductId =p.Product_code
inner join marketviewdef m
on m.pitid =pi.pitid 
where mktviewid = @p_Mkt_View_Id

Also try putting indexes on joining columns and column in where clause.

raja_saminathan
Starting Member

12 Posts

Posted - 2008-05-28 : 02:21:16

Hi ,
Try this

select Distinct Customer_id from Active_Product_Cust_Dtl A
inner join Products p on A.Product_Group_Code = p.Product_Group_Code
inner join pit on p.Products_Code = pit.ProductId
inner join marketviesdef M on M.pitid =pit.pitid
where M.mktviewid=@P_Mkt_View_Id

Rajesh

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-28 : 02:25:29

hi

try this..

SELECT DISTINCT Customer_id
FROM Active_Product_Cust_Dtl A
Left JOIN Product_code P ON A.Product_Group_Code = P.Product_Group_Code
Left JOIN Products Pd ON P.ProductId = Pd.ProductId
Left JOIN pit pt ON Pd.Product_code = pt.Product_code
Left JOIN marketviewdef MK ON pt.pitid = Mk.pitid
where MK.mktviewid = @p_Mkt_View_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 04:00:55

quote:
Originally posted by under2811

hi

try this..

SELECT DISTINCT Customer_id
FROM Active_Product_Cust_Dtl A
Left JOIN Product_code P ON A.Product_Group_Code = P.Product_Group_Code
Left JOIN Products Pd ON P.ProductId = Pd.ProductId
Left JOIN pit pt ON Pd.Product_code = pt.Product_code
Left JOIN marketviewdef MK ON pt.pitid = Mk.pitid
where MK.mktviewid = @p_Mkt_View_Id

No use of giving LEFT JOIN if you are leaving the WHERE condition as it is as it will again return only those records which have matching values in marketviewdef table. If you really wish to use LEFT JOIN to get results from first table regardless of matching condition on other tables you need to modify WHERE clause like this

where (MK.mktviewid = @p_Mkt_View_Id OR MK.mktviewid IS NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources