Connection pooling

I am comparing Oracle 8i and SQL Server 7.0 and am getting poor performance with Oracle. I have a VB component calling stored procedures in SQL Server and Oracle via ADO and ODBC drivers. Oracle is 20 times slower i.e. 0.6 secs per transaction as opposed to 0.03 secs/transaction with SQL Server.

However, if I run the Oracle stored procedure from SQL Plus then I get 0.002 secs/transaction. So it looks like the Oracle stored procedure and database are OK.

I am beginning to suspect that my problem concerns connection pooling. I am using the Oracle ODBC driver and I wonder if I am not getting any connection pooling at all. If I have a continuously open conection in VB I get 0.019 secs/transaction. If I open and close the connection each transaction then I get my 0.6 secs/transaction figure mentioned earlier.

With SQL Server I'm always closing and re-opening the connection but I bet that connection pooling is working and that's why I'm seeing superior performance.

So how do I activate connection pooling to my Oracle database? How can I tell that it's working?

Thanks in advance. Received on Fri Jun 02 2000 - 00:00:00 CDT