Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-23
5
Medium Priority
?
239 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 1000 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

963 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