Hi,
I've noticed a slowdown in the Jul2012 which appears to be due to disk I/O.
Printed below is the output of TRACE on the Jul2012 (11.11.5) release
and the Apr2012-SP2 release (11.9.7) of the following query:
#+BEGIN_EXAMPLE
WITH
span1 AS (
SELECT node.id
FROM node
),
solutions AS (
SELECT DISTINCT span1.id AS id1
FROM span1
)
SELECT count(*)
FROM solutions
#+END_EXAMPLE
Jul2012:
#+BEGIN_EXAMPLE
+---------+-----------------------------------------------------------------------------------+
| ticks | stmt |
+---------+-----------------------------------------------------------------------------------+
| 3 | X_1 := sql.mvc(); |
| 11 | X_2:bat[:oid,:int] =<tmp_1230>[1262014] := sql.bind(X_1=0,"annis","node","id",0); |
| 5 | X_7:bat[:oid,:oid] =<tmp_1366>[0] := sql.bind_dbat(X_1=0,"annis","node",1); |
| 6 | X_9=<tmpr_1366>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); |
| 13 | X_10=<tmp_2126>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); |
| 3543662 | (ext22=<tmp_2127>[1262014],grp20) := group.done(X_10=<tmp_2126>[1262014]); |
| 18 | X_13=<tmp_2130>[1262014] := bat.mirror(ext22=nil); |
| 82193 | X_14=<tmp_2131>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); |
| 2185 | X_15 := aggr.count(X_14=nil); |
| 15 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); |
| 8 | end s0_1; |
| 18 | function user.s0_1(); |
| 3629118 | X_5:void := user.s0_1(); |
+---------+-----------------------------------------------------------------------------------+
#+END_EXAMPLE
This query is accompanied by a lot of disk I/O (both reading and writing)
Apr2012-SP2:
#+BEGIN_EXAMPLE
+-------+-----------------------------------------------------------------------------------+
| ticks | stmt |
+-------+-----------------------------------------------------------------------------------+
| 4 | X_1 := sql.mvc(); |
| 12 | X_2:bat[:oid,:int] =<tmp_1471>[1262014] := sql.bind(X_1=0,"annis","node","id",0); |
| 6 | X_7:bat[:oid,:oid] =<tmp_1365>[0] := sql.bind_dbat(X_1=0,"annis","node",1); |
| 6 | X_9=<tmpr_1365>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); |
| 14 | X_10=<tmp_2074>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); |
| 12116 | (ext22=<tmp_2107>[1262014],grp20) := group.done(X_10=<tmp_2074>[1262014]); |
| 2427 | X_13=<tmp_2106>[1262014] := bat.mirror(ext22=nil); |
| 46 | X_14=<tmp_2111>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); |
| 6 | X_15 := aggr.count(X_14=nil); |
| 11 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); |
| 6 | end s0_1; |
| 16 | function user.s0_1(); |
| 15566 | X_5:void := user.s0_1(); |
+-------+-----------------------------------------------------------------------------------+
#+END_EXAMPLE
There is no disk I/O during this query.
Notice the difference in runtime of the group.done statement in line 5. This
statement is introduced by the DISTINCT in the query.
Cheers,
Viktor
