Re: Visual Basic to Oracle: how much thruput can I expect?

Keith:

I have a few more suggestions:

1. Review your application and make sure that it is not trying to re-connect after each insert. Oracle is notoriously slow with establishing the connection, but once it gets going, it usually goes pretty fast. I've seen cases where the time to connect could be anywhere from 8 to 45 seconds, and the actual insert could be nearly instantaneous. Needless to say, if you are performing multiple connects, the app will be slow.
2. If it makes sense with your application and it is running in NT, try using ODBC Connection Pooling. This could assist in reducing the number of times in which the application has to reconnect to the database even if your code is opening and closing connections. However, you should make sure that your ODBC driver can support this feature.
3. Try a better ODBC driver. I've been using one from Intersolv. They have a 30 day free trial which you can get off their web site. I've seen applications where performance can triple by using the Intersolv driver instead of the one from Oracle.
4. Here is a neat trick. Batch your insert statements into a single statement. For example, if you have to insert 4 rows of data, your statement would look like:

    Begin

       Insert into .........;
       Insert into .........;
       Insert into .........;
       Insert into .........;

Pass this as a single SQL statement. Oracle will insert all of the rows in one transaction. I usually set up a constant for the optimum batch size, which you can later tune by trial and error. If there are not too many fields, you might try batching as many as 100 inserts into one SQL statement.

Good Luck,

Bob.Rubertone_at_hbscorp.com Received on Tue Mar 16 1999 - 03:10:10 CET