Re: Oracle interface (SQL, OCI, ...)

Thanks for the great response! I agree that my problem lies with the application instead of the database. However, in order to leverage the work that has been already done there I would rather not change the application code. I've used cursors in several places and they work just fine but there are many more places where cursors don't mesh with the application code. For example, in one such place the app does the following :

begin transaction
get first record
while (there is a current record) {
  update it
  commit work
  begin transaction
  get another record
}
rollback.

Here, a cursor won't fit because the work needs to be committed with each update. As a result, the repeated SELECTS quickly bring the processor to its knees.

I know I might be looking at just rewriting the front end but I am still optimistic (based on a vague memory of such an interface) that there is another less conventional way to get at an Oracle database.

Thanks again,
Curt

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:8bfbma$9v$1_at_ctb-nnrp1.saix.net...
> Curt Ingram wrote in message
> <93BC4.191$eL2.45527_at_newsread2.prod.itd.earthlink.net>...
> >We would like to port an application which previously interfaced with a
> >Faircom C-Tree database to an Oracle backend. The conversion is complete
> >and everything works well but performance is extremely poor. The reason
 is
> >because reads and writes on individual records in C-Tree are very
 efficient
> >whereas with Oracle they are not. The application repeatedly reads a
 single
> >record, updates it and then reads another, etc. I would rather not
 rewrite
> >the application code so my question is, is there an interface to Oracle
> >which would operate on individual records more quickly than SQL.
>
> SQL can operate as quickly on individual records as you want it to. The
> problem is with the application design. ORACLE IS EFFICIENT WITH SINGLE
 ROW
> UPDATES.
>
> Consider the following method where you want to update individual.
> 1. Run a SQL statement to return a list of customers, e.g. SELECT * FROM
> customers
> 2. Now you want to update a single record in the cursor. To do this you
 use
> the FIND method of the application language, e.g. DATASET.FIND.FIELD('ID')
 =
> 'CUST12093'
> 3. Now you update that record, e.g. DATASET.FIELD('Surname') = 'SMITH',
> followed with a DATASET.UPDATE
>
> The only SQL involved here was the first one, where the application in
> effect pulled ALL the customer data from the database, on a large server,
> across the network, onto a small PC. And now this small PC and the
> application programming is doing all the processing.
>
> This is the method that we used in the old days when writing ISAM COBOL
> applications on mainframes and dBASE/Clipper applications for Novell File
> Servers. We OPEN A FILE, then use a SEARCH/FIND function to find a row,
 then
> we UPDATE it.
>
> This method does not work for relational databases. And will not work
> irrespective of whether you are using SQL or the lower level database
> interface (OCI for Oracle, db-lib for Sybase, etc.). The problem with this
> method is that the FIND/SEARCH functions scans the data set for the record
> to update. This is extremely inefficient - after all, that is why you have
 a
> database engine. To do the finding and scanning for you. Let the database
 do
> the job it is good at.
>
> How do you update a single row using SQL?:
> 1. BEGIN TRANSACTION
> 2. UPDATE customers SET surname = 'SMITH'
> WHERE id = 'CUST12093'
> 3. COMMIT
>
> SQL itself is very powerful. With a single UPDATE state you can update a
> single row, or multiple rows. You can base the UPDATE on search criteria
> from another table. You can even update the columns (fields) of one row
> (record), using the data from another table (file), based on complex
> search/join criteria. And this with a -single- SQL statement!
>
> If you want to display the record first, then add statement number zero to
> the above, i.e.
> 0. SELECT * FROM customer WHERE id = 'CUST12093'
>
> What is more, when using transaction based processing (like you do with
> Oracle), you can ensure database entigrity, by starting a transactions,
> doing a whole bunch of updates (e.g. billing and creating invoices) and
 then
> committing that. If anything went wrong during that process, Oracle rolls
> the changes made back. So you wind up with a consistent database.
>
> So, IMHO there is little wrong with the way Oracle handles single row
> processing. The problem is with the application.
>
> regards,
> Billy
>
>
>
>
>
>
>
Received on Sun Mar 26 2000 - 00:00:00 CST