Re: inserting many records with ADO is very slow

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<Q%g2d.444242$%_6.367740_at_attbi_s01>...
>
> I didn't see a begin trans , end trans statement in your script so it is in
> auto commit mode (as far as I remember) and the ADO is issuing a commit for
> every update. (bad design) I don't know if ADO supports an array interface

Sorry i forgot them in my test script, but after adding them i can't see a big difference. With the BeginTrans/CommitTrans the values are getting even worse with the server side cursor but getting better with the client side cursor.

without BeginTrans/CommitTrans:

# records       adUseServer     adUseClient
 16000           63              31
 32000          326              62
 64000                          128
 

with BeginTrans/CommitTrans:

# records       adUseServer     adUseClient
  1000            2               1
  2000            3               3
  4000            6               5
  8000           18              12
 16000           88              23
 32000          951              46
 64000                           91
128000                          182

> or not, but if it doesn't you are losing a major performance feature. Also
> continually opening and closing a connection is very expensive. You should
> keep the cursor open and just change the bind variables. I don't use ADO

I don't think it's really that expensive because of the ADO session pooling mechanism. All connections are made with the same user/password and they are not very frequent (few connections per second). Keeping all the connections open all the time would be a licensing problem i think.

> for Oracle I use Oracle's OLE Objects driver and it has a nice array
> interface and you can easily use bind variables. I was able to insert
> 32,000 records in under 2 seconds.
> Jim

The application has to support different databases and it was a design decision to use ODBC and later ADO but if the difference is really that great it would be worth the try.
Could you please show me how you add the 32,000 records in under 2 seconds?

Sub Fill( n )

        On Error Resume Next 
        
        Dim j, t0, tdiff
        
        conn.Execute( "DELETE FROM SCOTT.TEST" )
        

   conn.BeginTrans

        rs.Open "SELECT NAME FROM SCOTT.TEST WHERE 1=2", conn, _
                        adOpenForwardOnly, adLockOptimistic, adCmdText

        Wscript.Echo "adding " & n & " entries at once"
        
        t0 = Now
        
        for j = 1 to n
        
                rs.AddNew
                
                rs( "NAME" ) = j
                
                rs.Update
        
        next
        
        tdiff = DateDiff( "s", t0, Now )
        
        Wscript.Echo tdiff & " seconds. " & vbCrLf
        
        rs.Close
        

   conn.CommitTrans

End Sub Received on Fri Sep 17 2004 - 04:29:39 CDT