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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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
