Quick Question: SqlDataAdapter.UpdateBatchSize and stored procedures?

  • Thread starter Thread starter windsurfing_stew
  • Start date Start date
W

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
 
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
 
Thanks Sahil,

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

Does SqlDataAdapter batching work with stored procedures too?

Stewart
 
Back
Top