Ideas for optimization

[Steen Jansdal <st...@jansdal.dk>]

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?

Unfortunately I haven't the knowledge yet to test this approach myself, 
but maybe some of you hardcore derby developers can step in.

Steen

Re: Ideas for optimization

[Daniel John Debrunner <dj...@debrunners.com>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jan Hlavaty wrote:
> Daniel John Debrunner wrote:
>
>> 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 :-)
>
>
> You're taking the suggestion too literally ;-)
>
> It would be easier and cleaner to build support for this optimization
> directly into the query compiler.
> So that any statement with constants in it would compile into a compiled
> execution plan with constant parameters... the same plan, no matter the
> values of parameters.
> Make it automatically treat any constants in the SQL as "internal"
> parameters. Don't let this stuff overflow outside into JDBC and other
> unrelated stuff.
>
> Jan

That's exactly what I was describing. :-)
The compiled plan (statement) cache lookup is based upon the text of the
SQL statement, that's why the internal code would need the re-write.

Dan.

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

iD8DBQFBQF+RIv0S4qsbfuQRAsh7AJ4/M73uTaULCVRLlNuw8NjuY8CCbwCgy7Gf
cxAQMLT6zgcYp2otk2AzUnE=
=PjsA
-----END PGP SIGNATURE-----

Re: Ideas for optimization

[Jan Hlavaty <hl...@code.cz>]

Daniel John Debrunner wrote:
> 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 :-)

You're taking the suggestion too literally ;-)

It would be easier and cleaner to build support for this optimization 
directly into the query compiler.
So that any statement with constants in it would compile into a compiled
execution plan with constant parameters... the same plan, no matter the 
values of parameters.
Make it automatically treat any constants in the SQL as "internal" 
parameters. Don't let this stuff overflow outside into JDBC and other 
unrelated stuff.

Jan

Re: Ideas for optimization

[Daniel John Debrunner <dj...@debrunners.com>]

-----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-----