Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Query Performance issue

Posted on 2009-07-09
8
Medium Priority
?
175 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
7 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

607 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