CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
ihorko
ihorko

Reputation: 6945

UPDATE takes long time on SQL Server

I have tables "Products" 300,000 rows, and "Imported_Products" 4,000 rows. Also I have view "View_Imported_Products" which is based on "Imported_Products" to make it well-formed.

When I run UPDATE:

UPDATE Products SET DateDeleted = GETDATE()
WHERE Suppiler = 'Supplier1' AND SKU NOT IN (SELECT SKU FROM View_Imported_Products)

It takes a lot of time about 1 minute even if I run it second time and no rows update.

I have added non-clustered indexes on Products.SKU and View_Imported_Products.SKU, also I changed NOT IN to NOT EXISTS

UPDATE Products SET DateDeleted = GETDATE() FROM Products P
WHERE Supplier = 'Supplier1' AND NOT EXISTS (SELECT SKU FROM View_Imported_Products I WHERE P.SKU=I.SKU)

But it still takes about 16 seconds to run.

What I'm doing wrong, and how to improve that update to run it fast.

Appreciate any help.

Thank you

UPDATED

  1. SELECT SKU FROM View_ImportedProducts - runs very fast, it takes 00:00:00 sec
  2. Changed query to use LEFT JOIN, instead NOT EXISTS - doesn't help much
  3. SELECT * FROM Products AS P
WHERE P.Supplier = 'Supplier1' AND DateDeleted IS NULL
AND 
NOT EXISTS
(
    SELECT 
        SKU 
    FROM View_ImportedProducts AS I 
    WHERE P.SKU = I.SKU
)
    takes also long time to execute

Upvotes: 1

Views: 787

Answers (3)

ihorko
ihorko

Reputation: 6945

Resolved it by added Non-clustered index to "Imported_Products".SKU field. My mistake was I added non-clustered index on "View_Imported_Products".SKU, instead of original table. Thank you all for help and replies!

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Why not using joins

UPDATE Products SET DateDeleted = GETDATE()     FROM Products P
Left join View_Imported_Products I On     P.SKU=I.SKU
Where I.Sku is null

And you have to create non clustered indexes on p.sku and i.sku

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

If a lot of rows (tens of thousands) are being updated you are creating a big hit on the log. If this is so you want to update either 1000 or 10000 rows at a time and then commit. Your transaction will have a much smaller impact on the transaction log and will execute a lot faster.

Upvotes: 0

Related Questions