Re: Command object and batching stored procedure calls

What database are you using that you have never seen semicolon-delimited
batched sql statements documented? It is certainly documented in SQL Server
Books OnLine.

How many statements are you thinking of batching? Perhaps ADO is not the
best tool for what you are doing ...

Michael Viking wrote:
Hi, thanks for the response.

We want to batch them up because connection-pooling or not, there's
still network latency. We're finding that when operating in a WAN
environment, every single SQL call is expensive and it's not due to
the number of bytes sent, it's due to a fixed cost of encryption,
hand-shaking, etc. for each network round-trip. Two calls bundled
into one network packet of size X is a lot faster than two separate
roundtrip calls of size X/2.

Am I missing something or misinterpreting what I'm seeing?

Also, I have seen batching being mentioned in newsgroups and using a
semicolon as a delimiter, like you've listed. That seems to be
undocumented and I don't understand how it works. I've investigated
this method and it clearly has a limit to how many statements it will
accept and I'm not sure if it's "length of string", "number of
statements" or some combination. At any rate, the algorithm is
unknown and therefore I can't really trust this method. Many times
only some subset of the calls actually show up in the database - and
no errors are ever thrown. It appears to work but rows will be
missing.

Thanks in advance for any input you can provide,
-Michael Viking

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23xXCekwcIHA.3572@xxxxxxxxxxxxxxxxxxxxxxx
Michael Viking wrote:
Greetings,
I've tried searching online for what I think must be easy to do, but
so far no luck. I have to call a stored procedure over and over as
data comes in. I'd like to queue the calls up on the client side and
send them in one batch to avoid all the network traffic but I can't
figure out how.

That's because there is no way without using dynamic sql to
concatenate all the procedure calls into a single string

sql="exec proc 'parm1';...;exec proc 'parmN'"
cmd.commandtype=adCmdText
cmd.commandtext = sql
cmd.execute ,,adExecuteNoRecords

Of course, doing so loses the value provided by using parameters:
security, strong datatyping, etc. Also, not all dbrms support batched
calls.

I'm not sure what you think you would be gaining by batching the
statements up. Session pooling should guarantee connection re-use if
you are worried about spawning too many connections.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages