Re: inner join very slow

1 Dec 2014

      Hello, 

join attributes type are : 

SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres 
SUB ON SRC.siteniveau1(VARCHAR(10)) =SUB.site( VARCHAR(10)) AND 
SRC.chapitreniveau1(VARCHAR(15)) =SUB.idchapitre1( VARCHAR(100)) ; 

I do not know how can return the join 

sql>select count ,sum(cnt) from (select count as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; 
+--------+------------+ 
| L2 | L3 | 
+========+============+ 
| 121382 | 1061964254 | 
+--------+------------+ 
1 tuple (54.1s) 

sql>select count , sum(cnt) from (select count as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; 
+--------+---------+ 
| L2 | L3 | 
+========+=========+ 
| 124307 | 1331867 | 
+--------+---------+ 
1 tuple (144.736ms) 

how to interpret this result ? 

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 

----- Mail original -----

De: "Stefan Manegold" <Stefan.Manegold@cwi.nl> 
À: "Communication channel for MonetDB users" <users-list@monetdb.org>, "Pierre-Adrien Coustillas" <pcoustillas@1g6.biz> 
Envoyé: Lundi 1 Décembre 2014 20:28:48 
Objet: Re: inner join very slow 

Of what type(s) are the join attributes? 

How big is the expected join result? 

If you don't know, 
select count(*), sum(cnt) from (select count(*) as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; 
select count(*), sum(cnt) from (select count(*) as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; 
might help to give some estimation / idea ... 

Best, 
Stefan 

On December 1, 2014 6:16:11 PM CET, Pierre-Adrien Coustillas <pcoustillas@1g6.biz> wrote:
...
Hello,
With this query : 
SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres 
SUB ON SRC.siteniveau1=SUB.site AND 
SRC.chapitreniveau1=SUB.idchapitre1;
SELECT count(*) FROM tmp__pierre2; 
+------------+ 
| L1 | 
+============+ 
| 1 061 964 254 |
SELECT count(*) FROM ref__at_lm_chapitres; 
+---------+ 
| L1 | 
+=========+ 
| 1 331 867 |
after several hours of running the query is still not finished and I 
stopped
how to find out where the problem?
thanks
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