Hi Martin and Stefan,
Thanks for the quick response, and sorry if this created another thread, I'm
not familiar with how to use this mailing list.
I am using:
MonetDB v5.22.3 on ubuntu 9.04 64bit
Queries and data loading use the php library.
To further explain my use case, I am planning on having monetdb be updated
about once a minute with 1000-10000 new rows of data. At the same time I
will be having as many clients as possible making queries on the database. I
have so far seen that 5 clients can be handled with satisfiable response
times. Is this a suitable application for monetdb? The query times I have
seen so far are great. My problems are handling the queries with concurrent
writes and if possible allowing for more clients to make simultaneous
queries.
Re: Martin's questions:
I tried using the recycle optimizer in the default pipe and my queries
didn't slow down, which is awesome. But, my problem now is that the values
aren't actually added to the dataset until an insert transaction occurs when
I am not querying the database. Therefore, if I continuously query the
database then the inserts are never seen, but if I stop querying for a
second and another insert happens the database is updated. I am fine with
some delay in the insert time, but is there anyway I could have it executed
slowly alongside the queries, or am I too greedy :)
The pipe I created is the same as the default pipe with the recycler
optimizer added:
bennett_pipe=inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector
My schema and and an example query are:
CREATE TABLE my_test_table (
account_id bigint,
dim1 bigint,
dim2 bigint,
dim3 bigint,
dim5 varchar(50),
dim6 varchar(50),
dim7 varchar(256),
dim8 bigint,
dim9 varchar(256),
dim10 varchar(256),
unique_id bigint,
my_time timestamp,
summable int
);
QUERY:
select dim5, sum(summable), count(distinct unique_id) from my_test_table
where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time <
'2010-07-10 00:00:00' group by dim5;
Re Stefan's questions:
The results from the trace are below. I apologize if any of the data looks
incorrect, I scrubbed my table's field names to be more generic.
I let the database cool down for 1 minute and 10 minutes, but the queries
remained slow.
If I restarted the database, the queries resumed their quick response times.
FAST QUERY TIME:
sql>trace select dim5, sum(summable) as summables, count(distinct unique_id)
as unqiue_ids from my_test_table where account_id = 76 and my_time >=
'2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5;
+-----------------------+--------+--------+
| dim1 | summables | unique_ids |
+=======================+========+========+
| test1 | 251 | 18 |
| test2 | 48 | 11 |
| test3 | 1 | 1 |
| test4 | 2 | 1 |
| test4 | 106 | 2 |
+-----------------------+--------+--------+
5 tuples (125.534ms)
+-------+------------------------------------------------------------------------------------------+
| ticks | stmt
|
+=======+==========================================================================================+
| 3 | _5 := sql.mvc();
|
| 18 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_id |
: : ",2);
:
| 7 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","accou |
: : nt_id",0);
:
| 4 | _15:bat[:oid,:lng] <tmp_1102>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_id |
: : ",1);
:
| 6 | _18:bat[:oid,:oid] <tmp_1357>[0] :=
sql.bind_dbat(_5=21685368,"sys","my_test_table",1); |
| 21 | _13<tmp_1527>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat[: |
: : oid,:lng][0]);
:
| 4 | _19<tmpr_1357>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
|
| 10 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table" |
: : ,"my_time",0);
:
| 27 | _14<tmp_1532>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); |
| 11 | _23 := calc.timestamp(A1="2010-07-03 00:00:00");
|
| 12 | _17<tmp_1527>[8000000] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); |
| 4 | _24 := calc.timestamp(A2="2010-07-10 00:00:00");
|
| 7 | _20<tmp_1532>[8000000] :=
algebra.kdifference(_17=nil,_19=<tmpr_1357>[0]); |
| 4 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my_t |
: : ime",2);
:
| 29575 | _25<tmp_1531>[359554] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 00: |
: : 00:00.000,_24=2010-07-10 00:00:00.000,true,false);
:
| 12 | _31:bat[:oid,:timestamp] <tmp_1132>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my_t |
: : ime",1);
:
| 24 | _28<tmp_1527>[359554] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestamp] |
: : [0]);
:
| 23 | _32<tmp_1536>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 |
: : .000,_24=2010-07-10 00:00:00.000,true,false);
:
| 35 | _29<tmp_1501>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 |
: : .000,_24=2010-07-10 00:00:00.000,true,false);
:
| 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",0);
:
| 12 | _30<tmp_1534>[359554] := algebra.kunion(_28=nil,_29=nil);
|
| 5 | _44:bat[:oid,:str] <tmp_1103>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5 |
: : e",2);
:
| 23 | _33<tmp_1501>[359554] := algebra.kunion(_30=nil,_32=nil);
|
| 6 | _46<tmp_1536>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:bat[ |
: : :oid,:str][0]);
:
| 8 | _34<tmp_1534>[359554] := algebra.kdifference(_33=nil,_19=nil);
|
| 9 | _47<tmp_1501>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); |
| 4 | _48:bat[:oid,:str] <tmp_1104>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",1);
:
| 10 | _50<tmp_1527>[8000000] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); |
| 6 | _66:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",0);
:
| 3 | _68:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",2);
:
| 7 | _69<tmp_1501>[8000000] :=
algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:bat[ |
: : :oid,:lng][0]);
:
| 9 | _70<tmp_1541>[8000000] :=
algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); |
| 4 | _71:bat[:oid,:lng] <tmp_1102>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",1);
:
| 8 | _74<tmp_1501>[8000000] :=
algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); |
| 4 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",0);
:
| 4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",2) |
: : ;
:
| 7 | _59<tmp_1541>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:bat[ |
: : :oid,:int][0]);
:
| 8 | _60<tmp_1524>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); |
| 3 | _61:bat[:oid,:int] <tmp_1066>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",1) |
: : ;
:
| 8 | _62<tmp_1541>[8000000] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); |
| 15525 | _35<tmp_1536>[359554] := algebra.semijoin(_20=nil,_34=nil);
|
| 2446 | _36<tmp_1534>[139] := algebra.uselect(_35=nil,A0=76:lng);
|
| 10 | _37<tmp_1536>[139] := algebra.markT(_36=nil,0@0:oid);
|
| 4 | _38<tmpr_1536>[139] := bat.reverse(_37=nil);
|
| 33 | _75<tmp_1477>[139] :=
algebra.leftjoin(_38=<tmpr_1536>[139],_74=nil); |
| 47 | _51<tmp_1403>[139] :=
algebra.leftjoin(_38=<tmpr_1536>[139],_50=nil); |
| 4 | _76<tmp_1477>[139] := algebra.selectNotNil(_75=nil);
|
| 47 | (ext61<tmp_1502>[5],grp59<tmp_1476>[139]) :=
group.done(_51=<tmp_1403>[139]); |
| 19 | _63<tmp_1363>[139] := algebra.leftjoin(_38=nil,_62=nil);
|
| 8 | _54<tmp_1541>[5] := bat.mirror(ext61=<tmp_1502>[5]);
|
| 3 | _64<tmp_1363>[139] := algebra.selectNotNil(_63=nil);
|
| 47 | _55<tmp_1471>[5] := algebra.join(_54=<tmp_1541>[5],_51=nil);
|
| 43 | (_77<tmp_1344>[33],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1476>[139],_76=<tmp_1477> |
: : [139]);
:
| 6 | _79<tmp_1527>[33] := bat.mirror(_77=nil);
|
| 22 | _80<tmp_1506>[33] := algebra.join(_79=nil,_76=nil);
|
| 20 | _81:bat[:oid,:wrd] <tmp_1527>[5] :=
aggr.count(_80=nil,grp59=<tmp_1476>[139],_54=<tmp_15 |
: : 41>[5]);
:
| 21 | _65:bat[:oid,:lng] <tmp_1536>[5] :=
aggr.sum(_64=nil,grp59=nil,_54=nil); |
| 63484 | barrier _137 := language.dataflow();
|
| 9 | _82 := sql.resultSet(3,1,_55=<tmp_1471>[5]);
|
| 7 |
sql.rsColumn(_82=1,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
|
| 5 |
sql.rsColumn(_82=1,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
| 5 |
sql.rsColumn(_82=1,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
|
| 3 | _99 := io.stdout();
|
| 41 | sql.exportResult(_99=14711568,_82=1);
|
| 83716 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00");
|
+-------+------------------------------------------------------------------------------------------+
SLOW QUERY TIME:
sql>trace select dim5, sum(summable) as summables, count(distinct unique_id)
as unqiue_ids from my_test_table where account_id = 76 and my_time >=
'2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5;
+-----------------------+--------+--------+
| dim1 | summables | unique_ids |
+=======================+========+========+
| test1 | 251 | 18 |
| test2 | 48 | 11 |
| test3 | 1 | 1 |
| test4 | 2 | 1 |
| test4 | 106 | 2 |
+-----------------------+--------+--------+
5 tuples (2.7s)
+---------+----------------------------------------------------------------------------------------+
| ticks | stmt
|
+=========+========================================================================================+
| 3 | _5 := sql.mvc();
|
| 20 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","account_ |
: : id",2);
:
| 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","acc |
: : ount_id",0);
:
| 25 | _13<tmp_1705>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
: : [:oid,:lng][0]);
:
| 4 | _15:bat[:oid,:lng] <tmp_1664>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","account |
: : _id",1);
:
| 20 | _14<tmp_1706>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); |
| 4 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=21685368,"sys","my_test_table",1); |
| 8 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
|
| 31627 | _51<tmp_1670>[0] :=
algebra.leftjoin(_38=<tmpr_1665>[0],_50=nil); |
| 20 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","my_time",0);
:
| 10 | _23 := calc.timestamp(A1="2010-07-03 00:00:00");
|
| 3 | _24 := calc.timestamp(A2="2010-07-10 00:00:00");
|
| 7243 | (ext61<tmp_1710>[0],grp59<tmp_1661>[0]) :=
group.done(_51=<tmp_1670>[0]); |
| 16 | _54<tmp_1665>[0] := bat.mirror(ext61=<tmp_1710>[0]);
|
| 47358 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil);
|
| 6 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil);
|
| 13 | (_77<tmp_1710>[0],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1661>[0],_76=<tmp_1651>[ |
: : 0]);
:
| 16 | _79<tmp_1674>[0] := bat.mirror(_77=nil);
|
| 11 | _80<tmp_1710>[0] := algebra.join(_79=nil,_76=nil);
|
| 10 | _81:bat[:oid,:wrd] <tmp_1651>[0] :=
aggr.count(_80=nil,grp59=<tmp_1661>[0],_54=<tmp_16 |
: : 65>[0]);
:
| 20 | _55<tmp_1711>[0] := algebra.join(_54=<tmp_1665>[0],_51=nil);
|
| 11 | _65:bat[:oid,:lng] <tmp_1674>[0] :=
aggr.sum(_64=nil,grp59=nil,_54=nil); |
| 11 | _82 := sql.resultSet(3,1,_55=<tmp_1711>[0]);
|
| 9 |
sql.rsColumn(_82=235,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
|
| 5 |
sql.rsColumn(_82=235,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
| 4 |
sql.rsColumn(_82=235,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
|
| 2 | _99 := io.stdout();
|
| 30 | sql.exportResult(_99=24880032,_82=235);
|
| 420626 | _17<tmp_1705>[8000010] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); |
| 17 | _20<tmp_1665>[8000010] :=
algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); |
| 13 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","my |
: : _time",2);
:
| 5 | _31:bat[:oid,:timestamp] <tmp_1704>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","m |
: : y_time",1);
:
| 13 | _32<tmp_1673>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: |
: : 00.000,_24=2010-07-10 00:00:00.000,true,false);
:
| 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",0);
:
| 4 | _44:bat[:oid,:str] <tmp_1103>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",2);
:
| 7 | _46<tmp_1661>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:ba |
: : t[:oid,:str][0]);
:
| 16 | _47<tmp_1706>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); |
| 3 | _5 := sql.mvc();
|
| 4 | _48:bat[:oid,:str] <tmp_1631>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","dim5",1);
:
| 21 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","account_ |
: : id",2);
:
| 5 | _15:bat[:oid,:lng] <tmp_1637>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","account |
: : _id",1);
:
| 4 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=25090760,"sys","my_test_table",1); |
| 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
|
| 5 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","my_time",0);
:
| 8 | _23 := calc.timestamp(A1="2010-07-27 00:00:00");
|
| 3 | _24 := calc.timestamp(A2="2010-08-03 00:00:00");
|
| 31883 | _25<tmp_1712>[391659] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-27 0 |
: : 0:00:00.000,_24=2010-08-03 00:00:00.000,true,false);
:
| 11 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","my |
: : _time",2);
:
| 21 | _28<tmp_1674>[391659] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
: : p][0]);
:
| 33 | _29<tmp_1714>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: |
: : 00.000,_24=2010-08-03 00:00:00.000,true,false);
:
| 11 | _30<tmp_1713>[391659] := algebra.kunion(_28=nil,_29=nil);
|
| 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","m |
: : y_time",1);
:
| 12 | _32<tmp_1714>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: |
: : 00.000,_24=2010-08-03 00:00:00.000,true,false);
:
| 10 | _33<tmp_1674>[391659] := algebra.kunion(_30=nil,_32=nil);
|
| 7 | _39:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",0);
:
| 4 | _44:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","unqiue_id",2);
:
| 6 | _46<tmp_1713>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba |
: : t[:oid,:lng][0]);
:
| 12 | _47<tmp_1715>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); |
| 4 | _48:bat[:oid,:lng] <tmp_1657>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",1);
:
| 366305 | _50<tmp_1713>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); |
| 14 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","summable",0);
:
| 4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","summable", |
: : 2);
:
| 17 | _59<tmp_1715>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
: : t[:oid,:int][0]);
:
| 16 | _60<tmp_1716>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); |
| 4 | _61:bat[:oid,:int] <tmp_1362>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","summable" |
: : ,1);
:
| 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","acc |
: : ount_id",0);
:
| 10 | _13<tmp_1717>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
: : [:oid,:lng][0]);
:
| 298001 | _62<tmp_1715>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); |
| 21 | _14<tmp_1716>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); |
| 374628 | _17<tmp_1717>[8000010] :=
algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); |
| 20 | _20<tmp_1716>[8000010] :=
algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); |
| 15 | _34<tmp_1720>[391659] := algebra.kdifference(_33=nil,_19=nil);
|
| 65082 | _35<tmp_1674>[391659] := algebra.semijoin(_20=nil,_34=nil);
|
| 2960 | _36<tmp_1714>[0] := algebra.uselect(_35=nil,A0=239:lng);
|
| 12 | _37<tmp_1674>[0] := algebra.markT(_36=nil,0@0:oid);
|
| 5 | _38<tmpr_1674>[0] := bat.reverse(_37=nil);
|
| 58011 | _51<tmp_1651>[0] :=
algebra.leftjoin(_38=<tmpr_1674>[0],_50=nil); |
| 26 | (ext61<tmp_1710>[0],grp59<tmp_1713>[0]) :=
group.done(_51=<tmp_1651>[0]); |
| 5 | _54<tmp_1674>[0] := bat.mirror(ext61=<tmp_1710>[0]);
|
| 27086 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil);
|
| 24 | _55<tmp_1715>[0] :=
algebra.join(_54=<tmp_1674>[0],_51=<tmp_1651>[0]); |
| 3 | _66<tmp_1651>[0] := algebra.selectNotNil(_51=nil);
|
| 11 | (_67<tmp_1710>[0],grp75) :=
group.derive(ext61=nil,grp59=<tmp_1713>[0],_66=<tmp_1651>[ |
: : 0]);
:
| 10 | _69<tmp_1712>[0] := bat.mirror(_67=nil);
|
| 5 | _70<tmp_1710>[0] := algebra.join(_69=nil,_66=nil);
|
| 9 | _71:bat[:oid,:wrd] <tmp_1651>[0] :=
aggr.count(_70=nil,grp59=<tmp_1713>[0],_54=<tmp_16 |
: : 74>[0]);
:
| 3 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil);
|
| 11 | _65:bat[:oid,:lng] <tmp_1712>[0] :=
aggr.sum(_64=nil,grp59=nil,_54=nil); |
| 1226556 | barrier _119 := language.dataflow();
|
| 9 | _72 := sql.resultSet(3,1,_55=<tmp_1715>[0]);
|
| 8 |
sql.rsColumn(_72=236,"sys.my_test_table","unique_id","bigint",64,0,_55=nil);
|
| 4 |
sql.rsColumn(_72=236,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
| 3 |
sql.rsColumn(_72=236,"sys.my_test_table","unique_ids","wrd",64,0,_71=nil:bat[:oid,:wrd]);
|
| 3 | _85 := io.stdout();
|
| 37 | sql.exportResult(_85=24880032,_72=236);
|
| 2 | _5 := sql.mvc();
|
| 18 | _11:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","account_ |
: : id",2);
:
| 4 | _15:bat[:oid,:lng] <tmp_1637>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","account |
: : _id",1);
:
| 3 | _18:bat[:oid,:oid] <tmp_1070>[0] :=
sql.bind_dbat(_5=25090760,"sys","my_test_table",1); |
| 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]);
|
| 6 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","my_time",0);
:
| 8 | _23 := calc.timestamp(A1="2010-06-24 00:00:00");
|
| 3 | _24 := calc.timestamp(A2="2010-07-01 00:00:00");
|
| 18 | _6:bat[:oid,:lng] <tmp_1410>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","acc |
: : ount_id",0);
:
| 678 | _13<tmp_1713>[8000000] :=
algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat |
: : [:oid,:lng][0]);
:
| 22 | _14<tmp_1674>[8000000] :=
algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); |
| 49964 | _25<tmp_1714>[372221] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-06-24 0 |
: : 0:00:00.000,_24=2010-07-01 00:00:00.000,true,false);
:
| 14 | _27:bat[:oid,:timestamp] <tmp_1131>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","my |
: : _time",2);
:
| 21 | _28<tmp_1713>[372221] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
: : p][0]);
:
| 24 | _29<tmp_1720>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: |
: : 00.000,_24=2010-07-01 00:00:00.000,true,false);
:
| 11 | _30<tmp_1712>[372221] := algebra.kunion(_28=nil,_29=nil);
|
| 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","m |
: : y_time",1);
:
| 13 | _32<tmp_1720>[0] :=
algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: |
: : 00.000,_24=2010-07-01 00:00:00.000,true,false);
:
| 10 | _33<tmp_1713>[372221] := algebra.kunion(_30=nil,_32=nil);
|
| 5 | _39:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",0);
:
| 5 | _44:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",2);
:
| 6 | _46<tmp_1712>[8000000] :=
algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba |
: : t[:oid,:lng][0]);
:
| 8 | _47<tmp_1717>[8000000] :=
algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); |
| 3 | _48:bat[:oid,:lng] <tmp_1657>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","unique_id",1);
:
| 1381664 | _50<tmp_1661>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); |
| 17 | _66:bat[:oid,:lng] <tmp_1413>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",0);
:
| 4 | _68:bat[:oid,:lng] <tmp_1101>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",2);
:
| 19 | _69<tmp_1706>[8000000] :=
algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:ba |
: : t[:oid,:lng][0]);
:
| 17 | _70<tmp_1721>[8000000] :=
algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); |
| 4 | _71:bat[:oid,:lng] <tmp_1703>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","unique_id",1);
:
| 405606 | _50<tmp_1712>[8000010] :=
algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); |
| 15 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=25090760,"sys","my_test_table","summable",0);
:
| 4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=25090760,"sys","my_test_table","summable", |
: : 2);
:
| 20 | _59<tmp_1717>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
: : t[:oid,:int][0]);
:
| 17 | _60<tmp_1722>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); |
| 4 | _61:bat[:oid,:int] <tmp_1362>[10] :=
sql.bind(_5=25090760,"sys","my_test_table","summable" |
: : ,1);
:
| 374062 | _74<tmp_1706>[8000010] :=
algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); |
| 16 | _56:bat[:oid,:int] <tmp_1366>[8000000] :=
sql.bind(_5=21685368,"sys","my_test_table","summable",0);
:
| 4 | _58:bat[:oid,:int] <tmp_1065>[0] :=
sql.bind(_5=21685368,"sys","my_test_table","summable", |
: : 2);
:
| 18 | _59<tmp_1721>[8000000] :=
algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba |
: : t[:oid,:int][0]);
:
| 16 | _60<tmp_1723>[8000000] :=
algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); |
| 4 | _61:bat[:oid,:int] <tmp_1361>[10] :=
sql.bind(_5=21685368,"sys","my_test_table","summable" |
: : ,1);
:
| 1884678 | _25<tmp_1707>[359554] :=
algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 0 |
: : 0:00:00.000,_24=2010-07-10 00:00:00.000,true,false);
:
| 28 | _28<tmp_1724>[359554] :=
algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam |
: : p][0]);
:
| 30 | _29<tmp_1726>[0] :=
algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: |
: : 00.000,_24=2010-07-10 00:00:00.000,true,false);
:
| 11 | _30<tmp_1725>[359554] := algebra.kunion(_28=nil,_29=nil);
|
| 10 | _33<tmp_1726>[359554] := algebra.kunion(_30=nil,_32=nil);
|
| 7 | _34<tmp_1725>[359554] := algebra.kdifference(_33=nil,_19=nil);
|
| 63572 | _35<tmp_1726>[359554] := algebra.semijoin(_20=nil,_34=nil);
|
| 2812 | _36<tmp_1673>[139] := algebra.uselect(_35=nil,A0=76:lng);
|
| 10 | _37<tmp_1726>[139] := algebra.markT(_36=nil,0@0:oid);
|
| 5 | _38<tmpr_1726>[139] := bat.reverse(_37=nil);
|
| 58840 | _75<tmp_1715>[139] :=
algebra.leftjoin(_38=<tmpr_1726>[139],_74=nil); |
| 6 | _76<tmp_1715>[139] := algebra.selectNotNil(_75=nil);
|
| 21903 | _51<tmp_1711>[139] :=
algebra.leftjoin(_38=<tmpr_1726>[139],_50=nil); |
| 62 | (ext61<tmp_1344>[5],grp59<tmp_1651>[139]) :=
group.done(_51=<tmp_1711>[139]); |
| 11 | _54<tmp_1661>[5] := bat.mirror(ext61=<tmp_1344>[5]);
|
| 46 | _55<tmp_1570>[5] := algebra.join(_54=<tmp_1661>[5],_51=nil);
|
| 34 | (_77<tmp_1500>[33],grp85) :=
group.derive(ext61=nil,grp59=<tmp_1651>[139],_76=<tmp_171 |
: : 5>[139]);
:
| 6 | _79<tmp_1706>[33] := bat.mirror(_77=nil);
|
| 24 | _80<tmp_1502>[33] := algebra.join(_79=nil,_76=nil);
|
| 22 | _81:bat[:oid,:wrd] <tmp_1706>[5] :=
aggr.count(_80=nil,grp59=<tmp_1651>[139],_54=<tmp_ |
: : 1661>[5]);
:
| 554759 | _62<tmp_1717>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); |
| 541977 | _62<tmp_1721>[8000010] :=
algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); |
| 41224 | _63<tmp_1356>[139] := algebra.leftjoin(_38=nil,_62=nil);
|
| 10 | _64<tmp_1356>[139] := algebra.selectNotNil(_63=nil);
|
| 48 | _65:bat[:oid,:lng] <tmp_1721>[5] :=
aggr.sum(_64=nil,grp59=nil,_54=nil); |
| 2761640 | barrier _137 := language.dataflow();
|
| 12 | _82 := sql.resultSet(3,1,_55=<tmp_1570>[5]);
|
| 8 |
sql.rsColumn(_82=3,"sys.my_test_table","dim5","varchar",50,0,_55=nil);
|
| 5 |
sql.rsColumn(_82=3,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]);
|
| 6 |
sql.rsColumn(_82=3,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]);
|
| 3 | _99 := io.stdout();
|
| 45 | sql.exportResult(_99=14711568,_82=3);
|
| 2761973 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00");
|
+---------+----------------------------------------------------------------------------------------+
