Re: Oracle Slower than Access?

I don't find this too surprising. I have also done the same thing almost. I had a VB app that inserted into both MS-ACCESS and Oracle. One difference atleast for Oracle on Unix, is that there are more levels to go thru to get to the database (ODBC stuff). It is something Like this

        Your VB Application
                 |
        ODBC Driver
                 |
        Oracle ODBC driver
                 |
            OCI Layer
                 |
          SQL*Net
                 |
          Network
                 |
     SQL*Net Listener
                 |
             Oracle

Something like 6 layers inbetween. With Access I would imagine that there are far fewer levels. That is just my thoughts on the reasoning why it takes longer. I used VB's DAO to do the transfer, it took forever with that. RDO is the way to go, and that sounds like what you are using..

Kevin

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
>
> The actual structure of the table is somewhat bigger and looks like this:
>
> TAPEFORMAT VARCHAR2(1)
> RECTYPE VARCHAR2(1)
> RECNUMBER VARCHAR2(9)
> UNITSEP1 VARCHAR2(1)
> SEARCHCODE VARCHAR2(10)
> SORTGROUP VARCHAR2(1)
> STACKERBYTE VARCHAR2(1)
> SELECTPOLYBAG VARCHAR2(1)
> MAKEUPCODE VARCHAR2(4)
> UNITSEP2 VARCHAR2(1)
> SELCODE VARCHAR2(7)
> LINE1 VARCHAR2(112)
> LINE2 VARCHAR2(112)
> LINE3 VARCHAR2(112)
> LINE4 VARCHAR2(112)
> LINE5 VARCHAR2(112)
> LINE6 VARCHAR2(112)
> LINE7 VARCHAR2(112)
> LINE8 VARCHAR2(112)
> LINE9 VARCHAR2(112)
> LINE10 VARCHAR2(112)
> LINE11 VARCHAR2(112)
>
> 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.
>
> Thank you for any help or insight you can provide.
>
> Ric Gibson
> ric.gibson_at_bpc.com
> 215.679.4451 ext. 2266
Received on Thu Oct 01 1998 - 07:35:13 CDT