|
If you were logged in you would be able to see more operations.
|
|
|
|
File Attachments:
|
|
|
Environment:
|
generic
|
|
| Issue & fix info: |
Release Note Needed
|
| Resolution Date: |
19/Sep/06 03:34 PM
|
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
}
|
|
Description
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
} |
Show » |
Satheesh Bandaram made changes - 23/Dec/05 02:10 PM
| Field |
Original Value |
New Value |
|
Description
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union all select i,j from T2;
create view V2 as select a,b from T3 union all select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
}
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
}
|
Satheesh Bandaram made changes - 24/Dec/05 12:57 AM
|
Description
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
}
|
Derby's handling of union subqueries in from list can be improved by materializing invariant resultsets once, rather than creating them many times.
For example:
create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
For a query like select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5), it is possible the resultset for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very costly if the underlying selects can take long time and also may perform union many times.
Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
public boolean performMaterialization(JBitSet outerTables)
throws StandardException
{
// RESOLVE - just say no to materialization right now - should be a cost based decision
return false;
/* Actual materialization, if appropriate, will be placed by our parent PRN.
* This is because PRN might have a join condition to apply. (Materialization
* can only occur before that.
*/
//return true;
}
|
Satheesh Bandaram made changes - 05/Jan/06 05:18 AM
|
Comment
|
[ Thanks Jeff for your analysis. I was just getting ready to file another improvement request to make this optimization more generic. (not specific to unions) I have seen huge improvements in two different customer situations. For the situation I filed the defect, each of the views (V1 and V2) had 36 tables each and by materializing the inner view into a temp. table, I noticed speed up from 70-150 seconds to under 3 seconds. (including the cost of creating temp. table)
I also saw another situation later without unions where materializing some table subqueries improved performance by couple of orders of magnitude. So you are right... this optimization can be applied to other cases too.
I think materialization with or without hash joins should be useful. In both situations, creating temp. table that materialized derived tables improved so much.
]
|
|
made changes - 01/Jul/06 08:47 PM
made changes - 01/Jul/06 08:58 PM
|
Derby Info
|
|
[Patch Available]
|
made changes - 12/Jul/06 10:37 PM
|
Attachment
|
|
d781_v2.patch
[ 12336745
]
|
made changes - 18/Jul/06 08:21 PM
|
Derby Info
|
[Patch Available]
|
[Patch Available, Release Note Needed]
|
made changes - 19/Jul/06 04:14 PM
|
Derby Info
|
[Patch Available, Release Note Needed]
|
[Patch Available, Existing Application Impact, Release Note Needed]
|
made changes - 21/Jul/06 04:56 PM
|
Resolution
|
|
Fixed
[ 1
]
|
|
Derby Info
|
[Existing Application Impact, Patch Available, Release Note Needed]
|
[Existing Application Impact, Release Note Needed]
|
|
Status
|
Open
[ 1
]
|
Resolved
[ 5
]
|
made changes - 13/Sep/06 06:59 PM
|
Summary
|
Materialize union subqueries in select list where possible to avoid creating invariant resultsets many times.
|
Materialize subqueries in select list where possible to avoid creating invariant resultsets many times.
|
|
Derby Info
|
[Release Note Needed, Existing Application Impact]
|
[Existing Application Impact, Release Note Needed]
|
made changes - 13/Sep/06 07:01 PM
|
Status
|
Resolved
[ 5
]
|
Closed
[ 6
]
|
made changes - 19/Sep/06 03:33 PM
|
Resolution
|
Fixed
[ 1
]
|
|
|
Status
|
Closed
[ 6
]
|
Reopened
[ 4
]
|
made changes - 19/Sep/06 03:34 PM
|
Status
|
Reopened
[ 4
]
|
Resolved
[ 5
]
|
|
Derby Info
|
[Release Note Needed, Existing Application Impact]
|
[Existing Application Impact, Release Note Needed]
|
|
Resolution
|
|
Fixed
[ 1
]
|
|
Fix Version/s
|
|
10.2.1.0
[ 11187
]
|
made changes - 19/Sep/06 03:34 PM
|
Status
|
Resolved
[ 5
]
|
Closed
[ 6
]
|
made changes - 30/Jun/09 04:12 PM
|
Issue & fix info
|
[Release Note Needed, Existing Application Impact]
|
[Release Note Needed]
|
|
Improvement
Closed
Major
Materialize subqueries in select list where possible to avoid creating invariant resultsets many times.
