Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query Performance issue

Posted on 2009-07-09
8
Medium Priority
?
173 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

0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24818507
Likely you need indexes.

Are there indexes on the join columns?

Particularly I see:

WO.DESIRED_WANT_DATE
0
 

Author Comment

by:holemania
ID: 24818556
The joining columns I don't believe have indexes.
0
 
LVL 5

Assisted Solution

by:rgc6789
rgc6789 earned 400 total points
ID: 24818603
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1200 total points
ID: 24818642
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
 
LVL 19

Expert Comment

by:folderol
ID: 24819184
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
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 400 total points
ID: 24819453
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
 

Author Comment

by:holemania
ID: 24825188
After I created indexes it is much faster now.  Take only about 2-3 seconds.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question