HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.relationalserver.performance » How many commands in a transaction
Topic: How many commands in a transaction
Hello everybody,
I need to update over than 1 milion rows in a large table and this
update is going to affect the primary key column. My team developed a program
that generates a script containing all the UPDATE statements that must be
executed on each row, i.e., there are over than 1 milion UPDATE statements. I
was wondering about the best way to execute these statements and a question
came into my mind: is there a best number of statements to be grouped in a
single transaction? What is the best choice: having few transactions with a
large number of statements or several transactions with few statements? Does
this impact the overall performance of my SQL Server 2005? Thanks in advance.
Fabio Hemylio
--
Message posted via http://www.sqlmonster.com
Replies below ↓
Replies
Re: How many commands in a transaction
You definitely want to batch them up, otherwise log flushes for all of the
implicit transactions will kill performance. Also make sure that whatever
batching you do has some error checking around it to roll back and stop if
something breaks. Sure hope all referencing entities have indexes on them
too or it's gonna be ugly.
I would say somewhere between 10K and 50K statements for each batch. YMMV.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Fabio Hemylio via SQLMonster.com" <u41023@uwe> wrote in message
news:7efe86821e46f@uwe...
> Hello everybody,
>
> I need to update over than 1 milion rows in a large table and this
> update is going to affect the primary key column. My team developed a
> program
> that generates a script containing all the UPDATE statements that must be
> executed on each row, i.e., there are over than 1 milion UPDATE
> statements. I
> was wondering about the best way to execute these statements and a
> question
> came into my mind: is there a best number of statements to be grouped in a
> single transaction? What is the best choice: having few transactions with
> a
> large number of statements or several transactions with few statements?
> Does
> this impact the overall performance of my SQL Server 2005? Thanks in
> advance.
>
> Fabio Hemylio
>
> --
> Message posted via http://www.sqlmonster.com
>
Re: How many commands in a transaction
Fabio Hemylio via SQLMonster.com (u41023@uwe) writes:
> I need to update over than 1 milion rows in a large table and this
> update is going to affect the primary key column. My team developed a
> program that generates a script containing all the UPDATE statements
> that must be executed on each row, i.e., there are over than 1 milion
> UPDATE statements. I was wondering about the best way to execute these
> statements and a question came into my mind: is there a best number of
> statements to be grouped in a single transaction? What is the best
> choice: having few transactions with a large number of statements or
> several transactions with few statements? Does this impact the overall
> performance of my SQL Server 2005? Thanks in advance.
In additions to Kevin's reply, if you can change the script generator so
that you update more than one row at a time, that's when you can get the
real speed out this.
--
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
Re: How many commands in a transaction
Hi guys,
First of all, thanks for your replies. I´ll ask the development team to
wrap each 20K-statement block within a transaction and to include error
checking in order to identify which one has failed, if any.
Erland, I believe I can´t update more than one row at time because the
updates will be executed on the primary key column.
Regards,
Fabio Hemylio
Erland Sommarskog wrote:
>> I need to update over than 1 milion rows in a large table and this
>> update is going to affect the primary key column. My team developed a
>[quoted text clipped - 6 lines]
>> several transactions with few statements? Does this impact the overall
>> performance of my SQL Server 2005? Thanks in advance.
>
>In additions to Kevin's reply, if you can change the script generator so
>that you update more than one row at a time, that's when you can get the
>real speed out this.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/200801/1
Re: How many commands in a transaction
hemylio via SQLMonster.com (u41023@uwe) writes:
> Erland, I believe I can´t update more than one row at time because
> the updates will be executed on the primary key column.
You should be able update multiple rows even if you change the value of
the PK. Unless there are triggers that needs to be able to correlate
the new and old values.
--
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