db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Some design related questions about using derby embedded mode.
Date Mon, 22 Jan 2007 18:07:00 GMT
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.




Mime
View raw message