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.

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

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

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)

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.