Solved

SQL Query Performance issue

Posted on 2009-07-09
8
171 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 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 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