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.
|