Re: Command object and batching stored procedure calls

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. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.




.

Relevant Pages