1

So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts table, but only update the records which are needed and keep track of which ones.

This is the code I am using to find changes :

SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price

This is the code I am using to update :

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1

When the two pieces of code are run separately they complete in less than a second each. However when combined like below, the query runs for HOURS !

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1
WHERE ap.sku IN (
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
);

Below is the structure

ActiveProducts.Product_id - PrimeKey
ActiveProducts.name - Index
ActiveProducts.sku - Index

XMLImport.Product_id - PrimeKey
XMLImport.name - Index
XMLImport.sku - Index

Server version: 5.5.44-MariaDB MariaDB Server

Tables: InnoDB

1
  • what RDBMS are you using ? Please tag your question with proper RDBMS and include your version as well. Also, include some test data, create table, etc. You can use sqlfiddle.com for a repro if possible (make sure you select proper RDBMS on it).
    – Kin Shah
    Sep 30 '15 at 19:19
1

Why don't you just build a temp table and use the results to update. This is bad form. It's got to build the Subquery first, then assemble that in the WHERE Clause, then it's going to update every record to find a match.

Instead just INNER JOIN to a temp table that does the first part, or a CTE if you're using SQL Server and can't have a multi-step process.

WITH SkuList AS
(
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
)
UPDATE ActiveProducts ap INNER JOIN SkuList xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1
4
  • I understand for larger queries using a temporary table would be a better idea, but is this necessary for a few records? This is what I don't get, the sub-query completes in 1/10 of a second, the update takes 3/10 of a second. Yet combined they were still running after 2 and a half hours. For new I will go with a temporary table since MariaDB doesn't support with.
    – user76664
    Sep 30 '15 at 19:38
  • @AndrewH the subquery is executed FOR EACH ROW of the outer query (the updated table), that is 0.1s * some tens of thousands? When you put a subquery inside IN() it is executed as "dependent subquery" (aka correlated) and thats bad. You can just rewrite it to a derived table (that means "inlined temporary table") and join to it, the WITH is not needed for that.
    – jkavalik
    Sep 30 '15 at 19:49
  • (maybe I am wrong and update won't allow you to access the updated table in a subquery that way, the explicit temp table is a good solution for that)
    – jkavalik
    Sep 30 '15 at 19:51
  • @jkavalik OP Changed his original question so I can't account for accuracy on this. I don't see why you can't utilize CTE. He also updated tags so MySQL has no CTE. I'm going to remove this shortly.
    – Hituptony
    Sep 30 '15 at 20:39
0

Okay, so I've gone with this...

TRUNCATE TABLE SkuTemp;

INSERT INTO SkuTemp (sku)
SELECT sku FROM (
  SELECT name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description FROM ActiveProducts
  UNION ALL
  SELECT name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description FROM XMLImport
) tbl
GROUP BY name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description
HAVING count(*) = 1;

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku 
SET ap.name = xml.name, ap.type = xml.type, ap.size = xml.size, ap.colour = xml.colour, ap.weight = xml.weight, ap.instock = xml.instock, 
ap.quantity = xml.quantity, ap.price = xml.price, ap.image1 = xml.image1, ap.image2 = xml.image2, ap.image3 = xml.image3, ap.image4 = xml.image4, 
ap.image5 = xml.image5, ap.image6 = xml.image6, ap.description = xml.description, ap.updated = 1 
WHERE ap.sku IN (SELECT sku FROM SkuTemp);

Works and executed in under 2 seconds.

0

DISTINCT and GROUP BY are redundant. Do just the GROUP BY.

IN ( SELECT ... ) is very inefficient.

FROM ( SELECT ... ) can be inefficient because of the lack of an index on the tmp table it enerates for you.

UPDATE ... WHERE sku IN ( SELECT ... ) can (should) be turned into a multi-table UPDATE using a JOIN. That will further help your final suggestion.

You can probably get rid of the tmp table, too, by

UPDATE ap
       JOIN xml
       JOIN ( SELECT ... FROM UNION ... GROUP BY ... DISTINCT ) skus

(This assumes that ap and xml have indexes on sku.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy