Re: Command object and batching stored procedure calls

I'm using SQL Server. For example, the books online do not list a
semi-colon as a delimiter in the help for the 'CommandText' property of the
ADO Command object. For example here:
http://msdn2.microsoft.com/en-us/library/ms675958(VS.85).aspx. What should
I be reading if not that?

22 separate statements is the most I've been able to get in one CommandText,
but I've seen as few as 15 be the maximum. It doesn't seem to be related
exactly to the length of CommandText, either, because I've tried cutting
them off by string length. As I've said, it appears to be magic formula.
I'd appreciate it if you could point me to the Books Online where it's
documented how many can be sent via a Command object, or what the maximum
length is or something.

Perhaps ADO isn't the best tool...If I need to run a stored procedure on the
DB every time a line of data comes in over a port, what do you recommend?
Naively it's safe and easy to use a command object and execute the stored
procedure every time a line comes in. Easy to understand, program and it's
safe. Performance: dismal. Obvious answer: queue them up somehow and batch
process them. I'm posting to get ideas...What's the best tool?



"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23bD6Gr1cIHA.2000@xxxxxxxxxxxxxxxxxxxxxxx
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

  • Re: Best Way to Test Against a Database
    ... that I have my CommandText, ... If your database client library (and the server) supports the concept of "prepared" statements, this would be a very easy way to verify the correctness of the SQL statements without actually executing the command. ... "Preparing" a statement will send the SQL to the server and the server can use the SQL to build a query plan. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Best Way to Test Against a Database
    ... that I have my CommandText, ... "Preparing" a statement will send the SQL to the server and the ...
    (microsoft.public.dotnet.languages.csharp)
  • TADODataset.Parameters.Refresh results in incorrect parameter names
    ... I am changing the CommandText of a TADODataset at runtime after ... rebuilding the SQL to change the parameters. ... where ([CONTEXT] =:CONTEXT) ...
    (borland.public.delphi.database.ado)
  • RE: Change Pivot Tables query
    ... CommandText or SQL value whose length is between 242 characters and 257 ... Excel nor by using Excel VBA to edit the CommandText from the workbook ...
    (microsoft.public.excel.programming)
  • Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.
    ... MS ADO passes SQL directly to OLEDB provider. ...
    (borland.public.delphi.database.ado)