Hi,
Thanks for your help. Please change the extenstion to .txt for the
attached trace. Gender is f25. Thanks again. Dariusz.
Stefan Manegold wrote:
On Wed, Jan 28, 2009 at 12:25:57PM -0500, dariuszs wrote:
Hi,
The algebra frontend has no impact on the old query (-G). Thanks. Dariusz.
try all three of the following and look at (or send us) the results:
trace select gender,count(*) from table1 group by gender;
trace select gender,count(*) from table1 where gender in ('Female','Male') group by gender;
trace select gender,count(*) from table1 where gender <> 'Unknown' group by gender;
Stefan
Niels Nes wrote:
On Wed, Jan 28, 2009 at 11:47:20AM -0500, dariuszs wrote:
Hi,
If I rewrite this query like this
select * from (select gender,count(*) from table1 group by gender) as t1
where t1.gender in ('F','M');
It takes about 2 seconds which is wonderful however how do I apply this
to very complicated queries? Thanks. Dariusz.
please try the original (slow) query using mclient -G (requires a
server restart to take affect)
And let us know if this helps
niels
Niels Nes wrote:
On Wed, Jan 28, 2009 at 04:51:34PM +0100, Stefan Manegold wrote:
On Wed, Jan 28, 2009 at 09:54:35AM -0500, dariuszs wrote:
> Hi,
> Could you please help me with this query?
> I've got gender field in the table1 with values 'Female', 'Male', 'Unknown'
> sql1:
> select gender,count(*) from table1 group by gender
> returns results in 2 seconds
> sql2:
> select gender,count(*) from table where gender in ('Female','Male')
> group by gender
> returns results in 20 seconds
>
> Why the second query took longer when I'm selecting less values? Thanks.
>
>
>
a) Because your request grouging in the second case, but not in the first
case, i.e., more complex query => more time.
b) How many less tuples do you get in the second case, i.e., how many tuples
are there in total, and how many of them have "unkown" gender?
run
trace select gender,count(*) from table1 group by gender;
and also for the second query. Then you see were the time is spend.
Niels
Stefan
> Dariusz.
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> SourcForge Community
> SourceForge wants to tell your story.
> http://p.sf.net/sfu/sf-spreadtheword
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>
>
>
>
--
| Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
| CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ |
| 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 |
| The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
:0[0]:W
Strace select f25,count(*) from trw100 group by f25;
:2734[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 16000 usec _1 := sql.bind("sys","trw100","f1",0); ]
[ 0 usec _6 := sql.bind("sys","trw100","f1",1); ]
[ 15000 usec _8 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _1 := nil; ]
[ 15000 usec _6 := nil; ]
[ 0 usec _9 := sql.bind("sys","trw100","f1",2); ]
[ 16000 usec _11 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _8 := nil; ]
[ 15000 usec _12 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 0 usec _11 := nil; ]
[ 16000 usec _9 := nil; ]
[ 16000 usec _13 := sql.bind_dbat("sys","trw100",1); ]
[ 15000 usec _14 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 0 usec _13 := nil; ]
[ 16000 usec _15 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 15000 usec _12 := nil; ]
[ 0 usec _14 := nil; ]
[ 16000 usec _17 := algebra.markT([96240405],0@0); ]
[ 16000 usec _15 := nil; ]
[ 15000 usec _18 := bat.reverse([96240405]); ]
[ 0 usec _17 := nil; ]
[ 16000 usec _19 := sql.bind("sys","trw100","f25",0); ]
[ 16000 usec _21 := sql.bind("sys","trw100","f25",1); ]
[ 15000 usec _22 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 0 usec _19 := nil; ]
[ 16000 usec _21 := nil; ]
[ 15000 usec _23 := sql.bind("sys","trw100","f25",2); ]
[ 16000 usec _24 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 0 usec _22 := nil; ]
[ 16000 usec _25 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _24 := nil; ]
[ 0 usec _23 := nil; ]
[ 16000 usec _26 := algebra.join(<~tmp_17251>[96240405],[96240405]); ]
[ 16000 usec _18 := nil; ]
[ 15000 usec _25 := nil; ]
[ 1016000 usec (ext30,grp28) := group.new([96240405]); ]
[ 15000 usec _29 := bat.mirror([4]); ]
[ 16000 usec ext30 := nil; ]
[ 0 usec _30 := algebra.join([4],[96240405]); ]
[ 16000 usec _26 := nil; ]
[ 1109000 usec _31 := aggr.count([96240405],[96240405],[4]); ]
[ 31000 usec grp28 := nil; ]
[ 16000 usec _29 := nil; ]
[ 16000 usec _32 := sql.resultSet(2,1,[4]); ]
[ 15000 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0,[4]); ]
[ 16000 usec _30 := nil; ]
[ 15000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][4]); ]
[ 0 usec _31 := nil; ]
[ 16000 usec _40 := io.stdout(); ]
&1 0 4 2 4
% sys.trw100, sys.trw100 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 8 # length
[ "F", 29577383 ]
[ "M", 64261486 ]
[ "U", 2401517 ]
[ "", 19 ]
[ 16000 usec sql.exportResult(137943968,0,""); ]
[ 2734000 usec user.s9_1(); ]
:2781[0]:R
:17984[0]:W
Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25;
:31562[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 0 usec _3 := sql.bind("sys","trw100","f25",0); ]
[ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ]
[ 16000 usec _10 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _3 := nil; ]
[ 0 usec _8 := nil; ]
[ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ]
[ 15000 usec _13 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _10 := nil; ]
[ 0 usec _14 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _13 := nil; ]
[ 15000 usec _11 := nil; ]
[ 0 usec _15 := sql.bind_dbat("sys","trw100",1); ]
[ 16000 usec _16 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 16000 usec _15 := nil; ]
[ 15000 usec _17 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 0 usec _16 := nil; ]
[ 16000 usec _18 := bat.reverse([96240405]); ]
[ 15000 usec _17 := nil; ]
[ 16000 usec _19 := bat.new(nil,nil); ]
[ 0 usec bat.append(:bat[:oid,:str][1],"F",true); ]
[ 16000 usec bat.append(:bat[:oid,:str][2],"M",true); ]
[ 15000 usec _25 := bat.reverse(:bat[:oid,:str][2]); ]
[ 16000 usec _19 := nil; ]
[ 11172000 usec _26 := algebra.semijoin(<~tmp_17247>[96240405],<~tmp_17174>[2]); ]
[ 15000 usec _18 := nil; ]
[ 16000 usec _25 := nil; ]
[ 16000 usec _27 := bat.reverse([93838869]); ]
[ 0 usec _26 := nil; ]
[ 15000 usec _29 := algebra.markT(<~tmp_17260>[93838869],0@0); ]
[ 16000 usec _27 := nil; ]
[ 16000 usec _30 := bat.reverse([93838869]); ]
[ 0 usec _29 := nil; ]
[ 437000 usec _31 := algebra.join(<~tmp_17247>[93838869],[96240405]); ]
[ 63000 usec _30 := nil; ]
[ 15000 usec _14 := nil; ]
[ 953000 usec (ext37,grp35) := group.new([93838869]); ]
[ 16000 usec _34 := bat.mirror([2]); ]
[ 16000 usec ext37 := nil; ]
[ 0 usec _35 := algebra.join([2],[93838869]); ]
[ 46000 usec _31 := nil; ]
[ 344000 usec _36 := aggr.count([93838869],[93838869],[2]); ]
[ 47000 usec grp35 := nil; ]
[ 16000 usec _34 := nil; ]
[ 0 usec _37 := sql.resultSet(2,1,[2]); ]
[ 15000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0,[2]); ]
[ 16000 usec _35 := nil; ]
[ 16000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][2]); ]
[ 15000 usec _36 := nil; ]
[ 16000 usec _45 := io.stdout(); ]
&1 1 2 2 2
% sys.trw100, sys.trw100 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 8 # length
[ "F", 29577383 ]
[ "M", 64261486 ]
[ 15000 usec sql.exportResult(137943968,1,""); ]
[ 13578000 usec user.s1_1("F","M"); ]
:31609[0]:R
:36859[0]:W
Strace select f25,count(*) from trw100 where f25 not in ('F','M') group by f25;
:48234[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 0 usec _3 := sql.bind("sys","trw100","f25",0); ]
[ 453000 usec _9 := algebra.selectNotNil(:bat[:oid,:str][96240405]); ]
[ 16000 usec _10 := sql.bind("sys","trw100","f25",1); ]
[ 16000 usec _12 := algebra.selectNotNil(:bat[:oid,:str][0]); ]
[ 0 usec _13 := algebra.kunion([96240405],[0]); ]
[ 15000 usec _9 := nil; ]
[ 16000 usec _12 := nil; ]
[ 16000 usec _14 := sql.bind("sys","trw100","f25",2); ]
[ 0 usec _16 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _13 := nil; ]
[ 16000 usec _17 := algebra.selectNotNil(:bat[:oid,:str][0]); ]
[ 15000 usec _18 := algebra.kunion([96240405],[0]); ]
[ 0 usec _16 := nil; ]
[ 16000 usec _17 := nil; ]
[ 16000 usec _19 := sql.bind_dbat("sys","trw100",1); ]
[ 0 usec _20 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 15000 usec _19 := nil; ]
[ 16000 usec _21 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 16000 usec _18 := nil; ]
[ 0 usec _20 := nil; ]
[ 15000 usec _22 := bat.new(nil,nil); ]
[ 16000 usec bat.append(:bat[:oid,:str][1],"F",true); ]
[ 15000 usec bat.append(:bat[:oid,:str][2],"M",true); ]
[ 0 usec _27 := bat.reverse(:bat[:oid,:str][2]); ]
[ 16000 usec _22 := nil; ]
[ 7922000 usec _28 := algebra.join([96240405],<~tmp_17174>[2]); ]
[ 16000 usec _27 := nil; ]
[ 1859000 usec _29 := algebra.kdifference([96240405],[93838869]); ]
[ 0 usec _21 := nil; ]
[ 141000 usec _28 := nil; ]
[ 0 usec _31 := algebra.markT([2401536],0@0); ]
[ 15000 usec _29 := nil; ]
[ 16000 usec _32 := bat.reverse([2401536]); ]
[ 15000 usec _31 := nil; ]
[ 16000 usec _33 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _3 := nil; ]
[ 15000 usec _10 := nil; ]
[ 0 usec _34 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _33 := nil; ]
[ 16000 usec _35 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 15000 usec _34 := nil; ]
[ 16000 usec _14 := nil; ]
[ 297000 usec _36 := algebra.join(<~tmp_17170>[2401536],[96240405]); ]
[ 15000 usec _32 := nil; ]
[ 16000 usec _35 := nil; ]
[ 31000 usec (ext50,grp48) := group.new([2401536]); ]
[ 16000 usec _39 := bat.mirror([2]); ]
[ 15000 usec ext50 := nil; ]
[ 16000 usec _40 := algebra.join([2],[2401536]); ]
[ 16000 usec _36 := nil; ]
[ 31000 usec _41 := aggr.count([2401536],[2401536],[2]); ]
[ 0 usec grp48 := nil; ]
[ 16000 usec _39 := nil; ]
[ 15000 usec _42 := sql.resultSet(2,1,[2]); ]
[ 16000 usec sql.rsColumn(2,"sys.trw100","f25","varchar",1,0,[2]); ]
[ 0 usec _40 := nil; ]
[ 15000 usec sql.rsColumn(2,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][2]); ]
[ 16000 usec _41 := nil; ]
[ 16000 usec _50 := io.stdout(); ]
&1 2 2 2 2
% sys.trw100, sys.trw100 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 7 # length
[ "U", 2401517 ]
[ "", 19 ]
[ 0 usec sql.exportResult(137943968,2,""); ]
[ 11375000 usec user.s10_1("F","M"); ]
:48266[0]:R
:56656[0]:W
Strace select f25,count(*) from trw100 where f25 <> '' group by f25;
:62609[0]:R
[ 0 usec mdb.setTimer(true); ]
[ 0 usec _2 := sql.bind("sys","trw100","f25",0); ]
[ 16000 usec _7 := sql.bind("sys","trw100","f25",1); ]
[ 15000 usec _9 := algebra.kunion(:bat[:oid,:str][96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _2 := nil; ]
[ 0 usec _7 := nil; ]
[ 16000 usec _10 := sql.bind("sys","trw100","f25",2); ]
[ 15000 usec _12 := algebra.kdifference([96240405],:bat[:oid,:str][0]); ]
[ 0 usec _9 := nil; ]
[ 16000 usec _13 := algebra.kunion([96240405],:bat[:oid,:str][0]); ]
[ 16000 usec _12 := nil; ]
[ 15000 usec _10 := nil; ]
[ 0 usec _14 := sql.bind_dbat("sys","trw100",1); ]
[ 16000 usec _15 := bat.reverse(:bat[:oid,:oid][0]); ]
[ 15000 usec _14 := nil; ]
[ 16000 usec _16 := algebra.kdifference([96240405],<~tmp_17017>[0]); ]
[ 0 usec _15 := nil; ]
[ 3656000 usec _17 := algebra.antiuselect([96240405],""); ]
[ 16000 usec _16 := nil; ]
[ 0 usec _19 := algebra.markT([96240386],0@0); ]
[ 16000 usec _17 := nil; ]
[ 15000 usec _20 := bat.reverse([96240386]); ]
[ 16000 usec _19 := nil; ]
[ 453000 usec _21 := algebra.join(<~tmp_17251>[96240386],[96240405]); ]
[ 31000 usec _20 := nil; ]
[ 0 usec _13 := nil; ]
[ 969000 usec (ext26,grp24) := group.new([96240386]); ]
[ 0 usec _24 := bat.mirror([3]); ]
[ 15000 usec ext26 := nil; ]
[ 16000 usec _25 := algebra.join([3],[96240386]); ]
[ 31000 usec _21 := nil; ]
[ 359000 usec _26 := aggr.count([96240386],[96240386],[3]); ]
[ 47000 usec grp24 := nil; ]
[ 16000 usec _24 := nil; ]
[ 16000 usec _27 := sql.resultSet(2,1,[3]); ]
[ 15000 usec sql.rsColumn(3,"sys.trw100","f25","varchar",1,0,[3]); ]
[ 0 usec _25 := nil; ]
[ 16000 usec sql.rsColumn(3,"sys.trw100","count_f25","wrd",64,0,:bat[:oid,:wrd][3]); ]
[ 15000 usec _26 := nil; ]
[ 16000 usec _35 := io.stdout(); ]
&1 3 3 2 3
% sys.trw100, sys.trw100 # table_name
% f25, count_f25 # name
% varchar, wrd # type
% 1, 8 # length
[ "F", 29577383 ]
[ "M", 64261486 ]
[ "U", 2401517 ]
[ 16000 usec sql.exportResult(137943968,3,""); ]
[ 5953000 usec user.s11_1(""); ]
:62625[0]:R
:67578[0]:W
Connection closed
