java static sql vs oracle stored procedure

[raygg]

Here is a 10 pt bonus question for all you java gurus -

I am an oracle dba and do not know java at all. My developer created a java link that will tie another on-line non-oracle db on another server that sends up to 500,000 messages/day to an oracle 817 db. The messages can update any of 7 tables. Any two succeeding messages updating the same table can vary in that other than the primary and secondary key columns, any of the other columns may or may not be present. For example, if there is a table_a with key_1, col2, col3, col4 columns, key1 and col2 may have to be present for every insert, but col3 and col4 are optional. Within his code are java statments that compose the sql insert statements to be executed for each incoming statement. There are no oracle specific options in these update statements - they are straight and simple inserts which in the actual columns that are updated from message to message. With each incoming message any combination of 1 to 7 tables may have insert updates. We are most interested in throughput so optimizing execution speed is uppermost. I can address the oracle issues except one. We do not know whether to compose 7 statements entirely in java (one for each table) and then execute it, or do we set up a stored procedure for each of 7 tables and pass the incoming fields as data or null to the procedure? My developer thinks the composed statement is compiled once and interpreted once until the next db shutdown or restart of the link. I strongly suspect otherwise - that since each statement is not stored in oracle as a sp - it must be re-interpreted and recompiled for each execution for each new message by oracle. He refers to the java statements as "prepared statements" creating "static sql" which is compiled. We have not high volume testing yet. Help

 

[wushutwist]

A PreparedStatement should be resulting in library cache hits in Oracle because the parsed SQL should be cached. This is the whole appeal of using Prepared Statements.

Here is a bit from JavaDocs on Prepared Statements:

public interface PreparedStatement
extends Statement

An object that represents a precompiled SQL statement. 

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

I would suggest sticking with the PreparedStatement approach, less hoops to jump through when changes are needed and Stored Procedures are probably not going to get you much, if any, extra performance. Most likely there are other ways to increase your performance that would have a more substantial effect anyways.

One thing to note, a PreparedStatement is traditional tied to a Connection. If you are using some kind of Connection Pool for the Java Portion you need to ensure that the Connection Pool software supports PreparedStatements.

 

[raygg]

We are using oracle jcbc thin client.

wushutwist - you said>>>>>
<<<A PreparedStatement should be resulting in library cache hits in Oracle because the parsed SQL should be cached. This is the whole appeal of using Prepared Statements.>>>

We have tables 1 thru 7. If sequentially 1000 messages hit table 1 followed by 1000 for table 2 etc until table 7 gets 1000, and then table 1 gets 1000 again , can the prepared statement command to update table 1 be cleared from memory because of the least recently used approach used by oracle to store commands?

Is this caching of the sql something that oracle is doing - to store it in the sga - or is it a byproduct of the fact that since oracle is largely written in java - it just gets there anyway because it originates from a java module?

wushutwist - you said>>>>>
less hoops to jump through when changes are needed

What do you mean by that? Do you mean that a program change and recompile to the java is a lot easier to manager than a change to an oracle stored procedure? Since I do not know anyuthing about changing java but I do know that updating a sp is very easy, I am wondering why you say that. Perhaps what you mean is that if the format of the incoming message changes, then both the java and the sp have to be changed if sp is the choice, whereas if prepared statment is the choice then only the java needs changing?

This is very enlightening to me.

Thank you.

 

[wushutwist]

The big question is how many different Prepared Statements are being used. Oracle stores the parsed SQL of the MRU statements. One Prepared Statement is going to be only one entry in the Library Cache regardless of how many times it is executed if it is written correctly. It is like using Bind variables in Oracle. If each SQL Statement is put together piecemeal (like in most ASP pages) then each statement will result in a new entry in the Library Cache, this is obviously not the desired effect.

Raygg Wrote>>
Is this caching of the sql something that oracle is doing - to store it in the sga - or is it a byproduct of the fact that since oracle is largely written in java - it just gets there anyway because it originates from a java module?

It is a little of both, check out this link:

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Another cool feature of Prepared Statements is they can be used to send a Batch of commands to the DBMS all at once, instead of the traditional procedure of sending one at a time. Saves on overhead.

Raygg Wrote>>
wushutwist - you said>>>>>
less hoops to jump through when changes are needed

What do you mean by that? Do you mean that a program change and recompile to the java is a lot easier to manager than a change to an oracle stored procedure? Since I do not know anyuthing about changing java but I do know that updating a sp is very easy, I am wondering why you say that. Perhaps what you mean is that if the format of the incoming message changes, then both the java and the sp have to be changed if sp is the choice, whereas if prepared statment is the choice then only the java needs changing?

I meant your second example. I my cases the SQL will not change without requiring a change in the front-end software. If your situation is anything like mine than it is a pain to coordinate these modifications. I am a Java Developer, I have no Administrative rights to the Database (nor should I) if I need a change than I have to submit it to the DB Group. That is fine with me, the problem is that it usually takes the DB group a week, sometimes two, to get around to spending the 30 seconds of time it takes to run my script. Sorry, if I sound bitter but come on! Fortunately I have all the SQL code in Java and I only have to go the DB Group for Structure changes. If we were using SPs then I would go nuts!

 

[raygg]

This is really helpful stuff.

We are a development shop with no production systems - so the db group approval cycle is not an issue here. We just create it, test it, QA it and support it - we don't run a production environment. It will be our customers with their 500,000 trans/day that have to use it.

With that eliminated is the jumping thru hoops oracle procedures approach still an issue?

I also read there is something called Java Stored Proocedures. Is this something somewhere in the spectrum between stored procedures and java prepared statements?