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