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"); | +---------+----------------------------------------------------------------------------------------+