Hi all , we are trying to compare oracle vs monetdb.

the query we are try to run is :

select count( client_code_new ) as s from

(

SELECT t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident, MAX (t.system_code)

FROM transactions t left outer join

( SELECT c.linked_open_transaction_new as linked_open_transaction_new FROM calculated_taxs c GROUP BY c.linked_open_transaction_new) c_temp

on t.transaction_no = c_temp.linked_open_transaction_new

join clients_list cl on cl.client_code = t.client_code_new AND cl.branch = t.branch_new

WHERE t.opg_cls = 'O' AND (t.close_trans_disregard_balance_new) =0 and new_system_code = 1

GROUP BY t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident) a ;

tables sizes are:

transactions = 103M rows

calculated_taxs= 79M rows

The query is taking 1min to run.

we've notice that during the query run the VIRT column (from top utility) is become very high 10G , and during the we see extensive write to disk .

why does monet is do so much writing ?

thanks,

amihay

bellow is output of minimal_pipe optimizer

+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| mal |

+============================================================================================================================================================+

| function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void; |

| X_5 := sql.mvc(); |

| X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","clients_list"); |

| X_9 := sql.bind(X_5,"sys","clients_list","client_code",0); |

| (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2); |

| X_15 := sql.bind(X_5,"sys","clients_list","client_code",1); |

| X_17 := sql.delta(X_9,X_12,r1_12,X_15); |

| X_18 := algebra.leftfetchjoin(X_6,X_17); |

| X_19 := batcalc.int(X_18); |

| X_20:bat[:oid,:wrd] := batcalc.hash(X_19); |

| X_23 := calc.int(22); |

| X_25 := sql.bind(X_5,"sys","clients_list","branch",0); |

| (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2); |

| X_29 := sql.bind(X_5,"sys","clients_list","branch",1); |

| X_30 := sql.delta(X_25,X_27,r1_27,X_29); |

| X_31 := algebra.leftfetchjoin(X_6,X_30); |

| X_32:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31); |

| X_33:bat[:oid,:int] := bat.new(nil:oid,nil:int); |

| X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","transactions"); |

| X_38 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0); |

| (X_40,r1_40) := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2); |

| X_43 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1); |

| X_44 := sql.delta(X_38,X_40,r1_40,X_43); |

| X_45 := algebra.leftfetchjoin(X_36,X_44); |

| X_46 := A1; |

| X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0); |

| (X_50,r1_50) := sql.bind(X_5,"sys","transactions","new_system_code",2); |

| X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1); |

| X_55 := sql.delta(X_47,X_50,r1_50,X_53); |

| X_56 := algebra.leftfetchjoin(X_36,X_55); |

| X_57 := A2; |

| X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0); |

| (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2); |

| X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1); |

| X_63 := sql.delta(X_58,X_60,r1_60,X_62); |

| X_64 := algebra.leftfetchjoin(X_36,X_63); |

| X_65 := A0; |

| X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false); |

| X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false); |

| X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false); |

| X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0); |

| (X_74,r1_74) := sql.bind(X_5,"sys","transactions","transaction_no",2); |

| X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1); |

| X_78 := sql.delta(X_71,X_74,r1_74,X_77); |

| X_79 := algebra.leftfetchjoin(X_36,X_78); |

| X_80 := algebra.leftfetchjoin(X_70,X_79); |

| X_81:bat[:oid,:oid] := sql.tid(X_5,"sys","calculated_taxs"); |

| X_83 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0); |

| (X_86,r1_86) := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2); |

| X_89 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1); |

| X_91 := sql.delta(X_83,X_86,r1_86,X_89); |

| X_92 := algebra.leftfetchjoin(X_81,X_91); |

| (X_93,r1_93,r2_93) := group.subgroupdone(X_92); |

| X_96 := algebra.leftfetchjoin(r1_93,X_92); |

| X_97 := batcalc.lng(X_96); |

| (X_98,r1_98) := algebra.join(X_80,X_97); |

| X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0); |

| (X_102,r1_102) := sql.bind(X_5,"sys","transactions","client_code_new",2); |

| X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1); |

| X_106 := sql.delta(X_100,X_102,r1_102,X_104); |

| X_107 := algebra.leftfetchjoin(X_36,X_106); |

| X_108 := algebra.leftfetchjoin(X_70,X_107); |

| X_109 := algebra.leftfetchjoin(X_98,X_108); |

| X_110 := bat.append(X_33,X_109,true); |

| X_112 := bat.mirror(X_80); |

| X_113 := algebra.tdiff(X_112,X_98); |

| X_114 := algebra.leftfetchjoin(X_113,X_108); |

| X_115 := bat.append(X_110,X_114,true); |

| X_116:bat[:oid,:wrd] := batcalc.hash(X_115); |

| X_119 := calc.int(22); |

| X_121:bat[:oid,:int] := bat.new(nil:oid,nil:int); |

| X_124 := sql.bind(X_5,"sys","transactions","branch_new",0); |

| (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2); |

| X_131 := sql.bind(X_5,"sys","transactions","branch_new",1); |

| X_133 := sql.delta(X_124,X_128,r1_128,X_131); |

