[MonetDB-users] server crash/sql prompt
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server? - is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi, I'm using Nov2008-SP2 on windows server 2003 x64 First I terminated the client, then I started another client with no luck, then I've restarted the server and then I tried to get client to work but all I saw was blinking prompt. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
We are running a 3TB scientific database on a 64 Linux machine with 64GB RAM and 6 TB diskspace. The two largest tables contain 400M (500columns and 12B (!) (6columns) records, each roughly 1TB in storage.
The queries do not pose problems, except for reading sometimes quite some data.
dariuszs wrote:
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi,
64-bit Windows (and a 64-bit MonetDB, I assume?) with 48 GB of memory does not sound too bad --- well, it depends on how large your tables are --- 100 millions rows long ans 300 fields wide, but how wide are the fields, i.e., what type are they?
I you successfully manages to load all the data, I see no obvious reasons why simple `select count(*) from table;` queries should not work as well ...
Could please be a bit more elaborate with discribing your problem?
Could you please recap in detail all the steps since installing MonetDB that eventually led to the current problems?
In particular, we'd need to know of any errors or other message that the server and/or client showed and of any cases where you killed either server or client in any way.
Do I understand you correctly, that you now manage to start the MonetDB/SQL server without problems getting a server console, and that then starting the MonetDB/SQL client (both started the default way, i.e., via the respective entries in the start menu?) yields no a window with a blinking prompt? Can you type on that prompt? If so, can you run a trivial query such as `select 1;`? Do you get an answer for that?
We'd at least need to know all this to get a vague idea what might be wrong --- better still, it would be great if you could give as a clear recipe how we could try to reproduce your problem ...
Stefan
On Tue, Jan 27, 2009 at 04:25:30PM -0500, dariuszs wrote:
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi, Thank you for fast response. Here is what happed: I started the server, then he client, then created 3 tables with 300 fields all variable character with the record lenght 700 bytes - and that was done sequentially. Ran couple of queries, everything worked fine. Started creating another table with data from csv file. Started another client at the same time and submitted a query. The query was taking lots of time, so I've termianted the job.Started another client and all I saw was the blinking prompt. Terminated all the clients. Restarted the server - no problem. Restarted the client - blinking prompt, waited 6 hours to get the sql prompt with no luck. What I've noticed is that during the creation of the first table from csv file memory usage went up to 46GB, after It was complete the server did not release the memory. Is there any way to track what happened? How do I read the log files? I mean I can repeat the process. Thanks so much for the help. Dariusz.
Stefan Manegold wrote:
Hi,
64-bit Windows (and a 64-bit MonetDB, I assume?) with 48 GB of memory does not sound too bad --- well, it depends on how large your tables are --- 100 millions rows long ans 300 fields wide, but how wide are the fields, i.e., what type are they?
I you successfully manages to load all the data, I see no obvious reasons why simple `select count(*) from table;` queries should not work as well ...
Could please be a bit more elaborate with discribing your problem?
Could you please recap in detail all the steps since installing MonetDB that eventually led to the current problems?
In particular, we'd need to know of any errors or other message that the server and/or client showed and of any cases where you killed either server or client in any way.
Do I understand you correctly, that you now manage to start the MonetDB/SQL server without problems getting a server console, and that then starting the MonetDB/SQL client (both started the default way, i.e., via the respective entries in the start menu?) yields no a window with a blinking prompt? Can you type on that prompt? If so, can you run a trivial query such as `select 1;`? Do you get an answer for that?
We'd at least need to know all this to get a vague idea what might be wrong --- better still, it would be great if you could give as a clear recipe how we could try to reproduce your problem ...
Stefan
On Tue, Jan 27, 2009 at 04:25:30PM -0500, dariuszs wrote:
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi,
the most interesting question for us for now is what is your machine and/or mserver5 doing after the restart while the client cannot connect? Is mserver5 / the machine idle (no CPU load no I/O) or is there any (significant) activity (CPU load, I/O)?
... repeating the process on a virgin machine to see whether it is a reproducible problem would also provide us with valuable input ...
Stefan
On Wed, Jan 28, 2009 at 09:44:52AM -0500, dariuszs wrote:
Hi, Thank you for fast response. Here is what happed: I started the server, then he client, then created 3 tables with 300 fields all variable character with the record lenght 700 bytes - and that was done sequentially. Ran couple of queries, everything worked fine. Started creating another table with data from csv file. Started another client at the same time and submitted a query. The query was taking lots of time, so I've termianted the job.Started another client and all I saw was the blinking prompt. Terminated all the clients. Restarted the server - no problem. Restarted the client - blinking prompt, waited 6 hours to get the sql prompt with no luck. What I've noticed is that during the creation of the first table from csv file memory usage went up to 46GB, after It was complete the server did not release the memory. Is there any way to track what happened? How do I read the log files? I mean I can repeat the process. Thanks so much for the help. Dariusz.
Stefan Manegold wrote:
Hi,
64-bit Windows (and a 64-bit MonetDB, I assume?) with 48 GB of memory does not sound too bad --- well, it depends on how large your tables are --- 100 millions rows long ans 300 fields wide, but how wide are the fields, i.e., what type are they?
I you successfully manages to load all the data, I see no obvious reasons why simple `select count(*) from table;` queries should not work as well ...
Could please be a bit more elaborate with discribing your problem?
Could you please recap in detail all the steps since installing MonetDB that eventually led to the current problems?
In particular, we'd need to know of any errors or other message that the server and/or client showed and of any cases where you killed either server or client in any way.
Do I understand you correctly, that you now manage to start the MonetDB/SQL server without problems getting a server console, and that then starting the MonetDB/SQL client (both started the default way, i.e., via the respective entries in the start menu?) yields no a window with a blinking prompt? Can you type on that prompt? If so, can you run a trivial query such as `select 1;`? Do you get an answer for that?
We'd at least need to know all this to get a vague idea what might be wrong --- better still, it would be great if you could give as a clear recipe how we could try to reproduce your problem ...
Stefan
On Tue, Jan 27, 2009 at 04:25:30PM -0500, dariuszs wrote:
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi, Yes I will try to reproduce the error, my question is what do I need to set in order to capture that problem? Right now I don't see any logs or activity anywhere. Thanks. Dariusz.
Stefan Manegold wrote:
Hi,
the most interesting question for us for now is what is your machine and/or mserver5 doing after the restart while the client cannot connect? Is mserver5 / the machine idle (no CPU load no I/O) or is there any (significant) activity (CPU load, I/O)?
... repeating the process on a virgin machine to see whether it is a reproducible problem would also provide us with valuable input ...
Stefan
On Wed, Jan 28, 2009 at 09:44:52AM -0500, dariuszs wrote:
Hi, Thank you for fast response. Here is what happed: I started the server, then he client, then created 3 tables with 300 fields all variable character with the record lenght 700 bytes - and that was done sequentially. Ran couple of queries, everything worked fine. Started creating another table with data from csv file. Started another client at the same time and submitted a query. The query was taking lots of time, so I've termianted the job.Started another client and all I saw was the blinking prompt. Terminated all the clients. Restarted the server - no problem. Restarted the client - blinking prompt, waited 6 hours to get the sql prompt with no luck. What I've noticed is that during the creation of the first table from csv file memory usage went up to 46GB, after It was complete the server did not release the memory. Is there any way to track what happened? How do I read the log files? I mean I can repeat the process. Thanks so much for the help. Dariusz.
Stefan Manegold wrote:
Hi,
64-bit Windows (and a 64-bit MonetDB, I assume?) with 48 GB of memory does not sound too bad --- well, it depends on how large your tables are --- 100 millions rows long ans 300 fields wide, but how wide are the fields, i.e., what type are they?
I you successfully manages to load all the data, I see no obvious reasons why simple `select count(*) from table;` queries should not work as well ...
Could please be a bit more elaborate with discribing your problem?
Could you please recap in detail all the steps since installing MonetDB that eventually led to the current problems?
In particular, we'd need to know of any errors or other message that the server and/or client showed and of any cases where you killed either server or client in any way.
Do I understand you correctly, that you now manage to start the MonetDB/SQL server without problems getting a server console, and that then starting the MonetDB/SQL client (both started the default way, i.e., via the respective entries in the start menu?) yields no a window with a blinking prompt? Can you type on that prompt? If so, can you run a trivial query such as `select 1;`? Do you get an answer for that?
We'd at least need to know all this to get a vague idea what might be wrong --- better still, it would be great if you could give as a clear recipe how we could try to reproduce your problem ...
Stefan
On Tue, Jan 27, 2009 at 04:25:30PM -0500, dariuszs wrote:
Hi, Maybe I need a bigger box for what I'm doing. Right now it's 64 bit windows machine with lots of SAS storage and 48GB of memory. I've setup few tables with over 100 million records and about 300 fields in each table, and I'm running counts on those tables at the same time. Could you advise. Thanks. Dariusz.
Stefan Manegold wrote:
On Tue, Jan 27, 2009 at 03:50:02PM -0500, dariuszs wrote:
Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.
- did you terminate only the client or also the server?
- is your server still running? if not, you need to restart it. if it is, is it idle or busy? in any case quitting the server and restarting it might be a good idea. note though, that starting the server might take some time, in case it need to reply the write-ahead logs.
(do I recall correctly, that you are using the Nov2008 version of MonetDB (or Nov2008-SP1 or Nov2008-SP2?) on Win32?
Stefan
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
Hi, Is there any server log to see why I can't start sql client? Thanks. Dariusz.
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. Dariusz.
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?
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
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
Hi, I'm using group by in both queries - group by gender The first query returns 4 tuples The second query returns 2 tuples I always thought that any filter that you apply should increase the speed Thanks, Dariusz.
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
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.
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
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
Hi, The algebra frontend has no impact on the old query (-G). Thanks. Dariusz.
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
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
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(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _8 := nil; ] [ 15000 usec _12 := algebra.kunion(<tmp_17247>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 0 usec _13 := nil; ] [ 16000 usec _15 := algebra.kdifference(<tmp_17251>[96240405],<~tmp_17017>[0]); ] [ 15000 usec _12 := nil; ] [ 0 usec _14 := nil; ] [ 16000 usec _17 := algebra.markT(<tmp_17247>[96240405],0@0); ] [ 16000 usec _15 := nil; ] [ 15000 usec _18 := bat.reverse(<tmp_17251>[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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17247>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _22 := nil; ] [ 16000 usec _25 := algebra.kunion(<tmp_17257>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _24 := nil; ] [ 0 usec _23 := nil; ] [ 16000 usec _26 := algebra.join(<~tmp_17251>[96240405],<tmp_17247>[96240405]); ] [ 16000 usec _18 := nil; ] [ 15000 usec _25 := nil; ] [ 1016000 usec (ext30,grp28) := group.new(<tmp_17260>[96240405]); ] [ 15000 usec _29 := bat.mirror(<tmp_17170>[4]); ] [ 16000 usec ext30 := nil; ] [ 0 usec _30 := algebra.join(<tmp_17251>[4],<tmp_17260>[96240405]); ] [ 16000 usec _26 := nil; ] [ 1109000 usec _31 := aggr.count(<tmp_17247>[96240405],<tmp_17247>[96240405],<tmp_17251>[4]); ] [ 31000 usec grp28 := nil; ] [ 16000 usec _29 := nil; ] [ 16000 usec _32 := sql.resultSet(2,1,<tmp_17165>[4]); ] [ 15000 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0,<tmp_17165>[4]); ] [ 16000 usec _30 := nil; ] [ 15000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _10 := nil; ] [ 0 usec _14 := algebra.kunion(<tmp_17247>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference(<tmp_17251>[96240405],<~tmp_17017>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_17247>[96240405]); ] [ 15000 usec _17 := nil; ] [ 16000 usec _19 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17174>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17174>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _25 := bat.reverse(<tmp_17174>: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(<tmp_17260>[93838869]); ] [ 0 usec _26 := nil; ] [ 15000 usec _29 := algebra.markT(<~tmp_17260>[93838869],0@0); ] [ 16000 usec _27 := nil; ] [ 16000 usec _30 := bat.reverse(<tmp_17247>[93838869]); ] [ 0 usec _29 := nil; ] [ 437000 usec _31 := algebra.join(<~tmp_17247>[93838869],<tmp_17251>[96240405]); ] [ 63000 usec _30 := nil; ] [ 15000 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_17257>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_17170>[2]); ] [ 16000 usec ext37 := nil; ] [ 0 usec _35 := algebra.join(<tmp_17260>[2],<tmp_17257>[93838869]); ] [ 46000 usec _31 := nil; ] [ 344000 usec _36 := aggr.count(<tmp_17251>[93838869],<tmp_17251>[93838869],<tmp_17260>[2]); ] [ 47000 usec grp35 := nil; ] [ 16000 usec _34 := nil; ] [ 0 usec _37 := sql.resultSet(2,1,<tmp_17165>[2]); ] [ 15000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0,<tmp_17165>[2]); ] [ 16000 usec _35 := nil; ] [ 16000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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(<tmp_5213>:bat[:oid,:str][96240405]); ] [ 16000 usec _10 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _12 := algebra.selectNotNil(<tmp_17032>:bat[:oid,:str][0]); ] [ 0 usec _13 := algebra.kunion(<tmp_5213>[96240405],<tmp_17032>[0]); ] [ 15000 usec _9 := nil; ] [ 16000 usec _12 := nil; ] [ 16000 usec _14 := sql.bind("sys","trw100","f25",2); ] [ 0 usec _16 := algebra.kdifference(<tmp_17260>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _13 := nil; ] [ 16000 usec _17 := algebra.selectNotNil(<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _18 := algebra.kunion(<tmp_17251>[96240405],<tmp_61>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _17 := nil; ] [ 16000 usec _19 := sql.bind_dbat("sys","trw100",1); ] [ 0 usec _20 := bat.reverse(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _19 := nil; ] [ 16000 usec _21 := algebra.kdifference(<tmp_17260>[96240405],<~tmp_17017>[0]); ] [ 16000 usec _18 := nil; ] [ 0 usec _20 := nil; ] [ 15000 usec _22 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_17174>:bat[:oid,:str][1],"F",true); ] [ 15000 usec bat.append(<tmp_17174>:bat[:oid,:str][2],"M",true); ] [ 0 usec _27 := bat.reverse(<tmp_17174>:bat[:oid,:str][2]); ] [ 16000 usec _22 := nil; ] [ 7922000 usec _28 := algebra.join(<tmp_17251>[96240405],<~tmp_17174>[2]); ] [ 16000 usec _27 := nil; ] [ 1859000 usec _29 := algebra.kdifference(<tmp_17251>[96240405],<tmp_17170>[93838869]); ] [ 0 usec _21 := nil; ] [ 141000 usec _28 := nil; ] [ 0 usec _31 := algebra.markT(<tmp_17260>[2401536],0@0); ] [ 15000 usec _29 := nil; ] [ 16000 usec _32 := bat.reverse(<tmp_17170>[2401536]); ] [ 15000 usec _31 := nil; ] [ 16000 usec _33 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 15000 usec _10 := nil; ] [ 0 usec _34 := algebra.kdifference(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _35 := algebra.kunion(<tmp_17257>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _34 := nil; ] [ 16000 usec _14 := nil; ] [ 297000 usec _36 := algebra.join(<~tmp_17170>[2401536],<tmp_17251>[96240405]); ] [ 15000 usec _32 := nil; ] [ 16000 usec _35 := nil; ] [ 31000 usec (ext50,grp48) := group.new(<tmp_17257>[2401536]); ] [ 16000 usec _39 := bat.mirror(<tmp_17163>[2]); ] [ 15000 usec ext50 := nil; ] [ 16000 usec _40 := algebra.join(<tmp_17260>[2],<tmp_17257>[2401536]); ] [ 16000 usec _36 := nil; ] [ 31000 usec _41 := aggr.count(<tmp_17251>[2401536],<tmp_17251>[2401536],<tmp_17260>[2]); ] [ 0 usec grp48 := nil; ] [ 16000 usec _39 := nil; ] [ 15000 usec _42 := sql.resultSet(2,1,<tmp_17165>[2]); ] [ 16000 usec sql.rsColumn(2,"sys.trw100","f25","varchar",1,0,<tmp_17165>[2]); ] [ 0 usec _40 := nil; ] [ 15000 usec sql.rsColumn(2,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17260>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _9 := nil; ] [ 16000 usec _13 := algebra.kunion(<tmp_17251>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _14 := nil; ] [ 16000 usec _16 := algebra.kdifference(<tmp_17260>[96240405],<~tmp_17017>[0]); ] [ 0 usec _15 := nil; ] [ 3656000 usec _17 := algebra.antiuselect(<tmp_17251>[96240405],""); ] [ 16000 usec _16 := nil; ] [ 0 usec _19 := algebra.markT(<tmp_17170>[96240386],0@0); ] [ 16000 usec _17 := nil; ] [ 15000 usec _20 := bat.reverse(<tmp_17251>[96240386]); ] [ 16000 usec _19 := nil; ] [ 453000 usec _21 := algebra.join(<~tmp_17251>[96240386],<tmp_17260>[96240405]); ] [ 31000 usec _20 := nil; ] [ 0 usec _13 := nil; ] [ 969000 usec (ext26,grp24) := group.new(<tmp_17257>[96240386]); ] [ 0 usec _24 := bat.mirror(<tmp_17163>[3]); ] [ 15000 usec ext26 := nil; ] [ 16000 usec _25 := algebra.join(<tmp_17170>[3],<tmp_17257>[96240386]); ] [ 31000 usec _21 := nil; ] [ 359000 usec _26 := aggr.count(<tmp_17260>[96240386],<tmp_17260>[96240386],<tmp_17170>[3]); ] [ 47000 usec grp24 := nil; ] [ 16000 usec _24 := nil; ] [ 16000 usec _27 := sql.resultSet(2,1,<tmp_17165>[3]); ] [ 15000 usec sql.rsColumn(3,"sys.trw100","f25","varchar",1,0,<tmp_17165>[3]); ] [ 0 usec _25 := nil; ] [ 16000 usec sql.rsColumn(3,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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
On Wed, Jan 28, 2009 at 12:49:13PM -0500, dariuszs wrote:
Hi, Thanks for your help. Please change the extenstion to .txt for the attached trace. Gender is f25. Thanks again. Dariusz.
thanks. here you see where the time goes:
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(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _8 := nil; ] [ 15000 usec _12 := algebra.kunion(<tmp_17247>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 0 usec _13 := nil; ] [ 16000 usec _15 := algebra.kdifference(<tmp_17251>[96240405],<~tmp_17017>[0]); ] [ 15000 usec _12 := nil; ] [ 0 usec _14 := nil; ] [ 16000 usec _17 := algebra.markT(<tmp_17247>[96240405],0@0); ] [ 16000 usec _15 := nil; ] [ 15000 usec _18 := bat.reverse(<tmp_17251>[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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17247>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _22 := nil; ] [ 16000 usec _25 := algebra.kunion(<tmp_17257>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _24 := nil; ] [ 0 usec _23 := nil; ] [ 16000 usec _26 := algebra.join(<~tmp_17251>[96240405],<tmp_17247>[96240405]); ] [ 16000 usec _18 := nil; ] [ 15000 usec _25 := nil; ] [ 1016000 usec (ext30,grp28) := group.new(<tmp_17260>[96240405]); ] [ 15000 usec _29 := bat.mirror(<tmp_17170>[4]); ] [ 16000 usec ext30 := nil; ] [ 0 usec _30 := algebra.join(<tmp_17251>[4],<tmp_17260>[96240405]); ] [ 16000 usec _26 := nil; ] [ 1109000 usec _31 := aggr.count(<tmp_17247>[96240405],<tmp_17247>[96240405],<tmp_17251>[4]); ] [ 31000 usec grp28 := nil; ] [ 16000 usec _29 := nil; ] [ 16000 usec _32 := sql.resultSet(2,1,<tmp_17165>[4]); ] [ 15000 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0,<tmp_17165>[4]); ] [ 16000 usec _30 := nil; ] [ 15000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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(); ]
^^^^^^^^^^^^ "base-line" 2.7 secs
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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _10 := nil; ] [ 0 usec _14 := algebra.kunion(<tmp_17247>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference(<tmp_17251>[96240405],<~tmp_17017>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_17247>[96240405]); ] [ 15000 usec _17 := nil; ] [ 16000 usec _19 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17174>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17174>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _25 := bat.reverse(<tmp_17174>:bat[:oid,:str][2]); ] [ 16000 usec _19 := nil; ] [ 11172000 usec _26 := algebra.semijoin(<~tmp_17247>[96240405],<~tmp_17174>[2]); ]
^^^^^^^^^^^^^ ~ 11 secs for the semijoin that implements "where f25 in ('F','M')"
[ 15000 usec _18 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _27 := bat.reverse(<tmp_17260>[93838869]); ] [ 0 usec _26 := nil; ] [ 15000 usec _29 := algebra.markT(<~tmp_17260>[93838869],0@0); ] [ 16000 usec _27 := nil; ] [ 16000 usec _30 := bat.reverse(<tmp_17247>[93838869]); ] [ 0 usec _29 := nil; ] [ 437000 usec _31 := algebra.join(<~tmp_17247>[93838869],<tmp_17251>[96240405]); ] [ 63000 usec _30 := nil; ] [ 15000 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_17257>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_17170>[2]); ] [ 16000 usec ext37 := nil; ] [ 0 usec _35 := algebra.join(<tmp_17260>[2],<tmp_17257>[93838869]); ] [ 46000 usec _31 := nil; ] [ 344000 usec _36 := aggr.count(<tmp_17251>[93838869],<tmp_17251>[93838869],<tmp_17260>[2]); ] [ 47000 usec grp35 := nil; ] [ 16000 usec _34 := nil; ] [ 0 usec _37 := sql.resultSet(2,1,<tmp_17165>[2]); ] [ 15000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0,<tmp_17165>[2]); ] [ 16000 usec _35 := nil; ] [ 16000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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"); ]
^^^^^^^^^^^^^ 13.6 secs in total, i.e., ~= 2.7 secs "base-line" + 11 secs for the extra "where f25 in ('F','M')" predicate (i.e., the extra work that your query requests)
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(<tmp_5213>:bat[:oid,:str][96240405]); ] [ 16000 usec _10 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _12 := algebra.selectNotNil(<tmp_17032>:bat[:oid,:str][0]); ] [ 0 usec _13 := algebra.kunion(<tmp_5213>[96240405],<tmp_17032>[0]); ] [ 15000 usec _9 := nil; ] [ 16000 usec _12 := nil; ] [ 16000 usec _14 := sql.bind("sys","trw100","f25",2); ] [ 0 usec _16 := algebra.kdifference(<tmp_17260>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _13 := nil; ] [ 16000 usec _17 := algebra.selectNotNil(<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _18 := algebra.kunion(<tmp_17251>[96240405],<tmp_61>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _17 := nil; ] [ 16000 usec _19 := sql.bind_dbat("sys","trw100",1); ] [ 0 usec _20 := bat.reverse(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _19 := nil; ] [ 16000 usec _21 := algebra.kdifference(<tmp_17260>[96240405],<~tmp_17017>[0]); ] [ 16000 usec _18 := nil; ] [ 0 usec _20 := nil; ] [ 15000 usec _22 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_17174>:bat[:oid,:str][1],"F",true); ] [ 15000 usec bat.append(<tmp_17174>:bat[:oid,:str][2],"M",true); ] [ 0 usec _27 := bat.reverse(<tmp_17174>:bat[:oid,:str][2]); ] [ 16000 usec _22 := nil; ] [ 7922000 usec _28 := algebra.join(<tmp_17251>[96240405],<~tmp_17174>[2]); ]
^^^^^^^^^^^^
[ 16000 usec _27 := nil; ] [ 1859000 usec _29 := algebra.kdifference(<tmp_17251>[96240405],<tmp_17170>[93838869]); ]
^^^^^^^^^^^^ ~ 7.9 + 1.9 = 9.8 secs for the "where f25 not in ('F','M')"
[ 0 usec _21 := nil; ] [ 141000 usec _28 := nil; ] [ 0 usec _31 := algebra.markT(<tmp_17260>[2401536],0@0); ] [ 15000 usec _29 := nil; ] [ 16000 usec _32 := bat.reverse(<tmp_17170>[2401536]); ] [ 15000 usec _31 := nil; ] [ 16000 usec _33 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 15000 usec _10 := nil; ] [ 0 usec _34 := algebra.kdifference(<tmp_17251>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _35 := algebra.kunion(<tmp_17257>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _34 := nil; ] [ 16000 usec _14 := nil; ] [ 297000 usec _36 := algebra.join(<~tmp_17170>[2401536],<tmp_17251>[96240405]); ] [ 15000 usec _32 := nil; ] [ 16000 usec _35 := nil; ] [ 31000 usec (ext50,grp48) := group.new(<tmp_17257>[2401536]); ] [ 16000 usec _39 := bat.mirror(<tmp_17163>[2]); ] [ 15000 usec ext50 := nil; ] [ 16000 usec _40 := algebra.join(<tmp_17260>[2],<tmp_17257>[2401536]); ] [ 16000 usec _36 := nil; ] [ 31000 usec _41 := aggr.count(<tmp_17251>[2401536],<tmp_17251>[2401536],<tmp_17260>[2]); ] [ 0 usec grp48 := nil; ] [ 16000 usec _39 := nil; ] [ 15000 usec _42 := sql.resultSet(2,1,<tmp_17165>[2]); ] [ 16000 usec sql.rsColumn(2,"sys.trw100","f25","varchar",1,0,<tmp_17165>[2]); ] [ 0 usec _40 := nil; ] [ 15000 usec sql.rsColumn(2,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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"); ]
^^^^^^^^^^^^^ ~ 11.4 secs ~= 2.7 secs "base-line" + 9.8 secs for extra predicate as above
:48266[0]:R :56656[0]:W Strace select f25,count(*) from trw100 where f25 <> '' group by f25;
^^^^^^^^^^^^^^^ should obviously have been where f25 <> 'U' ^
: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(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>: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(<tmp_17260>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _9 := nil; ] [ 16000 usec _13 := algebra.kunion(<tmp_17251>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _14 := nil; ] [ 16000 usec _16 := algebra.kdifference(<tmp_17260>[96240405],<~tmp_17017>[0]); ] [ 0 usec _15 := nil; ] [ 3656000 usec _17 := algebra.antiuselect(<tmp_17251>[96240405],""); ]
^^^^^^^^^^^^ ~ 3.6 secs for the "where f25 <> 'U'"
[ 16000 usec _16 := nil; ] [ 0 usec _19 := algebra.markT(<tmp_17170>[96240386],0@0); ] [ 16000 usec _17 := nil; ] [ 15000 usec _20 := bat.reverse(<tmp_17251>[96240386]); ] [ 16000 usec _19 := nil; ] [ 453000 usec _21 := algebra.join(<~tmp_17251>[96240386],<tmp_17260>[96240405]); ] [ 31000 usec _20 := nil; ] [ 0 usec _13 := nil; ] [ 969000 usec (ext26,grp24) := group.new(<tmp_17257>[96240386]); ] [ 0 usec _24 := bat.mirror(<tmp_17163>[3]); ] [ 15000 usec ext26 := nil; ] [ 16000 usec _25 := algebra.join(<tmp_17170>[3],<tmp_17257>[96240386]); ] [ 31000 usec _21 := nil; ] [ 359000 usec _26 := aggr.count(<tmp_17260>[96240386],<tmp_17260>[96240386],<tmp_17170>[3]); ] [ 47000 usec grp24 := nil; ] [ 16000 usec _24 := nil; ] [ 16000 usec _27 := sql.resultSet(2,1,<tmp_17165>[3]); ] [ 15000 usec sql.rsColumn(3,"sys.trw100","f25","varchar",1,0,<tmp_17165>[3]); ] [ 0 usec _25 := nil; ] [ 16000 usec sql.rsColumn(3,"sys.trw100","count_f25","wrd",64,0,<tmp_17267>: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(""); ]
^^^^^^^^^^^^ ~ 6 secs ~= 2.7 secs "base=line" + 3.6 secs for "where f25 <> 'U'"
essence: the domination cost in your query is the extra predicate; the simple the extra predicate the cheaper the extra cost for it.
Stefan
Hi, Could you please take a look at the second trace. I rewrote the query and it's performing very well. Is that the way I should do all the queries since most queries require filters and can I do complex queries like that? Thanks again. Dariusz.
:0[0]:W Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25; :13422[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _10 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 15000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ] [ 0 usec _13 := algebra.kdifference(<tmp_17326>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _10 := nil; ] [ 15000 usec _14 := algebra.kunion(<tmp_17325>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference(<tmp_17326>[96240405],<~tmp_17017>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_17325>[96240405]); ] [ 16000 usec _17 := nil; ] [ 15000 usec _19 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17320>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17320>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _25 := bat.reverse(<tmp_17320>:bat[:oid,:str][2]); ] [ 16000 usec _19 := nil; ] [ 10953000 usec _26 := algebra.semijoin(<~tmp_17325>[96240405],<~tmp_17320>[2]); ] [ 16000 usec _18 := nil; ] [ 15000 usec _25 := nil; ] [ 0 usec _27 := bat.reverse(<tmp_17323>[93838869]); ] [ 16000 usec _26 := nil; ] [ 16000 usec _29 := algebra.markT(<~tmp_17323>[93838869],0@0); ] [ 15000 usec _27 := nil; ] [ 16000 usec _30 := bat.reverse(<tmp_17325>[93838869]); ] [ 0 usec _29 := nil; ] [ 437000 usec _31 := algebra.join(<~tmp_17325>[93838869],<tmp_17326>[96240405]); ] [ 79000 usec _30 := nil; ] [ 15000 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_17300>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_17327>[2]); ] [ 16000 usec ext37 := nil; ] [ 15000 usec _35 := algebra.join(<tmp_17323>[2],<tmp_17300>[93838869]); ] [ 31000 usec _31 := nil; ] [ 360000 usec _36 := aggr.count(<tmp_17326>[93838869],<tmp_17326>[93838869],<tmp_17323>[2]); ] [ 47000 usec grp35 := nil; ] [ 15000 usec _34 := nil; ] [ 0 usec _37 := sql.resultSet(2,1,<tmp_17330>[2]); ] [ 16000 usec sql.rsColumn(14,"sys.trw100","f25","varchar",1,0,<tmp_17330>[2]); ] [ 16000 usec _35 := nil; ] [ 15000 usec sql.rsColumn(14,"sys.trw100","count_f25","wrd",64,0,<tmp_17275>:bat[:oid,:wrd][2]); ] [ 16000 usec _36 := nil; ] [ 15000 usec _45 := io.stdout(); ] &1 14 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 16000 usec sql.exportResult(137931296,14,""); ] [ 13422000 usec user.s1_1("F","M"); ] :13437[0]:R :86969[0]:W Strace select * from (select f25,count(*) from trw100 group by f25) as t1 where t1.f25 in ('F','M'); :89953[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 0 usec _10 := algebra.kunion(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 15000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 0 usec _13 := algebra.kdifference(<tmp_17323>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_17326>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _15 := nil; ] [ 16000 usec _17 := algebra.kdifference(<tmp_17323>[96240405],<~tmp_17017>[0]); ] [ 0 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 15000 usec _19 := algebra.markT(<tmp_17326>[96240405],0@0); ] [ 16000 usec _17 := nil; ] [ 0 usec _20 := bat.reverse(<tmp_17323>[96240405]); ] [ 16000 usec _19 := nil; ] [ 15000 usec _21 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _23 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _24 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 15000 usec _21 := nil; ] [ 16000 usec _23 := nil; ] [ 16000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _26 := algebra.kdifference(<tmp_17326>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion(<tmp_17300>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _26 := nil; ] [ 15000 usec _25 := nil; ] [ 0 usec _28 := algebra.join(<~tmp_17323>[96240405],<tmp_17326>[96240405]); ] [ 16000 usec _20 := nil; ] [ 15000 usec _27 := nil; ] [ 938000 usec (ext32,grp30) := group.new(<tmp_17325>[96240405]); ] [ 16000 usec _31 := bat.mirror(<tmp_17327>[4]); ] [ 15000 usec ext32 := nil; ] [ 0 usec _32 := algebra.join(<tmp_17323>[4],<tmp_17325>[96240405]); ] [ 16000 usec _28 := nil; ] [ 15000 usec _33 := bat.reverse(<tmp_17330>[4]); ] [ 16000 usec _34 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17320>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17320>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _40 := bat.reverse(<tmp_17320>:bat[:oid,:str][2]); ] [ 16000 usec _34 := nil; ] [ 16000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_17320>[2]); ] [ 0 usec _33 := nil; ] [ 15000 usec _40 := nil; ] [ 16000 usec _42 := bat.reverse(<tmp_17325>[2]); ] [ 15000 usec _41 := nil; ] [ 0 usec _43 := algebra.markT(<~tmp_17325>[2],0@0); ] [ 16000 usec _42 := nil; ] [ 16000 usec _44 := bat.reverse(<tmp_17300>[2]); ] [ 15000 usec _43 := nil; ] [ 0 usec _45 := algebra.join(<~tmp_17300>[2],<tmp_17330>[4]); ] [ 16000 usec _32 := nil; ] [ 1172000 usec _46 := aggr.count(<tmp_17326>[96240405],<tmp_17326>[96240405],<tmp_17323>[4]); ] [ 47000 usec grp30 := nil; ] [ 15000 usec _31 := nil; ] [ 16000 usec _47 := algebra.join(<~tmp_17300>[2],<tmp_17275>:bat[:oid,:wrd][4]); ] [ 16000 usec _44 := nil; ] [ 15000 usec _46 := nil; ] [ 0 usec _48 := sql.resultSet(2,1,<~tmp_17332>[2]); ] [ 16000 usec sql.rsColumn(15,"sys.t1","f25","varchar",1,0,<~tmp_17332>[2]); ] [ 15000 usec _45 := nil; ] [ 16000 usec sql.rsColumn(15,"sys.t1","count_f25","wrd",64,0,<~tmp_17254>[2]); ] [ 16000 usec _47 := nil; ] [ 15000 usec _56 := io.stdout(); ] &1 15 2 2 2 % sys.t1, sys.t1 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 0 usec sql.exportResult(137931296,15,""); ] [ 2984000 usec user.s4_1("F","M"); ] :90000[0]:R :111640[0]:W Connection closed
On Wed, Jan 28, 2009 at 01:32:09PM -0500, dariuszs wrote:
Hi, Could you please take a look at the second trace. I rewrote the query and it's performing very well. Is that the way I should do all the queries since most queries require filters and can I do complex queries like that? Thanks again. Dariusz.
The point is that in your query, you have a predicate on the group-by attribute; hence, there are two way to evaluate this, as reflected by the two variant of your query:
(1) first perform the restriction according to the predicate; the perform the grouping (plus aggregation, here: count).
(2) first perform the grouping (plus aggregation, here: count) on the whole table, and the restriction according to the predicate.
It appears that the evaluating the predicate on the whole table seems to be (significatly) more expensive than the grouping + aggregation --- the reason is most probably that your predicate selects 93838869 out of 96240405 tuples, i.e., MonetDB needs to materialize a rather larger intermediate result of ~97.5% of the original column, while the grouping results in only 3 tuples --- hence, doing the grouping first is (much) faster.
In priciple, our optimizer should be able to detect that case and rewrite the query accordingly --- we need to analyse why it doesn't --- might not be that trivial in teh general case...
Hence, for now, you might want rewrite queries that fall into this specific pattern as you did.
Stefan
:0[0]:W Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25; :13422[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _10 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 15000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ] [ 0 usec _13 := algebra.kdifference(<tmp_17326>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _10 := nil; ] [ 15000 usec _14 := algebra.kunion(<tmp_17325>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference(<tmp_17326>[96240405],<~tmp_17017>[0]); ] [ 0 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_17325>[96240405]); ] [ 16000 usec _17 := nil; ] [ 15000 usec _19 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17320>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17320>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _25 := bat.reverse(<tmp_17320>:bat[:oid,:str][2]); ] [ 16000 usec _19 := nil; ] [ 10953000 usec _26 := algebra.semijoin(<~tmp_17325>[96240405],<~tmp_17320>[2]); ] [ 16000 usec _18 := nil; ] [ 15000 usec _25 := nil; ] [ 0 usec _27 := bat.reverse(<tmp_17323>[93838869]); ] [ 16000 usec _26 := nil; ] [ 16000 usec _29 := algebra.markT(<~tmp_17323>[93838869],0@0); ] [ 15000 usec _27 := nil; ] [ 16000 usec _30 := bat.reverse(<tmp_17325>[93838869]); ] [ 0 usec _29 := nil; ] [ 437000 usec _31 := algebra.join(<~tmp_17325>[93838869],<tmp_17326>[96240405]); ] [ 79000 usec _30 := nil; ] [ 15000 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_17300>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_17327>[2]); ] [ 16000 usec ext37 := nil; ] [ 15000 usec _35 := algebra.join(<tmp_17323>[2],<tmp_17300>[93838869]); ] [ 31000 usec _31 := nil; ] [ 360000 usec _36 := aggr.count(<tmp_17326>[93838869],<tmp_17326>[93838869],<tmp_17323>[2]); ] [ 47000 usec grp35 := nil; ] [ 15000 usec _34 := nil; ] [ 0 usec _37 := sql.resultSet(2,1,<tmp_17330>[2]); ] [ 16000 usec sql.rsColumn(14,"sys.trw100","f25","varchar",1,0,<tmp_17330>[2]); ] [ 16000 usec _35 := nil; ] [ 15000 usec sql.rsColumn(14,"sys.trw100","count_f25","wrd",64,0,<tmp_17275>:bat[:oid,:wrd][2]); ] [ 16000 usec _36 := nil; ] [ 15000 usec _45 := io.stdout(); ] &1 14 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 16000 usec sql.exportResult(137931296,14,""); ] [ 13422000 usec user.s1_1("F","M"); ] :13437[0]:R :86969[0]:W Strace select * from (select f25,count(*) from trw100 group by f25) as t1 where t1.f25 in ('F','M'); :89953[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 0 usec _10 := algebra.kunion(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 15000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 0 usec _13 := algebra.kdifference(<tmp_17323>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_17326>[96240405],<tmp_61>: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(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _15 := nil; ] [ 16000 usec _17 := algebra.kdifference(<tmp_17323>[96240405],<~tmp_17017>[0]); ] [ 0 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 15000 usec _19 := algebra.markT(<tmp_17326>[96240405],0@0); ] [ 16000 usec _17 := nil; ] [ 0 usec _20 := bat.reverse(<tmp_17323>[96240405]); ] [ 16000 usec _19 := nil; ] [ 15000 usec _21 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _23 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _24 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 15000 usec _21 := nil; ] [ 16000 usec _23 := nil; ] [ 16000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _26 := algebra.kdifference(<tmp_17326>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion(<tmp_17300>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _26 := nil; ] [ 15000 usec _25 := nil; ] [ 0 usec _28 := algebra.join(<~tmp_17323>[96240405],<tmp_17326>[96240405]); ] [ 16000 usec _20 := nil; ] [ 15000 usec _27 := nil; ] [ 938000 usec (ext32,grp30) := group.new(<tmp_17325>[96240405]); ] [ 16000 usec _31 := bat.mirror(<tmp_17327>[4]); ] [ 15000 usec ext32 := nil; ] [ 0 usec _32 := algebra.join(<tmp_17323>[4],<tmp_17325>[96240405]); ] [ 16000 usec _28 := nil; ] [ 15000 usec _33 := bat.reverse(<tmp_17330>[4]); ] [ 16000 usec _34 := bat.new(nil,nil); ] [ 0 usec bat.append(<tmp_17320>:bat[:oid,:str][1],"F",true); ] [ 16000 usec bat.append(<tmp_17320>:bat[:oid,:str][2],"M",true); ] [ 15000 usec _40 := bat.reverse(<tmp_17320>:bat[:oid,:str][2]); ] [ 16000 usec _34 := nil; ] [ 16000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_17320>[2]); ] [ 0 usec _33 := nil; ] [ 15000 usec _40 := nil; ] [ 16000 usec _42 := bat.reverse(<tmp_17325>[2]); ] [ 15000 usec _41 := nil; ] [ 0 usec _43 := algebra.markT(<~tmp_17325>[2],0@0); ] [ 16000 usec _42 := nil; ] [ 16000 usec _44 := bat.reverse(<tmp_17300>[2]); ] [ 15000 usec _43 := nil; ] [ 0 usec _45 := algebra.join(<~tmp_17300>[2],<tmp_17330>[4]); ] [ 16000 usec _32 := nil; ] [ 1172000 usec _46 := aggr.count(<tmp_17326>[96240405],<tmp_17326>[96240405],<tmp_17323>[4]); ] [ 47000 usec grp30 := nil; ] [ 15000 usec _31 := nil; ] [ 16000 usec _47 := algebra.join(<~tmp_17300>[2],<tmp_17275>:bat[:oid,:wrd][4]); ] [ 16000 usec _44 := nil; ] [ 15000 usec _46 := nil; ] [ 0 usec _48 := sql.resultSet(2,1,<~tmp_17332>[2]); ] [ 16000 usec sql.rsColumn(15,"sys.t1","f25","varchar",1,0,<~tmp_17332>[2]); ] [ 15000 usec _45 := nil; ] [ 16000 usec sql.rsColumn(15,"sys.t1","count_f25","wrd",64,0,<~tmp_17254>[2]); ] [ 16000 usec _47 := nil; ] [ 15000 usec _56 := io.stdout(); ] &1 15 2 2 2 % sys.t1, sys.t1 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 0 usec sql.exportResult(137931296,15,""); ] [ 2984000 usec user.s4_1("F","M"); ] :90000[0]:R :111640[0]:W Connection closed
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
Hi, Can I use this database for commercial applications? Thanks. Dariusz.
dariuszs wrote:
Hi, Can I use this database for commercial applications? Thanks. Dariusz.
Yes you can. See the license agreement. There are no constraints, other than a proper reference to the use of MonetDB in your product.
Of course, we would like to learn about the product empowered by MonetDB for proper attribution on the website.
regards, Martin
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
On Wed, Jan 28, 2009 at 11:47 AM, dariuszs dariuszs@svnp.com 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.
I might suggest that it will be cleaner to do it this way.
select gender, count(*) from table1 group by gender having gender in ('F','M');
(In the past, I've found that the construct "select ... from (select ....)" ends up creating SQL that's really hard to debug. YMMV.)
On Wed, Jan 28, 2009 at 02:21:37PM -0500, Chris Curvey wrote:
On Wed, Jan 28, 2009 at 11:47 AM, dariuszs dariuszs@svnp.com 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.
I might suggest that it will be cleaner to do it this way.
select gender, count(*) from table1 group by gender having gender in ('F','M');
(In the past, I've found that the construct "select ... from (select ....)" ends up creating SQL that's really hard to debug. YMMV.)
indeed a good idea --- [cs]hould have been mine ... ;-)
dariuszs, feel free to test/trace this one on your data, too.
Stefan
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
Hi, I just did that - no improvement, still grouping by first on fields with little distinct values seems to be the way to go, see attached. Thanks. Dariusz.
Stefan Manegold wrote:
On Wed, Jan 28, 2009 at 02:21:37PM -0500, Chris Curvey wrote:
On Wed, Jan 28, 2009 at 11:47 AM, dariuszs dariuszs@svnp.com 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.
I might suggest that it will be cleaner to do it this way.
select gender, count(*) from table1 group by gender having gender in ('F','M');
(In the past, I've found that the construct "select ... from (select ....)" ends up creating SQL that's really hard to debug. YMMV.)
indeed a good idea --- [cs]hould have been mine ... ;-)
dariuszs, feel free to test/trace this one on your data, too.
Stefan
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
:250000[0]:W Strace select f25,count(*) from trw100 group by f25 having f25 in ('F','M'); :261735[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 15000 usec _10 := algebra.kunion(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 15000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 16000 usec _13 := algebra.kdifference(<tmp_20163>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_20173>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _13 := nil; ] [ 15000 usec _11 := nil; ] [ 16000 usec _15 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _16 := bat.reverse(<tmp_17017>:bat[:oid,:oid][0]); ] [ 15000 usec _15 := nil; ] [ 16000 usec _17 := algebra.kdifference(<tmp_20163>[96240405],<~tmp_17017>[0]); ] [ 15000 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 16000 usec _19 := algebra.markT(<tmp_20173>[96240405],0@0); ] [ 15000 usec _17 := nil; ] [ 16000 usec _20 := bat.reverse(<tmp_20163>[96240405]); ] [ 16000 usec _19 := nil; ] [ 15000 usec _21 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _23 := sql.bind("sys","trw100","f25",1); ] [ 15000 usec _24 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _21 := nil; ] [ 16000 usec _23 := nil; ] [ 15000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _26 := algebra.kdifference(<tmp_20173>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 31000 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion(<tmp_20160>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _26 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _28 := algebra.join(<~tmp_20163>[96240405],<tmp_20173>[96240405]); ] [ 15000 usec _20 := nil; ] [ 16000 usec _27 := nil; ] [ 937000 usec (ext32,grp30) := group.new(<tmp_20200>[96240405]); ] [ 16000 usec _31 := bat.mirror(<tmp_20020>[4]); ] [ 16000 usec ext32 := nil; ] [ 15000 usec _32 := algebra.join(<tmp_20163>[4],<tmp_20200>[96240405]); ] [ 16000 usec _33 := bat.reverse(<tmp_17330>[4]); ] [ 16000 usec _32 := nil; ] [ 15000 usec _34 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_20176>:bat[:oid,:str][1],"F",true); ] [ 15000 usec bat.append(<tmp_20176>:bat[:oid,:str][2],"M",true); ] [ 16000 usec _40 := bat.reverse(<tmp_20176>:bat[:oid,:str][2]); ] [ 16000 usec _34 := nil; ] [ 15000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_20176>[2]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _40 := nil; ] [ 15000 usec _42 := bat.reverse(<tmp_20104>[2]); ] [ 16000 usec _41 := nil; ] [ 15000 usec _43 := algebra.semijoin(<tmp_20163>[4],<~tmp_20104>[2]); ] [ 16000 usec _31 := nil; ] [ 16000 usec _44 := algebra.join(<~tmp_20160>[2],<tmp_20200>[96240405]); ] [ 15000 usec _28 := nil; ] [ 16000 usec _45 := bat.reverse(<tmp_20173>[96240405]); ] [ 16000 usec grp30 := nil; ] [ 6531000 usec _46 := algebra.semijoin(<~tmp_20173>[96240405],<~tmp_20104>[2]); ] [ 31000 usec _45 := nil; ] [ 16000 usec _42 := nil; ] [ 31000 usec _47 := bat.reverse(<tmp_20200>[93838869]); ] [ 16000 usec _46 := nil; ] [ 3062000 usec _48 := aggr.count(<~tmp_20200>[93838869],<~tmp_20200>[93838869],<~tmp_20160>[2]); ] [ 63000 usec _47 := nil; ] [ 15000 usec _43 := nil; ] [ 16000 usec _49 := sql.resultSet(2,1,<tmp_20174>[2]); ] [ 31000 usec sql.rsColumn(4,"sys.trw100","f25","varchar",1,0,<tmp_20174>[2]); ] [ 16000 usec _44 := nil; ] [ 15000 usec sql.rsColumn(4,"sys.trw100","count_f25","wrd",64,0,<tmp_17275>:bat[:oid,:wrd][2]); ] [ 16000 usec _48 := nil; ] [ 16000 usec _57 := io.stdout(); ] &1 4 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(1781102128,4,""); ] [ 11735000 usec user.s27_1("F","M"); ] :261782[0]:R :274375[0]:W Strace select * from (select f25,count(*) from trw100 group by f25) as t1 where t1.f25 in ('F','M'); :277563[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f1",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f1",1); ] [ 15000 usec _10 := algebra.kunion(<tmp_11270>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _3 := nil; ] [ 16000 usec _8 := nil; ] [ 15000 usec _11 := sql.bind("sys","trw100","f1",2); ] [ 16000 usec _13 := algebra.kdifference(<tmp_20174>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 31000 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_20160>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _13 := nil; ] [ 16000 usec _11 := nil; ] [ 16000 usec _15 := sql.bind_dbat("sys","trw100",1); ] [ 15000 usec _16 := bat.reverse(<tmp_17017>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 15000 usec _17 := algebra.kdifference(<tmp_20174>[96240405],<~tmp_17017>[0]); ] [ 16000 usec _14 := nil; ] [ 16000 usec _16 := nil; ] [ 15000 usec _19 := algebra.markT(<tmp_20160>[96240405],0@0); ] [ 16000 usec _17 := nil; ] [ 16000 usec _20 := bat.reverse(<tmp_20174>[96240405]); ] [ 15000 usec _19 := nil; ] [ 16000 usec _21 := sql.bind("sys","trw100","f25",0); ] [ 15000 usec _23 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _24 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_17032>:bat[:oid,:str][0]); ] [ 16000 usec _21 := nil; ] [ 15000 usec _23 := nil; ] [ 16000 usec _25 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _26 := algebra.kdifference(<tmp_20160>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _24 := nil; ] [ 16000 usec _27 := algebra.kunion(<tmp_20200>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 15000 usec _26 := nil; ] [ 16000 usec _25 := nil; ] [ 16000 usec _28 := algebra.join(<~tmp_20174>[96240405],<tmp_20160>[96240405]); ] [ 15000 usec _20 := nil; ] [ 16000 usec _27 := nil; ] [ 937000 usec (ext32,grp30) := group.new(<tmp_20163>[96240405]); ] [ 16000 usec _31 := bat.mirror(<tmp_20020>[4]); ] [ 16000 usec ext32 := nil; ] [ 15000 usec _32 := algebra.join(<tmp_20174>[4],<tmp_20163>[96240405]); ] [ 16000 usec _28 := nil; ] [ 16000 usec _33 := bat.reverse(<tmp_17330>[4]); ] [ 15000 usec _34 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_20176>:bat[:oid,:str][1],"F",true); ] [ 31000 usec bat.append(<tmp_20176>:bat[:oid,:str][2],"M",true); ] [ 16000 usec _40 := bat.reverse(<tmp_20176>:bat[:oid,:str][2]); ] [ 15000 usec _34 := nil; ] [ 16000 usec _41 := algebra.semijoin(<~tmp_17330>[4],<~tmp_20176>[2]); ] [ 16000 usec _33 := nil; ] [ 15000 usec _40 := nil; ] [ 16000 usec _42 := bat.reverse(<tmp_20104>[2]); ] [ 15000 usec _41 := nil; ] [ 16000 usec _43 := algebra.markT(<~tmp_20104>[2],0@0); ] [ 16000 usec _42 := nil; ] [ 15000 usec _44 := bat.reverse(<tmp_20163>[2]); ] [ 16000 usec _43 := nil; ] [ 16000 usec _45 := algebra.join(<~tmp_20163>[2],<tmp_17330>[4]); ] [ 15000 usec _32 := nil; ] [ 1110000 usec _46 := aggr.count(<tmp_20160>[96240405],<tmp_20160>[96240405],<tmp_20174>[4]); ] [ 31000 usec grp30 := nil; ] [ 15000 usec _31 := nil; ] [ 16000 usec _47 := algebra.join(<~tmp_20163>[2],<tmp_17275>:bat[:oid,:wrd][4]); ] [ 16000 usec _44 := nil; ] [ 15000 usec _46 := nil; ] [ 32000 usec _48 := sql.resultSet(2,1,<~tmp_20177>[2]); ] [ 15000 usec sql.rsColumn(5,"sys.t1","f25","varchar",1,0,<~tmp_20177>[2]); ] [ 16000 usec _45 := nil; ] [ 15000 usec sql.rsColumn(5,"sys.t1","count_f25","wrd",64,0,<~tmp_17254>[2]); ] [ 16000 usec _47 := nil; ] [ 16000 usec _56 := io.stdout(); ] &1 5 2 2 2 % sys.t1, sys.t1 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 15000 usec sql.exportResult(1781102128,5,""); ] [ 3188000 usec user.s4_1("F","M"); ] :277594[0]:R :280172[0]:W Connection closed
participants (5)
-
Chris Curvey -
dariuszs -
Martin Kersten -
Niels Nes -
Stefan Manegold