Posted by Erland Sommarskog on 06/15/07 16:56

Wolfing (wolfing1@gmail.com) writes:
> I have a table that every 30 minutes needs to be repopulated from
> another table that is recreated from scratch just before.
> What I did was this:
> CREATE PROCEDURE BatchUpdProducts AS
> begin transaction
> delete products
> insert into products
> select * from productsTemp
> commit transaction
> GO
>
> This takes about 30 seconds to run. I tried it doing it with a cursor,
> row by row, but it took like 30 minutes to run instead. The problem
> is with the fast approach is, once in a while I get a deadlock error
> in different areas trying to access the products table. Using SQL
> Server 2000 by the way.

If it is OK for you that the batch process is the deadlock victim, you
can add "SET DEADLOCK_PRIORITY LOW" first in the procedure.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]