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
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl