Hi, This will be most likely monetdb writing out temporary data for sorts etc. Check the amount of disk space on the filesystem where your database is during query execution. Used space should go up.
Radovan
On 07/29/2014 01:54 PM, amihay gonen wrote:
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 aslinked_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 http://batcalc.int(X_18);
|| X_20:bat[:oid,:wrd] := batcalc.hash(X_19);
|| X_23 := calc.int http://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 http://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")
:+------------------------------------------------------------------------------------------------------------------------------------------------------------+
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list