db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Derby select query speed questions
Date Mon, 23 Apr 2007 23:26:48 GMT
Hi Adam -
If I understand you correctly then what you suggest will work fine and 
you don't need to do anything.  Once the PreparedStatement is compiled 
and cached it will remain in the cache until it either 1) ages out 
(because the memory is needed for other PreparedStatements and this one 
hasn't been used) or 2) Derby is shutdown.  This is what Øystein was 
saying when he wrote:

Derby has a statement cache so if the statement is lexically equal to a previous statement,
the result of the previous compilation will be reused, and recompilation will not be needed.

If there is a known set of  prepared statements that are executed you 
can prepare them all in a background thread immediately after Derby is 
started.  They will not need to be compiled again, just set the values 
and execute the required statement. 

If this approach is impractical then you will need to pay the price of 
parsing the statement when it is prepared BUT it will not be compiled if 
the plan is found in the statement cache.  If, after parsing, the 
statement is NOT found in the cache then compilation will happen.  The 
next time that statement is prepared only parsing will happen.

HTH

Adam Bovill wrote:
> In my application, I have a blocking queue that different processes can add database
tasks to.  We need this because we need to have the application start up instantly and not
wait a few seconds for the DB to initialize.
>
> There is one process that handles all database operations and takes items off the queue
and runs them.
>
> So this queue could have several items on it.  These items at the moment are just the
PreparedStatement, with its values set.  The issue is that I only have one instance of the
PreparedStatement now.  So if I reset the parameters on it or do anything like that while
one item is in the queue, it would effect both items in the queue.
>
> One option that I have would be to queue up the PreparedStatement and the values that
should be set.  Then when it goes to execute, it sets the parameters and executes the query.
 
>
> What I was wondering was whether I could make a copy of the already prepared statement
and just set the values on that copy.  Thereby avoiding the statement compile time.
>
> Thanks,
>
> -Adam
>
> -----Original Message-----
> From: Oystein.Grovlen@Sun.COM [mailto:Oystein.Grovlen@Sun.COM] 
> Sent: Saturday, April 14, 2007 1:11 AM
> To: Derby Discussion
> Subject: Re: Derby select query speed questions
>
> Adam Bovill wrote:
>  > Hi Olav,
>  >
>  > Thanks.  That seems to have improved things.
>  >
>  > I was wondering whether or not there was a way to create one 
> PreparedStatement from another.  I have the first one that I've created 
> and would like to clone or duplicate this, w/o needing to recompile it.
>  >
>  > So when I'm using a PreparedStatement, I can set the parameters w/o 
> incurring too much of a penalty because it's already compiled?
>
> In order to answer this it would be good to know a bit more about what
> you are trying to achieve.  Why do need several prepared statements?
> Note that a prepared statement is local to a connection.  If you want
> to execute the same statement in another connection, you will have to
> prepare it for that connection.  However, Derby has a statement cache
> so if the statement is lexically equal to a previous statement, the
> result of the previous compilation will be reused, and recompilation
> will not be needed.
>
> --
> Øystein
>
>   



Mime
View raw message