Best way to update 1.000.000 rows?!

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 21, 2012 at 5:24 am

  #267045

  Hi,

  I have a table with 1.000.000 or more. It stores the stocks for each product on a store.

  Imagine I buy 10 units of each product for each store and want to update the stock...

  UPDATE StoreProducts SET Stock = Stock + 2 takes, on my test machine, about 1:20 minutes...

  There's an index on ProductID including Stock so I can join with the Products table and know how many there are on all stores.

  Using:

  ALTER INDEX IDX_ProdutStorages_Stock ON ProdutStorages DISABLE

  UPDATE ProdutStorages SET Stock = Stock + 2

  ALTER INDEX IDX_ProdutStorages_Stock ON ProdutStorages REBUILD

  is faster 20sec....

  Is there any other way of doing this?!

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 21, 2012 at 7:16 am

  #1540675

  UPDATE StoreProducts SET Stock = Stock + 2

  UPDATE ProdutStorages SET Stock = Stock + 2

  How many tables are you updating?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 21, 2012 at 7:25 am

  #1540683

  ChrisM@Work (9/21/2012)

  ---

  UPDATE StoreProducts SET Stock = Stock + 2

  UPDATE ProdutStorages SET Stock = Stock + 2

  How many tables are you updating?

  I wrongly named the 2nd query... It's just StoreProducts.

  ---

  
  If you need to work better, try working less...

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 21, 2012 at 8:25 am

  #1540728

  Can you post the CREATE INDEX script?

  If you're updating the whole table, then nonclustered indexes won't help performance and will impede performance if they too require updating.

  Batching the update could help - fewer resources are required for each batch.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• PiMané

  SSChampion

  Points: 13965

  More actions

  September 21, 2012 at 8:44 am

  #1540746

  the index is as follows:

  CREATE NONCLUSTERED INDEX [IDX_ProdutStorages_Stock] ON [dbo].[ProdutStorages]

  (

  [Stock] ASC

  )

  INCLUDE ( [Product])

  This index was created to determine which products had stock bellow X...

  There's another index that's the reverse of that one: on Product and include Stock. That one is used on a INNER JOIN with the products table to get SUM(Stock).

  ---

  
  If you need to work better, try working less...