Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of ksunithaghm@gmail.com designates
 64.233.184.226 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
        b=KVYgo4doO6gRK2HYnOxrcn/DhT213SFdeB6pj31CC+DrA996CToZv9r3o1H80+lelCsIoPLxFcH8/968Jh4DShX/dZBTR937HFMZNJZsJJ3N+WKLGTxEjYLMCq25kN1hguK1tGTckwsXY+xDE0jiVLXXmbyqAGk/17xtOaRq6Dc=
Message-ID: <4432A9FC.1060605@gmail.com>
Date: Tue, 04 Apr 2006 10:16:44 -0700
From: Sunitha Kambhampati <ksunithaghm@gmail.com>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: unable to execute procedure
References: <3535996c0604032059j52de8f50s7073f167d816e9d2@mail.gmail.com>
 <4432A36D.6030800@apache.org> <200604041203.22126.derby@segel.com>
In-Reply-To: <200604041203.22126.derby@segel.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.