Re: inserting many records with ADO is very slow

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

I don't have the code in front of me (its at work), but I used the example in the documentation with creating an array of bind variables and then doing the insert. (they have an example in the docs) If you installed OO4O then you should have actual examples on your PC. To further debug this you are probably going to have to trace what it is doing. If you use sqlLoader it is very efficient and it uses the published API. I suspect ADO does some "helpful" things and that is why your performance is low. Can't you use bind variables in ADO? You might be doing a ton of parsing and that is going to kill you. Doesn't ADO allow a multiple insert option? It isn't clear in your code where you put the commit (begin and end trans) You should put it outside the for loop.(inside would be like auto commit.) You need to turn on sq tracing and see what the driver is actually doing. Jim Received on Fri Sep 17 2004 - 10:10:40 CDT