db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Saurabh Vyas <Saurabh.V...@Sun.COM>
Subject Re: [jira] Updated: (DERBY-827) Performance can be improved by re-using language ResultSets across Activation executions.
Date Thu, 29 Mar 2007 10:48:25 GMT
Dyre Tjeldvoll (JIRA) wrote:
> Here is the repro (test_inbetween.sql) requested by AB as well as a snapshot patch of
> my sandbox (includes Dan's changes and my fixups) (derby-827.snapshot.diff). Feel free
> comment on the patch, but please note that it is work in
> progress, (that means I will ignore any comments about missing
> comments and/or poor indentation).
Hi Dyre, I tried the repro it works as expected for me!!!!!!!!!!!!!!!

ij> -- Only parameter markers (no constants).
prepare p1 as 'select * from bt1 where i in (?, ?)';
ij> execute p1 using 'values (2, 4)';
I          |C    |DE
2          |two  |22.2

1 row selected
ij> execute p1 using 'values (-2, -4)';
I          |C    |DE

0 rows selected
-------------<end snip>---------------

I had tried this repro on the development trunk only (10.3) Updated to 
revision 523546. Am I missing any thing ?

> The patch traces creation of all language result sets, and this
> creates a lot of output. This means that no diff-based tests
> will pass with this patch. Most of this output can be removed by
> reverting GenericResultSetFactory.java after applying the patch.
>> Performance can be improved by re-using language ResultSets across Activation executions.
>> -----------------------------------------------------------------------------------------
>>                 Key: DERBY-827
>>                 URL: https://issues.apache.org/jira/browse/DERBY-827
>>             Project: Derby
>>          Issue Type: Improvement
>>          Components: Performance
>>            Reporter: Daniel John Debrunner
>>         Attachments: close_nofinish.txt, d827_execute_method_cleanup.txt, derby-827.extra.diff,
derby-827.snapshot.diff, derby827_draft_reuse_result_sets.txt, derby827_update920.txt, noclose_finish.txt,
noclose_nofinish.txt, rsfromps.v1.diff, rsfromps.v1.stat, rsfromps_prelim.diff, rsfromps_prelim2.diff,
>>> Shouldn't DistinctScalarAggregateRS implement a close or a finish method
>>>> (not sure what the difference is) and close the scan controller there.
>> The close() and finish() methods are actually explained in their javadoc
>> in the language org.apache.derby.iapi.sql.ResultSet class.
>> [note this is not a JDBC java.sql.ResultSet object]
>> close() -  Tells the system that there will be no more calls to
>> getNextRow() (until the next open() call)
>> finish() - Tells the system that there will be no more access to any
>> database information via this result set
>> So close means the ResultSet may be opened again for more access, while
>> finish means it will not be used again.
>> However, their use in the code always doesn't match that, and that does
>> cause confusion, at least to me.
>> Language ResultSets (not JDBC ones) can be and are opened multiple
>> times, for example when scanning a table multiple times within a join.
>> An Activation, which represents the internal state of
>> java.sql.PreparedStatement object & has the lifetime of the
>> java.sql.PreparedStatement, contains a top-level language ResultSet.
>> This top-level language ResultSet provides the execution of the SQL
>> statement, DML, DDL or a query. The top-level ResultSet may contain
>> other ResultSets and could be seen as a tree structure. For the simple
>> case of a primary key lookup query like:
>>    select name from customer where id = ?
>> The activation would contain this:
>> top result set
>> ProjectRestrictRS << IndexRowToBaseRowRS << TableScanRS
>> Now for some reason, even though the api of ResultSet say they can be
>> re-used, and in some cases they are, this result set tree is thrown away
>> after each execution. That is, the top result set has its finish()
>> method called and then the activation removes its reference to it. Then
>> on the next execution a new (identical) tree is set up.
>> There is potential for a huge performance gain if this top level result
>> set and its tree are re-used and have the same lifetime as the
>> Activation. The saving comes in two forms, not having to create many
>> objects on each execution, and not creating short-lived objects for the
>> garbage collector to handle.
>> I made a simple fix, it's a couple of lines of code, just calling close
>> & finish at the correct times, and for the above simple primary key
>> lookup query, the performance went from 17,300 to 24,000 selects per
>> second (cached data, single user). I'll post a patch shortly as an
>> indication of the direction, once I can separate it from other changes
>> in my client.
>> However, I'm running the Derby tests and there are some (maybe 25-30)
>> failures, I think because not all the language ResultSet implementations
>> are correctly written to be re-opened. Interestingly, the first failure
>> I saw was in an aggregrate test, which goes back to the issue Manish saw.
>> Even if derbyall passed I would be nervous about submitting this patch
>> for real, because I don't think there's a lot of testing using repeat
>> executions of PreparedStatements in the tests. The ij tests mainly use
>> Statement, this is a single use of an activation so this change would
>> not affect them. Thus such a patch could regress Derby by making it more
>> likely existing bugs would be exposed.
>> Given the performance gains, I think we need to start re-using
>> ResultSets from Activation, and devise a way to ensure the testing
>> covers the re-use. The main issue is there is a large number of
>> ResultSet implementations to cover.

View raw message