T_SQL Query Very costly Performance 1

[latitude800]

Good Morning
May I please have an assistance. I have worked on the query below but the query is creating very issues mainly in performance very costly. WOuld please take a look and see if would be a better way to write it. Thanks in advance.

The query is set up to limit access to certain ecommunities where course materials are distributed. The query determines if a user is:

a) a speaker at that particular course/conference

b) an attendee of that particular course/conference, during a specified time period

c) a board member, who have access to everything


Regards,

Jimmy






Select *

FROM Name

WHERE ID IN(SELECT n.ID FROM Name n INNER JOIN Orders o ON o.BT_ID=n.ID INNER JOIN Order_Lines ol ON ol.Order_Number=rder_Number INNER JOIN Product p ON p.Product_Code=ol.Product_Code INNER JOIN Meet_Master mm ON mm.Meeting=p.Product_Major WHERE (o.Status NOT LIKE 'C%' AND ol.Quantity_Ordered>0 AND ol.Product_Code IN ('NA06CEFEB/E07', 'NA06CEFEB/SPKE07', 'NA06CEFEB/CLE07')))OR ID IN (SELECT n.ID FROM Name n INNER JOIN Activity a ON a.ID=n.ID WHERE a.Activity_Type='COMMUNITY' AND a.Product_Code='290'AND(DateDiff(Day,GetDate(),'03/31/2006')>=0)) OR ID IN (SELECT n.ID FROM Activity a INNER JOIN Name n ON a.ID = n.ID WHERE a.PRODUCT_CODE = 'COMMITTEE/Board' AND n.status='A' AND a.THRU_DATE >= '03/31/2006')

 

[vongrunt]

Let's make it more readable first:

Select *
FROM Name
WHERE ID IN
(	SELECT n.ID 
	FROM Name n 
	INNER JOIN Orders o ON o.BT_ID=n.ID 
	INNER JOIN Order_Lines ol ON ol.Order_Number=o.Order_Number 
	INNER JOIN Product p ON p.Product_Code=ol.Product_Code 
	INNER JOIN Meet_Master mm ON mm.Meeting=p.Product_Major 
	WHERE 
	(	o.Status NOT LIKE 'C%' 
		AND ol.Quantity_Ordered>0 
		AND ol.Product_Code IN ('NA06CEFEB/E07', 'NA06CEFEB/SPKE07', 'NA06CEFEB/CLE07')
	)
)
OR ID IN 
(	SELECT n.ID 
	FROM Name n 
	INNER JOIN Activity a ON a.ID=n.ID 
	WHERE a.Activity_Type='COMMUNITY' 
		AND a.Product_Code='290'
		AND(DateDiff(Day,GetDate(),'03/31/2006')>=0)
) 
OR ID IN 
(	SELECT n.ID 
	FROM Activity a 
	INNER JOIN Name n ON a.ID = n.ID 
	WHERE a.PRODUCT_CODE = 'COMMITTEE/Board' 
		AND n.status='A' 
		AND a.THRU_DATE >= '03/31/2006'
)

There are three parts of query, all ORed together which basically does the same as UNION. Run this query first with only 1st IN, then only with 2nd IN, then 3rd. Which one is the slowest?

In 1st IN(), are joins on Product and Meet_Master necessary?

2nd IN: what is the purpose of:

AND(DateDiff(Day,GetDate(),'03/31/2006')>=0)

?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

First, don't use select * unless you actually need every single field. Next look at your indexes,so you have them on every one of the join fields and the fields used inthe where clauses?

Another thing you can do is move some of the where conditions to the join itself, this makes fewer records to join on and often speeds things up.

Union might speed this up. Another thign to try is to make the Union statement a dervied table rather than using the where Id in clause. Joins are often faster than the where In clause.

Also check out the execution plan, you may see ways to imporve it after looking at that.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief