Re: inserting many records with ADO is very slow

"Stefan Felkel" <stefan.felkel_at_cim-team.de> wrote in message news:d705870.0409150555.28845dba_at_posting.google.com...
> 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

My guess is that you are committing after each record. Ouch. Also you are not using an array interface which anyone would use to get good performance.
Jim Received on Thu Sep 16 2004 - 00:40:43 CDT