Re: inserting many records with ADO is very slow

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.

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.

> 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.
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

FYI - I should have directed you to a microsoft.public.ado.xxx newsgroup.

-- 
Michael D. Long




> 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