Very Computer

I need to write store procedure that will run about 500 hundreds time per
second.
NT 4.0 Server, Option Pack 4.0 IIS 4.0, ADO 2.1,SQL 7.0
In first I try to write this code:

With GlobalCommand

    .ActiveConnection = clsdb.Db
    .CommandType = adCmdStoredProc
    .CommandText = "sp_add_Location"






    .Execute
End With

i ran this 1000 time and it took about 45 second.

 mvardb.Execute "sp_add_location '054341432','Message',12.3,10.2,'" & Now &
"',1"
on the other hand I ran this code 1000 times and it took between 6 to 8
second.

When I am running from Query Analyzer in loop(sp_add_location_1000 ) the
loop is in the store procedure it took 2 second.

Can I optimize the ADO more than the second choice.

Richard

Doing a batch job can nearly enter your speed to that of bulk copy.
But the batch must be done directly on the table

So
Set Rs = CreateObject(" ADODB.Recordset")
Rs.Open " table", conn, 3, 4, 2

do until
rs.addnew
rs("fld") = ...
rs("fld2") =...
etc edt
Loop

rs.updatebatch

Second your code could be optimized:

With GlobalCommand

    .ActiveConnection = clsdb.Db <= is this a STRING or a connection object?
if it is a string it's ok. If it's an object than you need the SET
statement;
    .CommandType = adCmdStoredProc
    .CommandText = "sp_add_Location"
    .Prepared = True
End With

Do your job

Do Until...
    With globalcommand






        .Execute ,, adExecuteNoRecords
    End With
Loop

You know why the speed in the first sample is 4 x faster than the command?
Because at each EXECUTE the command has to wait for the SQL server to
execute. THe execute and the Network traffic involved gives much bytes and
threads overhead.

That's why a real batch job runs on your local PC and it creates within a
string buffer all INSERT INTO statements.
You could buffer it yourself with a StringBuffer class (that I wrote myself
in Visual Basic). It gave me the same performance as with bulkcopy...

Nonsense,

I did some tests with ODBC and Oracle Objects (native COM to Oracle) and
several OleDB drivers.

The speed of ADO is just some 10% slower than for example native COM objects
to Oracle (without the overhead of extra translation drivers in ODBC).

When you program well it is FASt. But you must know the rules of when
performance drops and when not.

Richard

respect from Rome

MATRO
http://members.tripod.com/Matro