Solved

Any way to condense down this query? Taking 1 minute to run it

Posted on 2013-05-23
5
230 Views
Last Modified: 2013-05-28
I have this query below that is comparing 9 different columns to get result.

Its taking one minute to do it which is too long.

Any way to consolidate it? Im not sure how i can get less columns with that im trying to do

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item,AMIF.Item,AMIG.Item,AMIH.Item,AMII.Item,AMIJ.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description,AMIF.Description,AMIG.Description,AMIH.Description,AMII.Description,AMIJ.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem
LEFT JOIN JD as JDF 
ON I.OEMPartNumber = JDF.OEMSubNumber5 
LEFT JOIN amipartnumbers as AMIF 
ON JDB.OEMSubNumber2 = AMIF.OEMItem
LEFT JOIN JD as JDG 
ON I.OEMPartNumber = JDG.OEMSubNumber6 
LEFT JOIN amipartnumbers as AMIG 
ON JDB.OEMSubNumber2 = AMIG.OEMItem
LEFT JOIN JD as JDH 
ON I.OEMPartNumber = JDH.OEMSubNumber7 
LEFT JOIN amipartnumbers as AMIH 
ON JDB.OEMSubNumber2 = AMIH.OEMItem
LEFT JOIN JD as JDI 
ON I.OEMPartNumber = JDI.OEMSubNumber8 
LEFT JOIN amipartnumbers as AMII 
ON JDB.OEMSubNumber2 = AMII.OEMItem
LEFT JOIN JD as JDJ 
ON I.OEMPartNumber = JDJ.OEMSubNumber9 
LEFT JOIN amipartnumbers as AMIJ 
ON JDB.OEMSubNumber2 = AMIJ.OEMItem ;

select * from imports

Open in new window

0
Comment
Question by:bignadad
5 Comments
 
LVL 2

Author Comment

by:bignadad
ID: 39191803
Just tested the following query and it only takes about 5 seconds. Is there that much difference by removing 5 columns?

UPDATE imports 
SET imports.AMIPartNumber = 
coalesce(AMIA.Item,AMIB.Item,AMIC.Item,AMID.Item,AMIE.Item), 
Imports.AMIDescription = 
coalesce(AMIA.Description,AMIB.Description,AMIC.Description,AMID.Description,AMIE.Description) 
FROM imports as I 
LEFT JOIN JD as JDA 
ON I.OEMPartNumber = JDA.OEMPartNumber 
LEFT JOIN amipartnumbers as AMIA 
ON JDA.OEMPartNumber = AMIA.OEMItem 
LEFT JOIN JD as JDB 
ON I.OEMPartNumber = JDB.OEMSubNumber 
LEFT JOIN amipartnumbers as AMIB 
ON JDB.OEMSubNumber = AMIB.OEMItem 
LEFT JOIN JD as JDC 
ON I.OEMPartNumber = JDC.OEMSubNumber2 
LEFT JOIN amipartnumbers as AMIC 
ON JDB.OEMSubNumber2 = AMIC.OEMItem 
LEFT JOIN JD as JDD 
ON I.OEMPartNumber = JDD.OEMSubNumber3 
LEFT JOIN amipartnumbers as AMID 
ON JDB.OEMSubNumber2 = AMID.OEMItem 
LEFT JOIN JD as JDE 
ON I.OEMPartNumber = JDE.OEMSubNumber4 
LEFT JOIN amipartnumbers as AMIE 
ON JDB.OEMSubNumber2 = AMIE.OEMItem ;

select * from imports

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39192023
Can you describe the JD and amipartnumbers tables?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39192043
JD
---------------------------
OEMPartNumer    | OEMDescription | OEMSubNumber| OEMSubNumber2 (etc)
AR65123         |   Gear       |     AR77530     |     AR25684
AR12345         |   Gear       |     AR56242     |     AR42154

amipartnumbers
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR56242       | AR42154   | Spindle

Open in new window

0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 39192194
You probably need to add several indexes to the JD and Import tables.  If you run the execution plan it will make suggestions on which ones to add.
0
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 250 total points
ID: 39193239
General rule or writing queries is that you should only, if possible, have around 4-6 table joins. Basically, the fewer the better. So yes, removing all of those table joins, it can make that much difference.

As mentioned by BriCrowe, see what indexes that you might be able to include on the tables that would assist you update.

Not knowing exactly what you are trying to achieve with the query, and how the data links up, it is a little difficult to workout a better way to achieve the same result.
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.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

713 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