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.
e.g So statements like
select * from emp where i = 1;
select * from emp where i=2;
.....
will involve compilation cost for each of the statements
but if you use PreparedStatement with '?' like
select * from emp where i =?
The statement will be compiled once and subsequent executions will save
the compilation step.
For more details, check the following links in the tuning manual:
http://db.apache.org/derby/docs/10.0/manuals/tuning/perf21.html#HDRSII-PERF-18705
and http://db.apache.org/derby/docs/10.0/manuals/tuning/perf34.html#IDX438.
We have seen applications show considerable improvements when using
preparedstatements with '?' rather than Statements.
HTH,
Sunitha.
