ADO Connection Speed Connection 
Author Message
 ADO Connection Speed Connection

Hello Everyone,

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.



Thu, 27 Sep 2001 03:00:00 GMT  
 ADO Connection Speed Connection
Welcome to ADO --  It's S L O W!

Richard



Sat, 29 Sep 2001 03:00:00 GMT  
 ADO Connection Speed Connection
You may want to play around with your cursorlocation, cursor type, etc... to
try to optimize this.  I've seen some drastic changes in time to execute a
query with different options (sorry, been away from the data access coding
for a bit and can't remember all the details - but start with cursor
location, it makes a big difference).
Quote:

>Hello Everyone,

>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.



Sat, 29 Sep 2001 03:00:00 GMT  
 ADO Connection Speed Connection
Hi,

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...

Quote:

>Hello Everyone,

>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.



Tue, 02 Oct 2001 03:00:00 GMT  
 ADO Connection Speed Connection

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.

Quote:

>Welcome to ADO --  It's S L O W!

>Richard



Tue, 02 Oct 2001 03:00:00 GMT  
 ADO Connection Speed Connection
The speed may be fine for network based databases such as Oracle and SQL
Server, but when using an MDB file for the database DAO is 7-17 times faster
than ADO.  Those are measured numbers.

Richard



Sat, 06 Oct 2001 03:00:00 GMT  
 ADO Connection Speed Connection
On Fri, 16 Apr 1999 16:15:38 +0200, "Egbert Nierop"

Quote:

>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...

hi Egbert, can you explain more detaild about your StringBuffer class?
your post is a very interesting one.

respect from Rome

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



Sun, 07 Oct 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Getting the connection speed of a RAS connection

2. Connection Problem - ado failover connection

3. ADO Connection State Does Not Reflect Actual Connection State

4. Connection Errors via ADO Connection Object

5. ADO Connection - How to set ANSI_NULLS OFF through the connection

6. Release ADO connection from a connection pool

7. ADO connection passed to a DLL fails to maintain valid connection

8. *******THIS SITE WILL SPEED UP YOUR INTERNET CONNECTION BY 100%*******

9. test speed connection in vb.net

10. how to measure speed connection

11. Determine internet connection speed

12. Internet Connection Speed

 

 
Powered by phpBB® Forum Software