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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Steen Jansdal wrote:
> Hi,
>
> I've noticed that simple insert statements aren't the fastest in the
world.
>
> 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
ParameterNode)

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
like
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
place for SELECT/INSERT/UPDATE/DELETE/CALL statements.


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

iD8DBQFBP54iIv0S4qsbfuQRAnMWAKCldgf7KNqHpXsix8uY2AeKRSFkdwCZARJT
GqilGcFw0vzVnomaxBEi9X4=
=vqeM
-----END PGP SIGNATURE-----


Mime
View raw message