Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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

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
bignadad
Asked:
bignadad
2 Solutions
 
bignadadAuthor Commented:
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
 
awking00Commented:
Can you describe the JD and amipartnumbers tables?
0
 
bignadadAuthor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
ianmills2002Commented:
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now