Re: inner join very slow

newer
Inserting arrays with SciQL

older
COMMIT: transaction is aborted...

Stefan Manegold

1 Dec 2014 1 Dec '14

7:28 p.m.

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

Show replies by date

Pierre-Adrien Coustillas

1 Dec 1 Dec

10:02 p.m.

New subject: inner join very slow

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

Stefan Manegold

10:37 p.m.

New subject: 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" 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

Pierre-Adrien Coustillas

8 Dec 8 Dec

10:17 p.m.

New subject: 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" Stefan.Manegold@cwi.nl À: "Pierre-Adrien Coustillas" pcoustillas@1g6.biz Cc: "Communication channel for MonetDB users" users-list@monetdb.org 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" 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 > > -- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

3439

Age (days ago)

3446

Last active (days ago)

List overview

Download

3 comments

2 participants

tags (0)

participants (2)

Pierre-Adrien Coustillas
Stefan Manegold