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

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. [Quoted] [Quoted] 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 [Quoted] [Quoted] the FIND method of the application language, e.g. DATASET.FIND.FIELD('ID') = 'CUST12093'
[Quoted] [Quoted] 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 [Quoted] we UPDATE it.

[Quoted] 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 [Quoted] [Quoted] database engine. To do the finding and scanning for you. Let the database do [Quoted] the job it is good at.

[Quoted] 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 [Quoted] [Quoted] 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 Fri Mar 24 2000 - 10:13:47 CET