db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: unable to execute procedure
Date Tue, 04 Apr 2006 20:43:30 GMT
On Tuesday 04 April 2006 12:16 pm, Sunitha Kambhampati wrote:
> Michael Segel wrote:
> >On Tuesday 04 April 2006 11:48 am, Daniel John Debrunner wrote:
> >>Anil Samuel wrote:
> >>>            PreparedStatement ps = con.prepareStatement("select
> >>>TOTAL_TAKEN from DTP.LEAVE_APPROV where EMPLOYEE_ID=" + employee + " and
> >>>LEAVE_TYPE_ID=" + leaveTypeId);
> >>
> >>Couple of problems with the code:
> >
> >[SNIP]
> >
> >>May I suggest that if you are writing samples for others to use that you
> >>demonstrate use of parameter markers in PreparedStatements. This will
> >>perfom better on Derby and all other relational database engines.
> >>
> >>Here's a reworked version
> >
> >Why use a prepared statement at all? If he already knows the input values,
> > it would be cleaner to create a statement  then excute the statement.
> >
> >Statement s = con.createStatement();
> >s.executeUpdate(string stuff);
> >
> >Less overhead.
> Derby compares the statement text to generate a query plan and if you
> use Statement with literal values, that will involve derby having to
> compile a query plan for each of the statements , which affects
> performance.
> Using prepared statements instead of statements can help avoid
> unnecessary compilation which saves time.
The question was a rhetorical one....

In the code presented, the user created a class and within a single method, he
opened a connection to the data base, then declared a prepared statement, 
executed it only once, and then closed the connection. (Ok, Daniel pointed 
out that the code needed to be fixed, so lets make the assumption that he 
took Daniel's advice...)

But looking at the code, since it will only execute once, there is really no 
need to create a prepared statement.

To Craig's point... even if Derby did cache prepared statements, you would 
still have to consider the context of the statement. How often would the 
method be instantiated and how often would it be called?  I don't think it 
would be a good idea to rely upon the caching of the statement. How long does 
Derby/JavaDB/Cloudscape cache the statement?  You also have to consider that 
when writing an app in Java, you have the ability to write a cross platform 
application with minimal modification. (Different JDBC driver and connection 
properties...) So you need to consider the performance differences between a 
prepared statement and a regular statement.  This is why you would use a 
Statement over a PreparedStatement.

Now, had the user created a class where one method created the database 
connection, another prepared and executed the statement, and a third method 
(maybe the main method) then you should consider a prepared statement. (One 
method to prepare the statement, another to load the values and then execute 
the query and parse the results...) Even if your code executes it only once, 
there is a clear indication that the intention is to allow for multiple 
executions of the prepared statement.

Does that make sense? 


View raw message