Paradox 9 and Oracle 8i

We are currently in the process of migrating a pdox5 application to use an Oracle database due to the number of reecords and users involved. We experience muitliple corruptions and lck files access violation. We were trying to do this without rewriting the entire application, but have found out that some of the methods in paradox does not work well with sql database.

Here is one problem - we have a batch process(2000 records) that when it runs againts paradox tables it runs for 16mins. The same file was used against Oracle tables and the process ran for 1hr 20mins. The pdox5 application used tcursors and ui_objects to manipulate data in the tables. methods used are insertRecord(), empty(),locate(), locateNextPattern(),switchIndex(). Empty was replaced with sql syntax, locate* was replaced with qLocate etc.

The database does not refresh after we deleteRecord(), to refreshh the database we use forceRefresh() which takes several seconds to update the UI.

Our choices are, is it valid to change this to Oracle backend with these slow response time or do we upgrade all users to pdox9. They are currently using pdox5 on WINNT SP6. There problem as mentioned above is lock file problems and corrupt tables. This system has been running approx 7 years and we would rather change the backend than rewrite the enttire application.

Is there something that we can do to make the Oracle conversion be more user friendly?

How many records are in your largest table? How much storage (particularly the size of the .DB file) does it occupy?

What is the maximum number of simultaneous users of the application?

How many coders have had a hand in developing and maintaining this application?

Did you consider other back-end database platforms besides Oracle?

The problem you are facing is complex. Whether you should rewrite, reconnect, or migrate depends on many factors, and the questions above are only a beginning.

However, if you have the patience to keep supplying information, this conference will keep trying to help.

-Al.

A 600,000 record table and 50 simultaneous users is what I would call a large application for Paradox, or any platform that relies on file redirection for DB sharing. No doubt you would stress any network with that load.

In fact, I should say: congratulations on keeping it running for 7 years!

With only 3 developers having worked on it, the application is probably still clean enough to not require a rewrite just to make it more efficient.

My only objection to Oracle would have been the expense (v. InterBase eg), but since your company is buying it anyway, good. You are not worse off with Oracle once it is paid for.

I have used Paradox as a front end to an Oracle database on two projects and encountered no special problems. However, I was able to create or convert all queries in SQL and command the BDE to pass them through. This allows the table operations to be performed by Oracle, with only the answer set transmitted back to the client.

I believe the performance of your setup is sluggish because Paradox is still reading the entire table over the network for each query and scan, and at least the entire index for each insertRecord and locate.

So, given that the application is basically stable and clean, and Oracle remains in your future, I suggest rewriting only the bottlenecks. Try to identify the slowest process, and redesign it so that most of the work is done on the server. If this is not possible, then redesign it to copy the relevant data (probably the entire table) to the client once, process it there, and copy it back to the server.

Is the application quite modular? Can you identify the bottlenecks? Can you, or do you have access to someone who can, write stored procedures to replace some of the ObjectPAL processes?

Do you have lots of QBE's scattered through the application? The BDE cannot always translate a QBE into SQL, and may copy the relevant tables to the client in order to perform the QBE locally. Rewrite your QBE queries into SQL queries where possible, placing special emphasis, of course, on the perfromance bottlenecks.

The problems that you will have to solve to make the performance of this application acceptable are problems that you would have to solve anyway if you were to rewrite it in Delphi. In either case, most of your effort is going to go into getting Oracle to do more work, and the client, less.

-Al.

1) Our largest table contains 600,000 records which contains history for each year. Because rebuilding process would take several hours we broke each history table out by year. So we have a history table for each year which contains 600,000 plus records. We have a dedicated server for this program, approx 8.3 gigs. Each table has a primary index on 4 fields and two secondary indexes.

2)50 simultaneous users
3)Only 3 developers total has maintained and developed this appliation
4)No we didn't consider another database the company is moving to Oracle so thats what we have to use. The company is currently migrating all applications to oracle so we will have better support for the in house apps.

A work for a very larg company, out of which only 1 other person knows paradox. Because of all the problems the users have been experiencing we wanted to move the data to a more stable database without re-writing the paradox version. If we do descide to rewrite then we will move away from paradox to Java or delphi or power builder which will take 3 months to get up and running. So we thought moving the backend would run us a coupple weeks. The performance is slugglish.

The program is very modular and yes i can identify the bottlenecks. The program was written entirely using tcursors and ui_objects. No QBE was used.
I figured as much that most of our batch process will have to moved to the server. I wrote a few procedures but I know If i tell the boss we have to rewrite one of the main batch process she will opt for a new system completly.
I read on Corel's website that even if you attach to a live table in Pradox, Paradox still uses a view and then all the methods used have to be translated to SQL then back to Paradox language. I defintely think that the procedures will do the trick