[DERBY-827] Performance can be improved by re-using language ResultSets across Activation executions.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.3.1.4
• Bug behavior facts: Performance
• Assignee: Dyre Tjeldvoll
• Reporter: Daniel John Debrunner
• Created: 20/Jan/06 05:09
• Updated: 03/Sep/14 09:14
• Resolved: 14/Aug/07 06:45

Description

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

Issue Links

• is related to: DERBY-3037 Language ResultSet.finish() is called even when the ResultSet is going to be re-used.
• is related to: DERBY-3221 "java.sql.SQLException: The conglomerate (-5) requested does not exist." from Derby 10.3.1.4 embedded within Eclipse 3.3 and RAD 7.0
• is related to: DERBY-6724 NPE if insert statement needs recompilation after having fired a trigger
• is related to: DERBY-2594 Revoking a privilege from an SQL Object should invalidate statements dependent on that object
• is related to: DERBY-1876 Investigate overhead of JDBC layer and compiled activation code for simple embedded read-only, forward ResultSets
• is related to: DERBY-3459 Ensure that cleanup() puts the Language ResultSet objects in usable state in case an exception is raised
• relates to: DERBY-3343 Subsequent calls to PreparedStatement cause SQLIntegrityConstraintViolationException on column that is "Generated always"
• relates to: DERBY-4330 NullPointerException or assert failure when re-executing PreparedStatement after lock timeout
• relates to: DERBY-4204 Runtime statistics not collected on re-execution of statement
• relates to: DERBY-2594 Revoking a privilege from an SQL Object should invalidate statements dependent on that object

Sub-Tasks

• Language result sets should not reuse current isolation level across executions: Closed