Re: analyse performance slow query

16 Jan 2015


      Try using plan in front of your query to see the sql explain.

Regards,

Brian Hood
On Jan 16, 2015 2:59 PM, "Pierre-Adrien Coustillas" 
wrote:
...
Hi,
I try to analyze the slowness of a query with EXPLAIN and stethoscope
The query is :
SELECT count(*) FROM (
    SELECT
        id_unique,
        siteniveau1,
        siteniveau2,
        '0000-00-00',
        0,
        0,
        MAX(debutvisite),
        0,
        0
    FROM tmp__at__lm__visites_agregees_id_unique
    GROUP BY id_unique,siteniveau1,siteniveau2
    ) tbl ;
the beginning of explain is  (there is 272 tuples  )
+============================================================================================================================================================================================+
| function user.s2_5{autoCommit=true}(A0:str,A1:bte,A2:bte,A3:bte,A4:bte):
void;
                                         |
| barrier X_429 := language.dataflow();
|
|     X_7 := sql.mvc();
|
|     X_55:bat[:oid,:oid]  := sql.tid(X_7,"sys","tmp__at__lm__visites_
agregees_id_unique",0,8);
                                             |
|     X_70:bat[:oid,:int]  := sql.bind(X_7,"sys","tmp__at__lm__visites_
agregees_id_unique","id_unique",0,0,8);
                                            |
|     (X_78:bat[:oid,:oid] ,X_79:bat[:oid,:int] ) := sql.bind(X_7,"sys","
tmp__at__lm__visites_agregees_id_unique","id_unique",2,0,8);
                                           |
|     X_161 := sql.delta(X_70,X_78,X_79);
|
|     X_169 := algebra.leftfetchjoin(X_55,X_161);
|
|     X_96:bat[:oid,:int]  := sql.bind(X_7,"sys","tmp__at__lm__visites_
agregees_id_unique","siteniveau1",0,0,8);
                                            |
|     (X_111:bat[:oid,:oid] ,X_112:bat[:oid,:int] ) := sql.bind(X_7,"sys
","tmp__at__lm__visites_agregees_id_unique","siteniveau1",2,0,8);
                                           |
|     X_184 := sql.delta(X_96,X_111,X_112);
|
|     X_193 := algebra.leftfetchjoin(X_55,X_184);
|
|     X_129:bat[:oid,:int]  := sql.bind(X_7,"sys","tmp__at__lm__visites_
agregees_id_unique","siteniveau2",0,0,8);
                                           |
|     (X_144:bat[:oid,:oid] ,X_145:bat[:oid,:int] ) := sql.bind(X_7,"sys
","tmp__at__lm__visites_agregees_id_unique","siteniveau2",2,0,8);
                                           |
|     X_201 := sql.delta(X_129,X_144,X_145);
|
|     X_209 := algebra.leftfetchjoin(X_55,X_201);
|
|     (X_224,X_225,X_226) := group.subgroup(X_209);
|
|     (X_248,X_249,X_250) := group.subgroup(X_193,X_224);
|
|     (X_272,X_273,X_274) := group.subgroupdone(X_169,X_248);
|
|     X_275 := algebra.leftfetchjoin(X_273,X_169);
|
|     X_304 := algebra.leftfetchjoin(X_273,X_248);
|
|     X_331 := algebra.leftfetchjoin(X_304,X_249);
group.subgroupdone(X_169,X_248);       set 10 minutes to run.
Why ?
how to understand this function ?
Thank
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