Subject | Re: [firebird-support] Re: How to plan a query |
---|---|

Author | Kjell Rilbe |

Post date | 2008-12-05T08:45:31Z |

I'm not an expert, but I had a little look at your SQL. A couple of

questions that might help, but might also make no difference or might

even mess things up :-) Careful!

danyschaer wrote:

joins, but then use a where clause that checks the joined table for

non-null values. It should produce the same results with inner joins,

because non-matching records won't be included anyway due to the where

clause. I think inner joins are easier to execute fast.

Also, you have the selection of max MOVI.FECH inside a join condition.

Might be better to put it in the outermost where clause. Like this

(UNTESTED):

select "PROC"."PROC",

"PROC"."CARP",

"PROC"."ACTO",

"PROC"."DEMA",

"PROC"."OBSE",

"TPRO"."DSCR" "D_TPRO",

m."FECH",

m."HORA",

m."DSCR" "D_MOVI"

from "PROC"

left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"

left outer join "MOVI" m

on "PROC"."PROC" = m."PROC"

and m."TIPO" = '1'

and m."HECH" = 'P'

where "PROC"."PROC" in (

select "PROC"

from "PROC"

left outer join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"

left outer join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"

where ("ZONA"."ORDE" between '000000550' and '000000550')

)

and (m."PROC" is null

or m."FECH" = (

select max(msub."FECH")

from "MOVI" msub

where msub."PROC" = m."PROC"

and msub."TIPO" = '1'

and msub."HECH" = 'P'

))

order by lower("PROC"."ACTO"),

"PROC"."PROC"

Furthermore, why the subquery for PROC.PROC in...?

Can't that be achieved by inner joins at the outmost level? Like this

(UNTESTED):

select "PROC"."PROC",

"PROC"."CARP",

"PROC"."ACTO",

"PROC"."DEMA",

"PROC"."OBSE",

"TPRO"."DSCR" "D_TPRO",

m."FECH",

m."HORA",

m."DSCR" "D_MOVI"

from "PROC"

left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"

left outer join "MOVI" m

on "PROC"."PROC" = m."PROC"

and m."TIPO" = '1'

and m."HECH" = 'P'

inner join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"

inner join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"

where "ZONA"."ORDE" between '000000550' and '000000550'

and (m."PROC" is null

or m."FECH" = (

select max(msub."FECH")

from "MOVI" msub

where msub."PROC" = m."PROC"

and msub."TIPO" = '1'

and msub."HECH" = 'P'

))

order by lower("PROC"."ACTO"),

"PROC"."PROC"

I might be missing vital aspects here, but I'm sure you'll spot that if

that's the case...

Good luck!

Kjell

--

--------------------------------------

Kjell Rilbe

DataDIA AB

E-post: kjell@...

Telefon: 08-761 06 55

Mobil: 0733-44 24 64

questions that might help, but might also make no difference or might

even mess things up :-) Careful!

danyschaer wrote:

> In the FIRST one, I run a query for getting PROC rows filtering withIn the outmost where clause's subquery for ZONA.ORDE you use left outer

> values from other tables. Super-fast, less than one second.

>

> In the NEXT one, I run a query for getting the same PROC rows, but

> adding the values from MOVI (itÂ´s expected that MOVI can return more

> than one row for each PROC row, when there are more than one MOVI

> with same FECH). Very slow, near than 6 minutes.

>

> FIRST:

>

> Preparing query:

> select "PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC".

> "OBSE","TPRO"."DSCR" as "D_TPRO" from "PROC"

> left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO"

> where (("PROC"."PROC" in (select "PROC" from "PROC" left outer

> join "OJUD" on "PROC"."OJUD"="OJUD"."OJUD" left outer join "ZONA"

> on "OJUD"."ZONA"="ZONA"."ZONA" where ("ZONA"."ORDE"

> between '000000550' and '000000550')))) order by lower

> ("PROC"."ACTO"),"PROC"."PROC"

> Prepare time: 00:00:00.

> Field #01: PROC.PROC Alias:PROC Type:STRING(10)

> Field #02: PROC.CARP Alias:CARP Type:STRING(18)

> Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)

> Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)

> Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)

> Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)

> PLAN JOIN (JOIN (PROC INDEX (PROC_PK), OJUD INDEX (OJUD_PK)), ZONA

> INDEX (ZONA_PK))

> PLAN SORT (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)))

>

> Executing...

> Done.

> 221817 fetches, 0 marks, 702 reads, 0 writes.

