Quick Question: SqlDataAdapter.UpdateBatchSize and stored procedures?

3 Jul 2006 10:43 AM

windsurfing_stew

Hi,

Quick question. I've been playing with SqlDataAdapter.UpdateBatchSize
and am puzzled by the results. Firstly I've got profiling on in SQL
Server 2005. Now, when I set the UpdateBatchSize=0 I find that
profiler still shows every update as a separate statement. This is not
what I would have expected. This is the same whether it is calling a
stored proc or a sql statement.

I've read the MSDN help and seem to be following their guidelines.

So, my question is, how should I be using UpdateBatchSize to really get
the most out of inserting, updating and deleting large volumes of data?
Is it actually possible to get the gains I'm looking for?

Stew

3 Jul 2006 1:01 PM

Sahil Malik [MVP C#]

Stew,

The batching is implemented at TDS level. There are various reasons for
that, such as the upper limit on Max # of parameters on a SQL command, and
the possibility of parameter names clashing with each other. You are however
preventing roundtrips by doing batching, only SQL Profiler isn't showin' em

:)

- Sahil Malik
http://www.winsmarts.com
http://blah.winsmarts.com

<windsurfing_s***@yahoo.com.au> wrote in message
Show quote

news:1151923411.423850.66270@m79g2000cwm.googlegroups.com...
> Hi,
>
> Quick question. I've been playing with SqlDataAdapter.UpdateBatchSize
> and am puzzled by the results. Firstly I've got profiling on in SQL
> Server 2005. Now, when I set the UpdateBatchSize=0 I find that
> profiler still shows every update as a separate statement. This is not
> what I would have expected. This is the same whether it is calling a
> stored proc or a sql statement.
>
> I've read the MSDN help and seem to be following their guidelines.
>
> So, my question is, how should I be using UpdateBatchSize to really get
> the most out of inserting, updating and deleting large volumes of data?
> Is it actually possible to get the gains I'm looking for?
>
> Stew
>

4 Jul 2006 11:01 PM

windsurfing_stew

Thanks Sahil,

Is there any way to observe it is truely batching or not?

Does SqlDataAdapter batching work with stored procedures too?

Stewart

5 Jul 2006 12:48 AM

Sahil Malik [MVP C#]

The answers are "Yes" and "Yes".

#1 - you can do so using a network packet sniffer.
#2 - is a Yes (dOh!). :)

- Sahil Malik
http://www.winsmarts.com
http://blah.winsmarts.com

<windsurfing_s***@yahoo.com.au> wrote in message
Show quote

news:1152054079.745072.3870@p79g2000cwp.googlegroups.com...
> Thanks Sahil,
>
> Is there any way to observe it is truely batching or not?
>
> Does SqlDataAdapter batching work with stored procedures too?
>
> Stewart
>