Solved

SQL Query Performance issue

Posted on 2009-07-09
8
167 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
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.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

943 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

9 Experts available now in Live!

Get 1:1 Help Now