> 0 inserts, 0 updates, 0 deletes, 41664 index, 11427 seq.

> Delta memory: -24680 bytes.

> Execute time: 00:00:01.

> Script execution finished.

>

> NEXT:

>

> Preparing query:

> select "PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC".

> "OBSE","TPRO"."DSCR" as "D_TPRO",m3."FECH",m3."HORA",m3."DSCR"

> as "D_MOVI" from "PROC"

> left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO"

> left outer join (select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"

> from "MOVI" m1 where m1."FECH"=(select max(m2."FECH") from "MOVI" m2

> where m1."PROC"=m2."PROC" and m2."TIPO" = '1' and m2."HECH" = 'P')

> and m1."TIPO" = '1' and m1."HECH" = 'P') m3 on "PROC"."PROC"=m3."PROC"

> where (("PROC"."PROC" in (select "PROC" from "PROC" left outer

> join "OJUD" on "PROC"."OJUD"="OJUD"."OJUD" left outer join "ZONA"

> on "OJUD"."ZONA"="ZONA"."ZONA" where ("ZONA"."ORDE"

> between '000000550' and '000000550')))) order by lower

> ("PROC"."ACTO"),"PROC"."PROC"

> Prepare time: 00:00:00.

> Field #01: PROC.PROC Alias:PROC Type:STRING(10)

> Field #02: PROC.CARP Alias:CARP Type:STRING(18)

> Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)

> Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)

> Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)

> Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)

> Field #07: MOVI.FECH Alias:FECH Type:STRING(8)

> Field #08: MOVI.HORA Alias:HORA Type:STRING(4)

> Field #09: . Alias:D_MOVI Type:STRING(200)

> PLAN (M3 M2 INDEX (MOVI_PROC))

> PLAN JOIN (JOIN (PROC INDEX (PROC_PK), OJUD INDEX (OJUD_PK)), ZONA

> INDEX (ZONA_PK))

> PLAN JOIN (SORT (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK))), M3 M1

> INDEX (MOVI_PROC))

joins, but then use a where clause that checks the joined table for

non-null values. It should produce the same results with inner joins,

because non-matching records won't be included anyway due to the where

clause. I think inner joins are easier to execute fast.

Also, you have the selection of max MOVI.FECH inside a join condition.

Might be better to put it in the outermost where clause. Like this

(UNTESTED):

select "PROC"."PROC",

"PROC"."CARP",

"PROC"."ACTO",

"PROC"."DEMA",

"PROC"."OBSE",

"TPRO"."DSCR" "D_TPRO",

m."FECH",

m."HORA",

m."DSCR" "D_MOVI"

from "PROC"

left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"

left outer join "MOVI" m

on "PROC"."PROC" = m."PROC"

and m."TIPO" = '1'

and m."HECH" = 'P'

where "PROC"."PROC" in (

select "PROC"

from "PROC"

left outer join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"

left outer join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"

where ("ZONA"."ORDE" between '000000550' and '000000550')

)

and (m."PROC" is null

or m."FECH" = (

select max(msub."FECH")

from "MOVI" msub

where msub."PROC" = m."PROC"

and msub."TIPO" = '1'

and msub."HECH" = 'P'

))

order by lower("PROC"."ACTO"),

"PROC"."PROC"

Furthermore, why the subquery for PROC.PROC in...?

Can't that be achieved by inner joins at the outmost level? Like this

(UNTESTED):

select "PROC"."PROC",

"PROC"."CARP",

"PROC"."ACTO",

"PROC"."DEMA",

"PROC"."OBSE",

"TPRO"."DSCR" "D_TPRO",

m."FECH",

m."HORA",

m."DSCR" "D_MOVI"

from "PROC"

left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"

left outer join "MOVI" m

on "PROC"."PROC" = m."PROC"

and m."TIPO" = '1'

and m."HECH" = 'P'

inner join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"

inner join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"

where "ZONA"."ORDE" between '000000550' and '000000550'

and (m."PROC" is null

or m."FECH" = (

select max(msub."FECH")

from "MOVI" msub

where msub."PROC" = m."PROC"

and msub."TIPO" = '1'

and msub."HECH" = 'P'

))

order by lower("PROC"."ACTO"),

"PROC"."PROC"

I might be missing vital aspects here, but I'm sure you'll spot that if

that's the case...

Good luck!

Kjell

--

--------------------------------------

Kjell Rilbe

DataDIA AB

E-post: kjell@...

Telefon: 08-761 06 55

Mobil: 0733-44 24 64