Feature Requests item #2220597, was opened at 2008-11-04 13:07
Message generated for change (Comment added) made by mlkersten
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2220597&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL - general
Group: None
>Status: Closed
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Nobody/Anonymous (nobody)
Summary: SQL: reduce duplicate subqueries in one query
Initial Comment:
I think a significant performance gain can be achieved if the SQL parser would
'deduplicate' equal subqueries. The most trivial scenario would be a 'union
all' with 10 equal queries next to eachother. The amount of time taken for
processing this query (measured by more than 450 attributes) seems to be equal
to executing each query individually. With my naive knowledge I think a
programmer would 'assume' an atomic query. Hence all different subqueries
should result in the same result under the hood.
Now one could argue, deduplicate at layer 8 ;) True; But it would a nifty
feature if one could just get the optimization from SQL without changing
queries that were explicitly executed in this sequence to align with previous
results.
----------------------------------------------------------------------
>Comment By: Martin Kersten (mlkersten)
Date: 2009-12-04 11:37
Message:
Done and others are captured by the recycler.
>explain select * from unitest union all select * from unitest;
&1 0 32 1 32
% .explain # table_name
% mal # name
% clob # type
% 0 # length
=function user.s2_0{autoCommit=true}():void;
=barrier _52 := language.dataflow();
= _2:bat[:oid,:int] := sql.bind("sys","unitest","one",0);
= _7:bat[:oid,:int] := sql.bind("sys","unitest","one",2);
= _9 := algebra.kdifference(_2,_7);
= _2:bat[:oid,:int] := nil:BAT;
= _10 := algebra.kunion(_9,_7);
= _9 := nil:BAT;
= _7:bat[:oid,:int] := nil:BAT;
= _11:bat[:oid,:int] := sql.bind("sys","unitest","one",1);
= _13 := algebra.kunion(_10,_11);
= _10 := nil:BAT;
= _11:bat[:oid,:int] := nil:BAT;
= _14:bat[:oid,:oid] := sql.bind_dbat("sys","unitest",1);
= _15 := bat.reverse(_14);
= _14:bat[:oid,:oid] := nil:BAT;
= _16 := algebra.kdifference(_13,_15);
= _13 := nil:BAT;
= _15 := nil:BAT;
=exit _52;
= _17:bat[:oid,:int] := bat.new(nil:oid,nil:int);
= _20 := bat.append(_17,_16,true);
= _17:bat[:oid,:int] := nil:BAT;
= _21 := bat.append(_20,_16,true);
= _20 := nil:BAT;
= _16 := nil:BAT;
= _22 := sql.resultSet(1,1,_21);
= sql.rsColumn(_22,".unitest","one","int",32,0,_21);
= _21 := nil:BAT;
= _27 := io.stdout();
= sql.exportResult(_27,_22);
=end s2_0;
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2009-12-01 10:23
Message:
apparently this is not a bug in the common term expression optimiser then?
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2009-02-12 14:25
Message:
create table unitest (one integer);
insert into unitest (one) values (1);
insert into unitest (one) values (2);
insert into unitest (one) values (3);
insert into unitest (one) values (4);
insert into unitest (one) values (5);
sql>explain select * from unitest;
+--------------------------------------------------------------------------------------------------------+
| function user.s22_1():void;
|
| _1:bat[:oid,:int] := sql.bind("sys","unitest","one",0);
|
| _6 := sql.resultSet(1,1,_1);
|
| sql.rsColumn(_6,"sys.unitest","one","int",32,0,_1);
|
| _1:bat[:oid,:int] := nil;
|
| _12 := io.stdout();
|
| sql.exportResult(_12,_6);
|
| end s22_1;
|
+--------------------------------------------------------------------------------------------------------+
Timer 0.864 msec 10 rows
sql>explain select * from unitest union all select * from unitest;
+--------------------------------------------------------------------------------------------------------+
| function user.s23_1():void;
|
| _1:bat[:oid,:int] := sql.bind("sys","unitest","one",0);
|
| _6:bat[:oid,:int] := bat.new(nil:oid,nil:int);
|
| _9 := bat.append(_6,_1,true);
|
| _6:bat[:oid,:int] := nil;
|
| _11 := bat.append(_9,_1,true);
|
| _9 := nil;
|
| _1:bat[:oid,:int] := nil;
|
| _12 := sql.resultSet(1,1,_11);
|
| sql.rsColumn(_12,".unitest","one","int",32,0,_11);
|
| _11 := nil;
|
| _18 := io.stdout();
|
| sql.exportResult(_18,_12);
|
| end s23_1;
|
+--------------------------------------------------------------------------------------------------------+
Timer 1.115 msec 10 rows
sql>explain select * from unitest union all select * from unitest union
all select * from unitest;
+--------------------------------------------------------------------------------------------------------+
| function user.s24_1():void;
|
| _1:bat[:oid,:int] := sql.bind("sys","unitest","one",0);
|
| _6:bat[:oid,:int] := bat.new(nil:oid,nil:int);
|
| _9 := bat.append(_6,_1,true);
|
| _6:bat[:oid,:int] := nil;
|
| _11 := bat.append(_9,_1,true);
|
| _9 := nil;
|
| _12:bat[:oid,:int] := bat.new(nil:oid,nil:int);
|
| _13 := bat.append(_12,_11,true);
|
| _12:bat[:oid,:int] := nil;
|
| _11 := nil;
|
| _14 := bat.append(_13,_1,true);
|
| _13 := nil;
|
| _1:bat[:oid,:int] := nil;
|
| _15 := sql.resultSet(1,1,_14);
|
| sql.rsColumn(_15,".unitest","one","int",32,0,_14);
|
| _14 := nil;
|
| _21 := io.stdout();
|
| sql.exportResult(_21,_15);
|
| end s24_1;
|
+--------------------------------------------------------------------------------------------------------+
Timer 1.339 msec 10 rows
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2008-11-18 14:13
Message:
The commenterm expression optimizer is on by default.
A snippet of your code to ensure that the plans are
indeed not recognized are welcome.
----------------------------------------------------------------------
Comment By: Stefan de Konink (skinkie)
Date: 2008-11-18 11:08
Message:
@Martin; Should it be explicitly enabled? Because I highly doubt it is
working.
@Fabian; Of course I could catch it in the program that is actually
dynamically generating the mess. But the thing is I want it to be simple to
process the output.
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2008-11-18 09:55
Message:
if the subquery is really identical then the MAL optimizer
will/should catch it. And the recycler would further reduce
the work as well.
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2008-11-18 09:39
Message:
Perhaps it is easier to use the
USING SELECT xxx AS y
SELECT * FROM y UNION SELECT * FROM y;
or similar syntax. (I'm not sure if I got the syntax right.)
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2220597&group_id=56967
------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
