db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: unable to execute procedure
Date Tue, 04 Apr 2006 17:16:44 GMT
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.

Mime
View raw message