Re: Java stored procedure performance dilemma

Colin McGuire wrote:

> I am involved with small team of programmers that have just finished
> writing a suite of Java routines that perform some grunt-job string
> manipulation/processing :- the algorithms implemented as Java stored
> procedures.

Meaning as stored procs inside Oracle, correct?

> Unfortunately, at this late stage in the play, performance
> has become an issue and we realise we have probably made an incorrect
> technical decision early on.

What is the actual performance issue? Are you certain that you have identified and diagnosed this problem correctly?

> During a recent technical meeting it was
> mentioned one solution might be to rewrite (ie translate) our library
> routines from Java to Pro/C++, the latter I believe shipped with all
> flavours of Oracle and thus would run on every machine Oracle ran on,
> compile to fast native code etc. We have decided on having a think
> about this approach, weigh up the pro's and con's, and discuss again
> from an informed perspective at the next meeting.

I am uncertain as to why your performance problems will be solved by using Pro*C/C++ instead of Java.

Client-server in an Oracle context, means to me that as much processing as possible needs to be done in the database. As an extreme example :

• opening a cursor and pulling a row down at a time, validating and processing that row in the client software, and then updating the database vs.
• using a SQL SELECTs that does the same, but runs in the database

The first method is a throwback of the old days when we dealt with tapes, VSAM files, ISAM files and the like. The correct approach when dealing with a database, is to have the database engine doing as much of the work as possible and not have client software doing row-by-row cursor processing (ideally speaking of course).

This is why I am a bit unclear as to why you think that Pro*C will solve the problem. I would assume that a lot of the processing is done in Oracle by Oracle. I would assume that the actual speed of the client as a function of the language it is written in, is negligible in the overall performance sum.

In other words, if an Oracle client generates more CPU time than what its counterpart/shadow process in Oracle does, I would think there is something wrong.

What do we deal with? Data. So it makes sense to me to perform that data processing as close to the data source as possible.

Granted, there are always the exception. :-)

So, I do not see how Java performance can be an issue in this context. I also fail to see how Pro*C can provide a better solution. IMO, Pro*C is a bit of a bastard (speaking from personal experience). It does not contribute nearly as much to the ease and flexibility of development as PL/SQL for example. It is not as portable as you would believe. Yeah, the Pro*C part of your C code will compile on another platform, but that is not to say that you will face a few nut-kicking issues with the rest of the C code.

If at the end of the day, it does come down to the simple fact that the actual speed of the JAVA stored proc is not fast enough, then think of replacing it with an external stored procedure (DLL/shared object) written in C.

Personally I think Pro*C is a big mistake when it comes Oracle development. My take is doing server-side Oracle development in: - single butt kicking SQL statements (SQL is very powerful   and very much underrated)

- PL/SQL if too complex for SQL
- JAVA if PL/SQL does not suffice
- external C procedures if JAVA does not suffice

--
Billy