inserting many records with ADO is very slow

Hi,

i have serious performance problems adding many thousand records at once with ADO. Adding records to a Oracle 9.2.0.1 database is getting superproportional slower the more records i add:

 1000 records 1 s

 2000 records:    4 s
 4000 records:    8 s
 8000 records:   20 s
16000 records:   60 s
32000 records:  306 s
64000 records: 2700 s

While adding these records to the database only the client machine has a high cpu load. The database server has little load and the network traffic is low.
I already tested other Parameters for the Open Statement with the same result.

I used the following VBScript for the test:

On Error Resume Next

' CursorTypeEnum

Const adOpenForwardOnly = 0

' LockTypeEnum
Const adLockOptimistic = 3

'CursorLocationEnum

Const adUseServer = 2

'CommandTypeEnum

Const adCmdText = 1

Dim conn, rs, cs, i

cs = "Provider=OraOLEDB.Oracle;Data Source=ORADB2;User ID=SCOTT;Password=Tiger;"

Set conn = CreateObject( "ADODB.Connection" ) Set rs = CreateObject( "ADODB.Recordset" )

Wscript.Echo "connect string: " & cs
conn.Open( cs )

Wscript.Echo "connected"

conn.Execute( "DROP TABLE SCOTT.TEST" )

conn.Execute( "CREATE TABLE SCOTT.TEST (NAME VARCHAR2(750))" )

n = 1000
for i = 1 to 10

        Fill( n )        
        n = n * 2
        

next

Sub Fill( n )

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

        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

End Sub Received on Wed Sep 15 2004 - 08:55:08 CDT