Francois Orsini wrote:
Hi Legolas,
Hope you find some of the answers below:
1) We need highest performance, Should we use stored procedure?
Writing and running stored procedures in Java allows you obviously to
run Java logic within the same JVM as the Derby DB core engine - If your
application runs Derby embedded, you may not gain from a noticeable
improvement as everything runs embedded
In fact you may notice a slight performance degradation comparing java
procedures to an embedded application. The reason is that a java
procedure is stateless, thus it cannot keep a reference to any Statement
or PreparedStatement objects. So while a embedded client program can
prepare a statement once and re-use it many times, the code within a
Java procedure needs to be:
PreparedStatement ps = conn.prepareStatement("SELECT ...");
ResultSet rs = ps.executeQuery();
// process rs
ps.close();
Thus the additional cost of object creation for the PreparedStatements
can impact performance, typically a compile cost is not involved as
Derby has a internal cache for compiled plans.
Since the api (JDBC) is the similar for server-side Java procedures the
bulk of the logic can be written such that it works in embedded, client
or server-side. Doing these allows easy performance comparisons of your
own code in various situations, which is the best way of testing.
- However, you will bypass some
of the JDBC layer(s) as the stored procedure will run inside the
database spectrum in theory, with direct access to data, not requiring
the use of JDBC for that
JDBC is the api used to access data from within a Java procedure.
Derby's embedded JDBC code is the same as the server-side JDBC code.
- If you feel that you can run some of your
logic inside the database, then it certainly a good thing to do it but
as usual, there are tradeoffs as far as portability ( i.e. stored
procedure in Java), if you ever intend to port your stored procedure
logic to other RDBMS that does not support this.
Though Java procedures tend to be standard based as the standard
appeared before the technology in most databases. Oracle and DB2 support
Java procedures according to the standard.
Dan.
