We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Query Performance issue

Medium Priority
183 Views
Last Modified: 2012-05-07
I need help with 2 queries.  I need to combine the 2 query together and get one record out of it, but no matter what I do, it is taking about 30-40 seconds to execute.  If I run each query separately it takes less than a second to run both.  Am I doing something wrong?  Tried doing temp table, nested queries etc.  Is there anyway I can set these 2 queries as if they are separate table and then select joining these 2 together?

Just to clarify on why I only need the one with matching date is that these specific part the want date and required date are always exact.

Query 1 took less than 0 second to execute.  Query 2 took less than 0 second to execute.  Query 3 took about 40 seconds.
1)
SELECT	DISTINCT
		R.WORKORDER_BASE_ID, R.WORKORDER_LOT_ID, 
		R.WORKORDER_SUB_ID,R.REQUIRED_DATE
FROM	REQUIREMENT R
WHERE	R.WORKORDER_BASE_ID = '78414'
 
RESULT:
78414	2	0	2009-07-21 00:00:00.000
78414	2	2	2009-07-09 00:00:00.000
78414	2	2	2009-07-21 00:00:00.000
 
2)
SELECT	DISTINCT 
		WO.PART_ID,
		WO.BASE_ID, WO.LOT_ID, WO.SUB_ID, 
		WO.USER_3, WO.DESIRED_WANT_DATE
FROM	WORK_ORDER WO
WHERE	WO.PART_ID = 'R045AONGABB074DDY001AAA1H'
		AND WO.TYPE = 'W' 
 
RESULT:
R045AONGABB074DDY001AAA1H	76447	5	0	76276	2009-03-16 00:00:00.000
R045AONGABB074DDY001AAA1H	78381	5	0	76276	2009-07-07 00:00:00.000
R045AONGABB074DDY001AAA1H	78567	5	0	78414	2009-07-09 00:00:00.000
 
Combine both:
SELECT	DISTINCT
		WO.PART_ID, WO.BASE_ID, WO.LOT_ID, WO.SUB_ID, 
		WO.USER_3, WO.DESIRED_WANT_DATE,
		REQ.WORKORDER_BASE_ID, REQ.WORKORDER_LOT_ID, 
		REQ.WORKORDER_SUB_ID
FROM	WORK_ORDER WO 
 
		INNER JOIN 
		(SELECT R.WORKORDER_BASE_ID, R.WORKORDER_LOT_ID, R.WORKORDER_SUB_ID,
				R.REQUIRED_DATE
		FROM REQUIREMENT R
		) REQ ON REQ.WORKORDER_BASE_ID = WO.USER_3 AND REQ.REQUIRED_DATE = WO.DESIRED_WANT_DATE
	
WHERE	WO.PART_ID = 'R045AONGABB074DDY001AAA1H'
		AND WO.TYPE = 'W' 
 
RESULT:
 
R045AONGABB074DDY001AAA1H	78567	5	0	78414	2009-07-09 00:00:00.000	78414	2	2

Open in new window

Comment
Watch Question

Top Expert 2009

Commented:
Likely you need indexes.

Are there indexes on the join columns?

Particularly I see:

WO.DESIRED_WANT_DATE

Author

Commented:
The joining columns I don't believe have indexes.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
There's no telling what the execution plan looks like without looking at it, (it might not be doing a table scan, it can be pretty intelligent) but your derived table query of requirements does not include a where clause although your test #1 query does.  I would avoid the subquery form and do a simple join, and look at the execution plan.  If you don't use the estimated plan display,  you should, with a little practice it becomes a lot easier to read and optimize your plans.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
After I created indexes it is much faster now.  Take only about 2-3 seconds.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.