Re: Command object and batching stored procedure calls

Thanks for the great reply. Please see inline:

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23VxNYs2cIHA.4844@xxxxxxxxxxxxxxxxxxxxxxx
Michael Viking wrote:
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?

First off, you are reading the ADO documentation: you should be reading
the
SQL Server documentation.

Good point. I support both SQL 2000 and SQL 2005 and I looked for
'delimiter' in 2000's books online and didn't find much except in a blurb
about "Commands Generating Multiple-Rowset Results" in the "OLE DB and SQL
Server" section. There's nothing about "65,536 * Network Packet Size" like
you mention below. I did find it in the 2005 books online, but since I need
to support both, I'm not sure what to do.

<snip>

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.

I've never looked because it's nothing I have ever considered doing.
However, on this page

(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561
-d1a74dd824d7.htm),
you can see that the maximum batch size is "65,536 * Network Packet Size".
It does not sound from your description that you are getting anywhere near
this limit. What error are you getting that makes you think you've
exceeded
a limit?

As I said, no error is ever returned and as far as the client knows
everything worked fine. I know it doesn't work by inspecting the database.
For example, I send 200 rows of data to the client and I have them batch
them up via:
if ( length of command text > some number )
execute the whole she-bang

What I find is that for various "some number" values I might get 80 rows in
the database, or 22, or whatever. It seems like for my purposes "some
number" needs to be between 1000 and 2000 characters, much, much less than
"65,536 * Network Packet Size" in order to reliably get the full 200 rows.
And again, as near as I can tell, whatever formula ADO/ODBC is using, it
appears to be a combination of the number of batched calls and the string
length, because different commands give different results. Did this make
sense? It's very disappointing that it will not put in all the rows without
returning an error.


See below about telling ADO that you are not expecting records to be
returned by your batch's execution. Maybe that is related to your symptom.


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.

One way to improve performance using the Command object is to specify
adExecuteNoRecords in the <options> argument when calling the Execute
method. If you do not specify that, ADO, by default, creates a recordset
object behind the scenes to receive any resultsets returned from the
execution. You need to tell ADO that you are not getting any records back.

We are doing this, but thanks for the suggestion in case we weren't!


Obvious
answer: queue them up somehow and batch process them. I'm posting to
get ideas...What's the best tool?

This is not a situation I've ever encountered; however ...

You might consider writing the data to a csv file, which can be
periodically
imported into SQL via bcp, DTS or SSIS (... what version of SQL are you
using?), then a T-SQL process can loop through the data, using it to run
the
stored procedure.

Unfortunately this is not an option for us.


Another option is to go to a level below ADO and use OLE DB directly. See
http://msdn2.microsoft.com/en-us/library/ms722784(VS.85).aspx


Also not an option. Perhaps the next major release.

Thanks again for your responses,
-Michael


.

Relevant Pages

  • Re: Bill Vaughns "ADO Command Strategies" from 10/2000
    ... Middle tier components are now pre-compiled so the one-time cost of building a Command object is miniscule when compared to using Refresh to get the server to return metadata to ADO so it can build them. ... The ADO Command object's Parameters collection manages input, output, ...
    (microsoft.public.data.ado)
  • RE: Cursors with ADO
    ... Take a look at the Parameters collection in the ADO Command object. ... I would start with the BOL and look up the ADO Parameter object. ... > With cmd1 ...
    (microsoft.public.vb.database.ado)
  • Re: Bulk Insert
    ... Can you execute an sql statement in ADO? ... Here's part of my OLEDB code that performs the bulk insert operation: ... // The command requires the actual text as well as an indicator of its ...
    (microsoft.public.sqlserver.programming)
  • Re: Problems with ADO Interop and dBASE file
    ... All the hits I found on this error relate to people using ADO.NET, not ADO, so hopefully someone can help me. ... recs, ExecuteOptionEnum.adExecuteNoRecords); ... // YEAR must be escaped because apparently it's a reserved word in dBASE. ... I'd use Profiler to see the exact command that was being passed and diagnose problems from there. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: "Insert Into tbl1 Values(" & dynamicParams & ")" ?
    ... I am familiar with Command ... that I am using com ADO in a Non ... Command object that I wasn't familiar with before I start ... >> I would like to make this dynamic rather than hardcoding ...
    (microsoft.public.dotnet.languages.vb)