Query performance
Hi
I have a simple query that takes about 5 minutes to display is result. The
problem is the order by in the query. It use a temporary file. Whitout it,
it`s a lightning.
Look in attachment file sqlexpl.out, it containts the query with is query
plan.
I don't understand why the optimazer does not take the good index. I've got
a index who must (I think) do the job without a temporary table.
Their is 600 000 rows in my tables.
Take a look in attachment file "dbschema.txt". This is it, the output off
the dbschema for the table.
Thanks.
PS: If you can tell me where i can get good informations about optimazing
query, leave me a message.
--
Alain Martin
INT Syst'mes d'Informations Inc.
begin 600 Sqlexpl.out
M455%4EDZ#0HM+2TM+2T-"E-%3$5#5 D@<G-P+F$P-C,Q7W)O;%]N;R L#0H)
M(')S<"YA,#8S,E]R<W!?=F5R7VYO("P-"@D@<G-P+F$P.#DT7W)S<%]D;6=?
M:6YD("P-"@D@<G-P+F$P-C,S7W)S<%]G=VM?=F%L("P-"@D@<G-P+F$P-C0P
M7W)S<%]T=VM?=F%L("P-"@D@<G-P+F$P.#5B:5]U;6)?=V5G:%]C9" L#0H)
M(')S<"YA,#8S-%]R<W!?=V1T:" L#0H)(')S<"YA,#8S-5]R<W!?9&UT<B L
M#0H)(')S<"YA,#@U8U]U;6-?9&ES=%]C9" L#0H)(')S<"YA,# S.%]O9G1?
M8V0@+ T*"2!R<W N83 U,C-?<')D7V)R:6=H7V-D("P-"@D@<G-P+F$P-3(R
M7W!R9%]D<GE?8V0@+ T*"2!R<W N83 U,C1?<')G7V)W=%]C9" L#0H)(')S
M<"YA,#4R-5]P<G-?8V%L<&5?8V0@+ T*"2!R<W N83 U,C%?<')S7W=E=%]C
M9" L#0H)(')S<"YA,# W,5]O<F1?;F\\@+ T*"2!R<W N83 Q.#9?;V1I7VYO
M("P-"@D@<G-P+F$Y.3 R7W)S<%]N<FD-"D923TT)(')S< T*5TA%4D4-"@DH
M#0H)"7)S<"YA,#8S,5]R;VQ?;F\\@/CT@)R<-"@DI#0I!3D0-"@DH#0H)"7)S
M<"YA,3$X-5]R<W!?;'9?:6YD(#T@,0T*"2D-"D%.1 T*"2@-"@T*"0DH#0H)
M"0ER<W N83 P,SA?;V9T7V-D(#T@,0T*"0DI#0H)*0T*3U)$15(@0ED)(&$P
M-C,Q7W)O;%]N;PT*#0I%<W1I;6%T960@0V]S=#H@,S V-#,-"D5S=&EM871E
M9" C(&]F(%)O=W,@4F5T=7)N960Z(#(P-#@Y#0I496UP;W)A<GD@1FEL97,@
M4F5Q=6ER960@1F]R.B!/<F1E<B!">0T*#0HQ*2!R<W Z($E.1$58(%!!5$@-
M"@T*(" @($9I;'1E<G,Z("AR<W N83 V,S%?<F]L7VYO(#X]("<@)R!!3D0@
M<G-P+F$Q,3@U7W)S<%]L=E]I;F0@/2 Q("D-"@T*(" @("@Q*2!);F1E>"!+
M97ES.B!A,# S.%]O9G1?8V0-"B @(" @(" @3&]W97(@26YD97@@1FEL=&5R
9.B!R<W N83 P,SA?;V9T7V-D(#T@,0T*&B @
`
end
begin 600 Dbshema.txt
M9&)S8VAE;6$@+70@<&1R<W!T("UD(&TR<'!?<W-T#0H-"D1"4T-(14U!(%-C
M:&5M82!5=&EL:71Y(" @(" @($E.1D]234E8+5-13"!697)S:6]N(#<N,3(N
M54,Q#0I#;W!Y<FEG:'0@*$,I($EN9F]R;6EX(%-O9G1W87)E+"!);F,N+" Q
M.3@T+3$Y.34-"E-O9G1W87)E(%-E<FEA;"!.=6UB97(@04%"(THT-#8T-38-
M"GL@5$%"3$4@(FEN=&1B82(N<&1R<W!T(')O=R!S:7IE(#T@,3,Y(&YU;6)E
M<B!O9B!C;VQU;6YS(#T@,S<@:6YD97@@<VEZ92 ](#,Q,B!]#0IC<F5A=&4@
M=&%B;&4@(FEN=&1B82(N<&1R<W!T#0H@("@-"B @("!A,#8S,5]R;VQ?;F\\@
M8VAA<B@Q,"D@;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q
M,#8R,2P-"B @("!A,#8S,E]R<W!?=F5R7VYO('-M86QL:6YT(&YO="!N=6QL
M(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V-3)?,3 V,C(L#0H@(" @83$Q.#5?
M<G-P7VQV7VEN9"!S;6%L;&EN="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D
M8F$B+FXR-C4R7S$P-C(S+ T*(" @(&$P,#(P7W)S<%]P<F1?9'0@9&%T971I
M;64@>65A<B!T;R!S96-O;F0@;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT9&)A
M(BYN,C8U,E\\-"B @("!A,#@Y-%]R<W!?9&UG7VEN9"!S;6%L;&EN="!N;W0@
M;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C(U+ T*(" @(&$P
M-C,S7W)S<%]G=VM?=F%L(&1E8VEM86PH.2PU*2!N;W0@;G5L;"!C;VYS=')A
M:6YT(")I;G1D8F$B+FXR-C4R7S$P-C(V+ T*(" @(&$P-C0P7W)S<%]T=VM?
M=F%L(&1E8VEM86PH.2PU*2!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B
M+FXR-C4R7S$P-C(W+ T*(" @(&$P.#5B:5]U;6)?=V5G:%]C9"!S;6%L;&EN
M="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C(X+ T*
M(" @(&$P-CDU7W)S<%]G=VQ?=F%L(&1E8VEM86PH,3 L-2D@;F]T(&YU;&P@
M8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q,#8R.2P-"B @("!A,#8Y-E]R
M<W!?='=L7VQB(&1E8VEM86PH,3 L-2D@;F]T(&YU;&P@8V]N<W1R86EN=" B
M:6YT9&)A(BYN,C8U,E\\Q,#8S,"P-"B @("!A,#@U8FI?=6UB7W=E9VA?8V0@
M<VUA;&QI;G0@;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q
M,#8S,2P-"B @("!A,#8S-%]R<W!?=V1T:"!D96-I;6%L*#@L-2D@;F]T(&YU
M;&P@8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q,#8S,BP-"B @("!A,#8S
M-5]R<W!?9&UT<B!D96-I;6%L*#@L-2D@;F]T(&YU;&P@8V]N<W1R86EN=" B
M:6YT9&)A(BYN,C8U,E\\Q,#8S,RP-"B @("!A,#@U8U]U;6-?9&ES=%]C9"!S
M;6%L;&EN="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P
M-C,T+ T*(" @(&$P-C,P7W)S<%]P:V=?;F\\@8VAA<B@W*2!N;W0@;G5L;"!C
M;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C,U+ T*(" @(&$P-CDW7W)S
M<%]R:U]I;E]P:R!S;6%L;&EN="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D
M8F$B+FXR-C4R7S$P-C,V+ T*(" @(&$P,#,X7V]F=%]C9"!S;6%L;&EN="!N
M;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C,W+ T*(" @
M(&$P-3(S7W!R9%]B<FEG:%]C9"!C:&%R*#(I(&YO="!N=6QL(&-O;G-T<F%I
M;G0@(FEN=&1B82(N;C(V-3)?,3 V,S@L#0H@(" @83 U,C)?<')D7V1R>5]C
M9"!C:&%R*#(I(&YO="!N=6QL(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V-3)?
M,3 V,SDL#0H@(" @83 U,C1?<')G7V)W=%]C9"!C:&%R*#,I(&YO="!N=6QL
M(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V-3)?,3 V-# L#0H@(" @83 U,C5?
M<')S7V-A;'!E7V-D(&-H87(H,RD@;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT
M9&)A(BYN,C8U,E\\Q,#8T,2P-"B @("!A,#4R,5]P<G-?=V5T7V-D(&-H87(H
M,BD@;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q,#8T,BP-
M"B @("!A,# Y.%]C;W1?8V0@8VAA<B@Q*2!N;W0@;G5L;"!C;VYS=')A:6YT
M(")I;G1D8F$B+FXR-C4R7S$P-C0S+ T*(" @(&$P,C(X7V-T=%]C9"!C:&%R
M*#(I(&YO="!N=6QL(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V-3)?,3 V-#0L
M#0H@(" @83 R,S%?8V]R7W!L=6=?8V0@8VAA<B@Q*2!N;W0@;G5L;"!C;VYS
M=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C0U+ T*(" @(&$P,#DV7VUI;%]C
M9"!S;6%L;&EN="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R
M7S$P-C0V+ T*(" @(&$P,C(Q7VUA8U]C9"!S;6%L;&EN="!N;W0@;G5L;"!C
M;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C0W+ T*(" @(&$P,#<Q7V]R
M9%]N;R!I;G1E9V5R(&YO="!N=6QL(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V
M-3)?,3 V-#@L#0H@(" @83DY,#=?;W)C7VYR:5]R968@:6YT96=E<B!N;W0@
M;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C0Y+ T*(" @(&$P
M,#<Y7V]R:5]N;R!S;6%L;&EN="!N;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D
M8F$B+FXR-C4R7S$P-C4P+ T*(" @(&$P,3@V7V]D:5]N;R!S;6%L;&EN="!N
M;W0@;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C4Q+ T*(" @
M(&$P-#4Q7W5S<E]I9%]C9"!C:&%R*#@I+ T*(" @(&$P-C@Y7W)S<%]C;&U?
M8VYT('-M86QL:6YT(&YO="!N=6QL(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V
M-3)?,3 V-3(L#0H@(" @83$R-3%?<G-P7V-U<%]C9"!S;6%L;&EN="!N;W0@
M;G5L;"!C;VYS=')A:6YT(")I;G1D8F$B+FXR-C4R7S$P-C4S+ T*(" @(&$Y
M.3 Q7W)S<%]T<U]N;R!I;G1E9V5R(&YO="!N=6QL(&-O;G-T<F%I;G0@(FEN
M=&1B82(N;C(V-3)?,3 V-30L#0H@(" @83DY,#)?<G-P7VYR:2!S97)I86P@
M;F]T(&YU;&P@8V]N<W1R86EN=" B:6YT9&)A(BYN,C8U,E\\Q,#8U-2P-"B @
M("!A.3DP.%]R<W!?9'1T(&1A=&5T:6UE('EE87(@=&\\@<V5C;VYD(&YO="!N
M=6QL(&-O;G-T<F%I;G0@(FEN=&1B82(N;C(V-3)?,3 V#0H@(" @=6YI<75E
M("AA.3DP,E]R<W!?;G)I*2!C;VYS=')A:6YT(")I;G1D8F$B+G)S<%]U;FEQ
M=64-"B @*3L-"G)E=F]K92!A;&P@;VX@(FEN=&1B82(N<&1R<W!T(&9R;VT@
M(G!U8FQI8R([#0H-@