Re: Oracle Slower than Access?

It's not supprizing that access is faster on the "same machine".

Kevin Brings up a good point, that there are many protocol layers for data to be sifted through, to get to an Oracle Database.

However, I doubt these layers are consuming near that much CPU time.

Here are some more factors that need to be weighted in.

1. Oracle consumes a lot of RAM. If the Oracle database is running on the same machine, this could cause a lot of swapping and disk thrashing. (Doesn't sount like the case, or you would probably see even worse performance).
2. Oracle is true client/server, so you have seperat processes handeling client and server activity on the same machine. On the windows platforms, you need to help the Oracle database out, by providing it equal opportunity.

NT: Control Panel / system / Performance -> set boost to None -- Giving Foreground and background processes equal time.

95: ControlPanel / system / Performance / Set the Role of the Computer to be a Network Server. (something like that).

OTH: Access isn't client/server. It's more or less a single threaded application, so processing is not offloaded to a seperate program.

3. Rollback segments. Oracle has them, I don't believe Access does.

When you send a transaction to Oracle, instead of actually storing the data in the tablespace, it holds it into a rollback segment. Once the transaction is commited, the data is then moved from the rollback segment to the actual table.

4. There are a lot of methods for tuning Oracle databases and client interaction with Oracle databases. This is not necessiarly true with access.

Try the same test with both databases stored on a different machine. I wouldn't guarentee the results will be different, because there are too many variables, but I guess that 9 times out of 10 the answer will be the opposite of you're experiencing.

In the end, for a single user application, Access may very well be the better choice.

Kevin Kirkpatrick <kjk_at_hrb.com> wrote:

> 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
>
>
>

--
joebrown
@leading.net Received on Thu Oct 01 1998 - 12:19:44 CDT