db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dyre Tjeldvoll (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-827) Performance can be improved by re-using language ResultSets across Activation executions.
Date Fri, 30 Mar 2007 14:05:25 GMT

    [ https://issues.apache.org/jira/browse/DERBY-827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12485510
] 

Dyre Tjeldvoll commented on DERBY-827:
--------------------------------------

Dan> Do you know which statement was causing this [...]

The test that fails is lang/altertable.sql. There first occurs
when re-executing a prepared statement that adds a constraint to a
table that has been dropped and re-created:

ij> create table xxx(c1 int, c2 int);
0 rows inserted/updated/deleted
ij> prepare p1 as 'alter table xxx add check(c2 = 1)';
ij> execute p1;
0 rows inserted/updated/deleted
ij> drop table xxx;
0 rows inserted/updated/deleted
ij> create table xxx(c1 int);
0 rows inserted/updated/deleted
ij> execute p1;
ERROR 42X04: Column 'C2' is either not in any table in the FROM list or appears within a join
specification and is outside the scope of the join specification or appears in a HAVING clause
and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C2' is
not a column in the target table.
ij> alter table xxx add column c2 int;
0 rows inserted/updated/deleted
ij> execute p1;
ERROR XSAI2: The conglomerate (1,568) requested does not exist.
-- Should have been: "0 rows inserted/updated/deleted"

The second failure is similar (now dropping a constraint):
ij> -- verify that alter table works after drop/recreate of table
prepare p1 as 'alter table t0_1 drop constraint p2';
ij> execute p1;
0 rows inserted/updated/deleted
ij> drop table t0_1;
0 rows inserted/updated/deleted
ij> create table t0_1 (c1 int, c2 int not null constraint p2 primary key);
0 rows inserted/updated/deleted
ij> execute p1;
ERROR XSAI2: The conglomerate (992) requested does not exist.
-- Should have been: "0 rows inserted/updated/deleted"

So I'd say that your idea about immediate invalidation and forced
recompile looks correct.

> 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, multiprobe_notTested.patch,
noclose_finish.txt, noclose_nofinish.txt, rsfromps.v1.diff, rsfromps.v1.stat, rsfromps_prelim.diff,
rsfromps_prelim2.diff, test_inbetween.sql
>
>
> >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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message