Re: inner join very slow

thank you for your explanation. it was perfect to fix my problem -- 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" À: "Pierre-Adrien Coustillas" Cc: "Communication channel for MonetDB users" Envoyé: Lundi 1 Décembre 2014 23:37:59 Objet: Re: inner join very slow Hi, assuming uniformly distributed data, i.e., each of the 121382 distinct (siteniveau1, chapitreniveau1) combinations occurs on average 1061964254/121382 ~= 8748.9 times in tmp__pierre2, and each of the 124307 distinct (site, idchapitre1)combinations occurs on average 1331867/124307 ~= 10.7 times in ref__at_lm_chapitres, and assuming that each of the 121382 distinct (siteniveau1, chapitreniveau1) combinations from tmp__pierre2 finds a match in ref__at_lm_chapitres, the join result would by about 1061964254 * 10.7 ~= 113630175178 tuples, i.e., about 10 times larger than tmp__pierre2 . Having said that, could you share some more details and information: - hardware configuration of your system (CPU, #cores, memory size, disk system) - MonetDB version - operating system and what kind of activities do you observe while this query is running? How big is your mserver5 process? How high is the CPU load? How high is the I/O load? Is the system swapping? etc. Best, Stefan and assuming that all 121382 distinct (siteniveau1, chapitreniveau1) combinations from ----- Original Message -----

Hello,

join attributes type are : site, idchapitre1 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" À: "Communication channel for MonetDB users" , "Pierre-Adrien Coustillas" 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 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

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |