[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

SQL Query Performance issue

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

0
holemania
Asked:
holemania
3 Solutions
 
mrjoltcolaCommented:
Likely you need indexes.

Are there indexes on the join columns?

Particularly I see:

WO.DESIRED_WANT_DATE
0
 
holemaniaAuthor Commented:
The joining columns I don't believe have indexes.
0
 
rgc6789Commented:
You may also want to try adding nolock. If waiting for uncommitted data during updates, etc. isn't that crucial this can greatly increase query speed.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mrjoltcolaCommented:
Always create indexes on join columns.

Also make sure indexes exist on all predicate columns. (where col = 'ABC')

Try viewing the query execution plan. In SQL Server Mgmt Studio you can highlight the query, right-click -> Display Estimated Execution Plan

0
 
folderolCommented:
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.
0
 
JR2003Commented:
You don't need the subquery, just a normal inner join.
The original query is taking a long time because it is joining every row in REQUIREMENT table.
Combine both:
SELECT DISTINCT
       WO.PART_ID,
       WO.BASE_ID, 
       WO.LOT_ID, 
       WO.SUB_ID, 
       WO.USER_3, 
       WO.DESIRED_WANT_DATE,
       R.WORKORDER_BASE_ID, 
       R.WORKORDER_LOT_ID, 
       R.WORKORDER_SUB_ID
  FROM WORK_ORDER WO 
 INNER JOIN REQUIREMENT R
         ON R.WORKORDER_BASE_ID = WO.USER_3 
        AND R.REQUIRED_DATE = WO.DESIRED_WANT_DATE
 WHERE WO.PART_ID = 'R045AONGABB074DDY001AAA1H'
   AND WO.TYPE = 'W' 

Open in new window

0
 
holemaniaAuthor Commented:
After I created indexes it is much faster now.  Take only about 2-3 seconds.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now