db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: stored procedures and embedded derby
Date Mon, 14 Nov 2005 23:44:01 GMT
Michael McCutcheon wrote:

> Hello All,
> 
> I'm going to be using Derby (in embedded mode) in Tomcat for my web
> app.  I am building everything from scratch.
> 
> I'm trying to decide on if I want to put 'everything' in stored
> procedures or just code stuff in the application. (i.e. no SQL in the
> application code whatsoever, all logic in procedures, etc.)
> 
> It seems like stored procedures are just more developer work for an
> embedded database (more stuff to setup), and may actually be slower at
> runtime because (I assume) the static methods are called reflectively
> (correct me if I am wrong).

The methods will not be called reflectively, Derby will compile the SQL
CALL into Java byte code and the code for the generated class will call
your method directly. Even if reflection was involved, most likely it
would be a very very small cost compared to the execution of the actual
method.

There is the potential for slightly reduced performance compared to a
client side application. This is because the static Java method is
stateless and therefore cannot keep references to PreparedStatement
objects in the way a client application can. That is a client
application can keep a single connection open to the database, and keep
a set of PreparedStatements to use. A Java procedure must do something
like the following:

public static void MyProcedure(int id, ...)
{
 Connection conn = DriverManager.getConnection("jdbc:default:connection");

PreparedStatement ps = conn.prepareStatement("SELECT ...");

...

conn.close();
}


Now Derby has a database wide statement plan cache, so the prepare in
the static method most likely will not be a compilation, just object
creation hooking up to an existing plan.

And the conneciton creation is much faster than a client connection
creation, there's no socket involved, it's just object creation, hooking
up the existing connection.

> However, if I did do everything in stored procedures, that would make it
> easy if I were to move out of embedded and put the database on a
> seperate machine.
> 
> But for now the plan is to be completely embedded.
> 
> Can anyone share thoughts on this?  What do you think are the
> benefits/drawbacks of stored procedures for an embedded database?

Benefits are encapsulation, exact same procedures will work on other
databases supporting part 13 of the SQL standard.

Dan.


Mime
View raw message