batching an update, use TRANSACTION?

I have a storedproc that does several UPDATEs to a table, some of which fire
and some of which don't based on various IFs through the proc. There's a
maximum f three, it's not THAT complex.
However there is a trigger on the table that records all UPDATEs into a
separate auditing log. I would like there to be only one trigger fire
regardless if one, two or three of the UPDATEs were called.
Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
proc.
MauryIf I understand you correctly, TRANSACTION will not do it. Can you put in
some logic in your trigger to check whether a previous UPDATE has already
fired the trigger?
Linchi
"Maury Markowitz" wrote:
> I have a storedproc that does several UPDATEs to a table, some of which fire
> and some of which don't based on various IFs through the proc. There's a
> maximum f three, it's not THAT complex.
> However there is a trigger on the table that records all UPDATEs into a
> separate auditing log. I would like there to be only one trigger fire
> regardless if one, two or three of the UPDATEs were called.
> Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
> proc.
> Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
I guess, but only with peril.
Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
Can I perhaps wrap the individual fields of the update in some sort of
conditional, and thereby combine them into one larger statement? Everything
is already loaded into local vars.
Maury

Batch updates with SQL CLR

Unfortunately batch updates (i.e. setting SqlDataAdapter.UpdateBatchSize to >1) are not possible with ADO.NET using a context connection. Does anyone know why it's not possible? Will it be implemented or allowed in a future version of .NET?

Here's what I'm currently doing:
I have a C# stored procedure, using a context connection, that updates all the records in a table (typically a million records) by repeatedly:
- sequentially reading a group of records (1000 at a time) into a SqlDataAdapter (using a 'select top row_number' -type statement that prevents re-querying processed records);
- performing complex processing on each record and writing the results back to the adapter (several fields of each record are updated);
- updating the table when each group has been processed.

This works, but it's not quite as fast as I'd have hoped for and is only slightly slower if using a non-context connection from an external application. I'd like to enable batch updating to increase performance (I actually get much faster performance from an external application that batch updates using a non-context connection, say 20% faster!)... but of course I can't with SQL CLR.

Any ideas on how I can improve performance of my updating?

Thanks in advance,

Graham

If I understand correctly, you're doing record by record calculation. So all required info is present within the record. If I would try to solve this I would have started with a CLR function, not a SP. So my statement would look something like:

Update BigTable Set CalcField = CLRFunc.ToughCalc(fieldX, fieldY, fieldZ)

I'm not sure if this would be faster, but it would be set based (not all functions will produce set bases solutions)

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done bu
t
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!
Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>
|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...[vbcol=seagreen]
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done but
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>
|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...[vbcol=seagreen]
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!
|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>
|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...[vbcol=seagreen]
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
Dan
Are you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could
be slowing down as the transaction log grows. Try breaking the updates into
smaller logical batches (say 1000 inserts/updates at a time) and issue a co
mmit. This may solve your
problem....
Brad Feaker
Database Administrator

Batch performance degradation - SQL Server 2000

I am running a VB.net console (batch) application that performs 50,000 sets
of reads, inserts, and updates to numerous SQL Server 2000 database tables.
Each individual set is mutually exclusive from the previous or subsequent
sets. I am monitoring the average processing time per set. When I start
the program, the average processing time is very small. However, the
average processing time slowly grows by about 67% by the end of the 50,000
sets. If I start another group of 50,000 right away, the processing time at
the beginning is again very fast. But, again, the performance degrades as
the program runs.
I've added more RAM to the server, increased the minimum buffer pool size,
and removed all of the constraints from the tables being inserted or
updated. None of these have eliminated nor reduced the degradation. I've
generated traces using Profiler, but have not been able to identify a stored
procedure that takes longer and longer to execute as the program progresses.
Can anyone suggest any other places to investigate?
Thanks,
DanAre you updating all 50K records as a single transaction? If so, you could be slowing down as the transaction log grows. Try breaking the updates into smaller logical batches (say 1000 inserts/updates at a time) and issue a commit. This may solve your problem...
Brad Feake
Database Administrator

Batch Failing

I run two batches at night time:
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...I run two batches at night time:1) A batch that updates all the =records one by one to calculate special interest on accounts.2) The =databas optimization batch that cleans up pages, free space, rebuilds =indexes, etc.My batch 1 is failing with this error: "Key column =information is insufficient or incorrect. Too many rows were affected by =update." I do know that batch 2 is running in tbe background still. They both take =around 3-4 hours each. It's hard to schedual them apart enough to keep =them from running at the same time because their run lengths are so unpredictable. Any ideas?My updates are using ado 2.7Jason Roozee

---

--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2c0e01c373bb$e9=832330$a401280a@.phx.gbl...Yes, all the tables have a primary key and it's included in the select =list of fields in my ADO record set..."SET LEVEL" processing' Explain.Jason>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jaso=n Roozee" wrote in message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...>>I run two batches at night time:>>1) A batch that updates =all the records one by one to >calculate special interest on accounts.>>2) The databas optimization batch that cleans =up pages, >free space, rebuilds indexes, etc.>>My batch 1 is =failing with this error: "Key column >information is insufficient or =incorrect. Too many rows >were affected by update." I do know that batch 2 =is >running in tbe background still. They both take around 3-4 >hours each. It's hard to schedual them apart enough to =>keep them from running at the same time because their run >lengths are so unpredictable. >>>Any ideas?>>My =updates are using ado 2.7>>Jason Roozee>

---

--=_NextPart_000_0166_01C3739A.E6D14CA0--