Oracle Objects for OLE Performance

From: John Cieslik <john_cieslik_at_email.msn.com>
Date: Thu, 1 Oct 1998 12:16:08 +0100
Message-ID: <#IA6w7S79GA.207@upnetnews05>

I currently have a VB app which uses the OOOLE v2.1.02 to write to a parameter array and subsequent insert statement to the server, 7.3.3.

The problem is this.
The parameter array has a max size of 32K. I want to write MBs of data to the server. This has been achived by splitting the data into multiple chunks and executing the array insert. However, after about 100,000 rows, the performance starts to grind to a halt such that a practical maximum of about 200,000 rows is realised. ie 100,000 rows = 10 seconds, 200,000 rows = 15 minutes.

Why does performance degrade so quickly? The server itself is idle, and all the work is on the client.

For completeness, a precis of the code follows:

     For currentrow = 1 to numrows
        For currentfield = 1 to numFields

                OraParray(currentfield).Put_Value Val(strcurrentfield),

CurrentRow

Next currentfield
Next CurrentRow

'create a sqlstmt to insert array values into table

StrSql = "insert into " + StrTablePrefix + Add_Underscore(szTableName) + _

                "(" + StrOracleFieldListUnderScored + ") values(" +

StrOracleFieldListColons + ")"

OraDatabase.ExecuteSQl (StrSql) Received on Thu Oct 01 1998 - 06:16:08 CDT

Oracle Objects for OLE Performance - help needed