Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1)
Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://monetdb:50000/lemonde'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>SELECT DISTINCT Device FROM donnees_lmd_pierre ;
+----------------------+
| device |
+======================+
| Application Tablette |
| Application Mobile |
| Web/Tablette |
| Web/Ordinateur |
| Web/Mobile |
| |
+----------------------+
6 tuples (10.4s)
sql>CREATE TABLE tmp__pierre5 AS SELECT id_unique,'Application Tablette' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='Application Tablette' GROUP BY id_unique WITH DATA;
operation successful (4.8s)
sql>INSERT INTO tmp__pierre5 SELECT id_unique,'Application Mobile' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='Application Mobile' GROUP BY id_unique;
13724181 affected rows (27.8s)
sql>INSERT INTO tmp__pierre5 SELECT id_unique,'Web/Tablette' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='Web/Tablette' GROUP BY id_unique;
8491961 affected rows (8.4s)
sql>INSERT INTO tmp__pierre5 SELECT id_unique,'Web/Ordinateur' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='Web/Ordinateur' GROUP BY id_unique;
43188751 affected rows (46.0s)
sql>INSERT INTO tmp__pierre5 SELECT id_unique,'Web/Mobile' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='Web/Mobile' GROUP BY id_unique;
3587951 affected rows (3.5s)
sql>INSERT INTO tmp__pierre5 SELECT id_unique,'' as Device,count(*) FROM donnees_lmd_pierre WHERE Device ='' GROUP BY id_unique;
5327969 affected rows (2.8s)
But this is not a good solution is a patch
--
1G6
52 route de bischwiller
67300
Schiltigheim
Société de Services et de Formations en Logiciels Libres
http://1g6.biz
Tél : 06 64 63 70 35
De: "Pierre-Adrien Coustillas" <pcoustillas@1g6.biz>
À: "Communication channel for MonetDB users" <users-list@monetdb.org>
Envoyé: Jeudi 11 Décembre 2014 18:08:41
Objet: Re: speed query with 2 columns group by
I tested on two other server with the same data. I have the same bug
Server 3 : Jan2014-SP2
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP2)
Database: MonetDB v11.17.17 (Jan2014-SP2), 'mapi:monetdb://rd:50000/lemonde'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE tmp__pierre5 AS ( SELECT id_unique, Device, COUNT(*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA;
operation successful (9m 4s)
Server 4 : Oct2014-SP1
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1)
Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://poledev:50000/lemonde'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE tmp__pierre5 AS ( SELECT id_unique, Device, COUNT(*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA;
The query runs from 10 hours
is what you want to dump the table for download ?
Pierre
--
1G6
52 route de bischwiller
67300
Schiltigheim
Société de Services et de Formations en Logiciels Libres
http://1g6.biz
Tél : 06 64 63 70 35
De: "Pierre-Adrien Coustillas" <pcoustillas@1g6.biz>
À: "Communication channel for MonetDB users" <users-list@monetdb.org>
Envoyé: Mardi 9 Décembre 2014 08:16:46
Objet: speed query with 2 columns group by
Hello,
I have an query with the same source data on Jan2014-SP3 version of monetddb and Oct2014-SP1 :
CREATE TABLE tmp__pierre5 AS (
SELECT id_unique, Device, COUNT(*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device
) WITH DATA;
On version Jan2014-SP3 :
Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://lemondedev:50000/lemonde'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE tmp__pierre5 AS ( SELECT id_unique, Device, COUNT(*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA;
operation successful (6m 30s)
On version Oct2014-SP1 :
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1)
Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://monetdb:50000/lemonde'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE tmp__pierre5 AS ( SELECT id_unique, Device, COUNT(*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA;
operation successful (383m 20s)
explain query is different, you want to explain the results of the query ?Pierre
--
1G6
52 route de bischwiller
67300
Schiltigheim
Société de Services et de Formations en Logiciels Libres
http://1g6.biz
Tél : 06 64 63 70 35
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list