Solved

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

Posted on 2013-05-23
5
226 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 31

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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…
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.

746 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

12 Experts available now in Live!

Get 1:1 Help Now