db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Olav Sandstå" <ola...@gmail.com>
Subject Re: SQLPrepare + SQLExecute made a great improvement in preformance - is it possible?
Date Thu, 15 Nov 2007 14:12:56 GMT
On 15/11/2007, yarono <yarono@il.ibm.com> wrote:
> I wrote a small test comparing between inserting/selecting/updating 10000
> statements. One time it was with ExecDirect (the statement was rewritten in
> each iteration), and the second time with SQLPrepare, Binding parameters and
> SQLExecute.
> The test showed a great improvement in performance when using SQL prepare,
> espcially in SELECT.
> Is it possible? Does it make sense to get such a great improvement?

Yes, this is possible. Preparing (compiling) a SQL statement in Derby
is costly. In addition to compiling and optimizing the statement,
Derby produces Java byte code for executing the query. This byte code
has to be loaded. By using a prepared statement Derby has only to do
this once, while executing a "normal" statement (particularly if it is
"rewritten"/changed between each execution) Derby has to do this job
for every execution.

I once did a test measuring the CPU usage of executing a simple SELECT
statement. Having to compile it for every execution took about 2.5 ms
of CPU. Using a prepared statement instead took 0.1 ms to execute it.
For more details see slide 16 & 17 in the following presentation:


It is also reasonable that you see most effect of this for SELECT
statement since these are least costly to execute and does not require
writing log to disk.


> here are the results:
> *** It took 88 seconds to insert with bind+prepare
> *** It took 121 seconds to insert with execDirect
> *** Select (bind+prepare) took 3 second
> *** Select (execDirect) took 43 second
> *** Update (bind+prepare) took 86 second
> *** Update (execDirect) took 92 second

View raw message