Re: inserting many records with ADO is very slow

"Michael D. Long" <michael.d.long-nospam_at_comcast.net> wrote in message news:<lbWdnbvR0tO52NHcRVn-sA_at_comcast.com>...
> Response inline...
>
> "Stefan Felkel" <stefan.felkel_at_cim-team.de> wrote in message
> news:d705870.0409170129.7f2f56_at_posting.google.com...
> >
> > 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.
>
> The connection pooling algorithm used by ADO is process based, which means
> that when you close the last connection associated with a process (i.e.,
> your application) the connection pool is destroyed.

That's fine as the application is a server running for a long time handling all the client requests.

>
> As to licensing, connection pooling doesn't effectively increase the number
> of users that may connect to the database server. If you read the license
> carefully you'll see that the use of middleware doesn't reduce the number of
> licenses you need for your user base, even in the case where the middleware
> is able to serve 100 concurrent application users with 7 active sessions.
>

Ok, i overlooked that, but wouldn't it waste resources on the server when holding 100 connections open all the time?

> > 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.
>
> Is anyone on your team an actual database applications programmer? Skimming
> the newbie primers for a VERY BASIC technique on how to insert rows into a
> table using ADO is going to yield less than desirable results. Don't
> complain about the poor performance when using a poor approach.
>
> Performance tips:
>
> 1. When opening your Recordset use adLockBatchOptimistic.

With SQL-Server this is not bad (128000 records in 10 seconds instead of 45 seconds), but with Oracle it doesn't pay much (128000 records in 216 seconds instead of 258 seconds).

>
> 2. The use of late binding for field assignment adds significant overhead -
> change to ordinal reference (i.e., rs(0) = j, which can shave ).
>
> 3. The rs.Update call in the loop is not needed.
>
> 4. Where possible, commit the transaction using rs.UpdateBatch in suitable
> work units (500, 1000, etc.) and begin a new transaction.
>
> for j = 1 to n
> rs.AddNew
> rs(0) = j
> if j mod 1000 = 0 then
> rs.UpdateBatch
> conn.CommitTrans
> if j < n then conn.BeginTrans
> end if
> next
>
> 5. Read the documentation and explore behaviors with multiple code samples

I did exactly that and found that prepared sql statements with parameters could help me much (128000 records in 25 seconds instead of 258 seconds with Oracle and 3 seconds instead of 45 with SQL-Server).

Thanks for your tips,

        Stefan Received on Thu Sep 23 2004 - 07:33:05 CDT