db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Ideas for optimization
Date Thu, 09 Sep 2004 00:04:51 GMT
Hash: SHA1

Steen Jansdal wrote:
> Hi,
> I've noticed that simple insert statements aren't the fastest in the
> We have a closed source module that do a lot of simple insertions like:
> INSERT INTO test ( col1, col2, col3 ) VALUES ( 18, 34, 33 )
> INSERT INTO test ( col1, col2, col3 ) VALUES ( 44, 1435, 856 )
> ..
> ..
> INSERT INTO test ( col1, col2, col3 ) VALUES ( 117, 342, 452 )
> without using parameters. This means that the statements are different
> and the cache of compiled statements doesn't help us at all.
> Unfortunately we aren't able to change this, since its not our own
> program.
> Idea: Would it be possible for Derby to rewrite these statements
> containing constant parameters into prepared statements with parameter.
> In our example this would be an internal rewrite into
> INSERT INTO test ( col1, col2, col3 ) VALUES ( ?, ?, ? )
> and the execution would be much faster.
> What do you think?

I've thought about this before but never got around to trying to code it.

The required steps would be something like

1) During parse phase convert every ConstantNode to a ParameterNode
somehow storing the associated value (and setting the type of the

2) Generate a text form of the SQL statement from the current modified
tree, where the constants are replaced with question marks.

3) Re-compile statement, looking in cache etc.

4) Somehow get the saved parameter values and set them into the
parameter set of the newly compiled statement. Somehow hide the
parameters from the user's JDBC object (e.g. PreparedStatement)

5) Finally execute :-)

There are two approaches to step 2)
	A) Add methods to the query tree nodes to generate a canonical SQL text
form of themselves. This might add a somewhat large amount of code
solely for the purpose of re-writing statements.
	B) Keep a running modified version of the original SQL text with the
start and end positions of the original constant replaced with a ?. This
might be the easiest way. Eg. for one of the examples as the parser
discovered constants it would keep a modified SQL statement that changed
INSERT INTO test ( col1, col2, col3 ) VALUES ( ?, 34, 33 )
INSERT INTO test ( col1, col2, col3 ) VALUES ( ?, ?, 33 )
INSERT INTO test ( col1, col2, col3 ) VALUES ( ?, ?, ? )

Another item is that this constant to parameter mapping should only take

Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


View raw message