Solved

SQL Query Performance issue

Posted on 2009-07-09
8
166 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
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 100 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 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 100 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now