Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

718 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