Re: Oracle slower than MS Access?

Ric Gibson wrote:

> I'm experiencing a surprising performance discrepancy between Oracle and
> Access.
>
> I have a VB app that writes records to a database. It writes an SQL string
> such as "INSERT INTO MyTable VALUES('BLAH', 'BLAH', 'BLA'.....);
>
> No triggers, no stored procedures, nothing fancy, just a simple INSERT
> query. The data is coming from a flat file parsed into records by my VB code
>

[snip, snip....]

> Now here's where the confusion lies. when I run this app against an Oracle
> database it will insert about 2,000 records per minute on average over the
> course of 400,000 records. When I run it against a MS Access database, it
> will insert about 5,000 records per minute !!! Can this right right ?!?
> Everything else is identical, both tests were performed on the same machine
> using the same data under the same conditions.
>
> I'm hoping somebody will tell me that I'm doing something wrong as I can't
> believe that Oracle is intrinsically that much slower than Access.

Several things. Firstly, when you use VB, you probably use ODBC as well. This is one bottleneck in itself - ODBC tries to translate desktop database functionality to real, transaction based functionality, and it doesn't perform very well.

Secondly, Oracle can be tuned in many ways, and it probably isn't optimized for speed in your installation.

Thirdly, Oracle and other all purpose SQL databases are not made with speed as the primary goal. In my view performance of a database is also something about reliability, scalability and other factors as well. And the biggest strength
of them all, when we're talking about Oracle: portability. Basically, there's no

difference in administration and programming, whether you use a Personal Oracle for Win95 or an Enterprise Server for a mainframe, except for those inherent in the machine architectures or OSes.

If you want to improve your data load, use eg. SQLload, which is Oracle's standard tool for that. According to some, the performance difference between accessing Oracle via ODBC and directly through SQLNet is a factor 10 to 15! So you should actually be loading 20000 records per minute.

/jan Received on Thu Oct 01 1998 - 03:15:31 CDT