| X_134 := algebra.leftfetchjoin(X_36,X_133); |

| X_135 := algebra.leftfetchjoin(X_70,X_134); |

| X_136 := algebra.leftfetchjoin(X_98,X_135); |

| X_137 := bat.append(X_121,X_136,true); |

| X_138 := algebra.leftfetchjoin(X_113,X_135); |

| X_139 := bat.append(X_137,X_138,true); |

| X_140:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_116,X_119,X_139); |

| (X_141,r1_141) := algebra.join(X_32,X_140); |

| X_143 := algebra.leftfetchjoin(X_141,X_19); |

| X_144 := algebra.leftfetchjoin(r1_141,X_115); |

| X_145:bat[:oid,:bit] := batcalc.==(X_143,X_144); |

| X_147 := calc.bit(true); |

| X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false); |

| X_151 := algebra.leftfetchjoin(X_149,X_141); |

| X_152 := algebra.leftfetchjoin(X_151,X_31); |

| X_153 := algebra.leftfetchjoin(X_149,r1_141); |

| X_154 := algebra.leftfetchjoin(X_153,X_139); |

| X_155:bat[:oid,:bit] := batcalc.==(X_152,X_154); |

| X_157 := calc.bit(true); |

| X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false); |

| X_159 := algebra.leftfetchjoin(X_158,X_153); |

| X_160:bat[:oid,:dbl] := bat.new(nil:oid,nil:dbl); |

| X_163 := sql.bind(X_5,"sys","transactions","bank",0); |

| (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2); |

| X_171 := sql.bind(X_5,"sys","transactions","bank",1); |

| X_173 := sql.delta(X_163,X_168,r1_168,X_171); |

| X_174 := algebra.leftfetchjoin(X_36,X_173); |

| X_175 := algebra.leftfetchjoin(X_70,X_174); |

| X_176 := algebra.leftfetchjoin(X_98,X_175); |

| X_177 := bat.append(X_160,X_176,true); |

| X_178 := algebra.leftfetchjoin(X_113,X_175); |

| X_179 := bat.append(X_177,X_178,true); |

| X_180 := algebra.leftfetchjoin(X_159,X_179); |

| X_181:bat[:oid,:str] := bat.new(nil:oid,nil:str); |

| X_183 := sql.bind(X_5,"sys","transactions","account",0); |

| (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2); |

| X_187 := sql.bind(X_5,"sys","transactions","account",1); |

| X_188 := sql.delta(X_183,X_185,r1_185,X_187); |

| X_189 := algebra.leftfetchjoin(X_36,X_188); |

| X_190 := algebra.leftfetchjoin(X_70,X_189); |

| X_191 := algebra.leftfetchjoin(X_98,X_190); |

| X_192 := bat.append(X_181,X_191,true); |

| X_194 := algebra.leftfetchjoin(X_113,X_190); |

| X_195 := bat.append(X_192,X_194,true); |

| X_196 := algebra.leftfetchjoin(X_159,X_195); |

| X_197:bat[:oid,:str] := bat.new(nil:oid,nil:str); |

| X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0); |

| (X_204,r1_204) := sql.bind(X_5,"sys","transactions","instrument_ident",2); |

| X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1); |

| X_209 := sql.delta(X_199,X_204,r1_204,X_207); |

| X_210 := algebra.leftfetchjoin(X_36,X_209); |

| X_211 := algebra.leftfetchjoin(X_70,X_210); |

| X_212 := algebra.leftfetchjoin(X_98,X_211); |

| X_213 := bat.append(X_197,X_212,true); |

| X_214 := algebra.leftfetchjoin(X_113,X_211); |

| X_215 := bat.append(X_213,X_214,true); |

| X_216 := algebra.leftfetchjoin(X_159,X_215); |

| X_217 := algebra.leftfetchjoin(X_159,X_115); |

| X_218 := algebra.leftfetchjoin(X_159,X_139); |

| (X_219,r1_219,r2_219) := group.subgroup(X_218); |

| (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219); |

| (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222); |

| (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225); |

| (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228); |

| X_234 := algebra.leftfetchjoin(r1_231,X_217); |

| X_235 := algebra.selectNotNil(X_234); |

| X_236 := aggr.count(X_235); |

| sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,""); |

| end s4_3; |

| # querylog.define("explain \nselect count( client_code_new ) as s from \n(\n select t.client_code_new, t.bank, t.branch_new, t.account, t. |

: instrument_ident, max (t.system_code)\n from transactions t left outer join \n ( select c.linked_open_transaction_new as linked_open_transa :

: ction_new from calculated_taxs c group by c.linked_open_transaction_new) c_temp\n on t.transaction_no = c_temp.linked_open_transaction_new \njoin cli :

: ents_list cl on cl.client_code = t.client_code_new and cl.branch = t.branch_new\n where t.opg_cls = \\'O\\' and (t.close_trans_disregard_balance_new) = :

: 0 and new_system_code = 1 \n group by t.client_code_new, t.bank, t.branch_new, t.account, t.instrument_ident) a :

: ;","minimal_pipe") :

+------------------------------------------------------------------------------------------------------------------------------------------------------------+