You lessened thanks with this variant from 1.11 minutes to 55.901 seconds

I use firebird 2.1.3

I use PLAN SORT (JOIN (A INDEX (FK_PAFACPAG_EFI), B INDEX (FK_PAFACPAG_EFI)))

FK_PAFACPAG_EFI = Field(ENTIDAD, FECHA, IDEDOC)

I would like to decrease time more, but really not himself if I will be able to.

Thanks to all
=========
|| ISMAEL ||
=========

----- Original Message -----
From: Woody
To: firebird-support@yahoogroups.com
Sent: Friday, July 09, 2010 4:55 PM
Subject: Re: [firebird-support] Optimizing query



From: "Ismael L. Donis García" <ismael@...>
Sent: Friday, July 09, 2010 3:05 PM
To: <firebird-support@yahoogroups.com>
Subject: [firebird-support] Optimizing query

> I have the following query, but it takes long.
>
> Does any way exist of optimizing response time?
>
> select a.idedoc, a.documento, a.docemp, a.fecha as fec, a.debe as ded,
> a.antdebe, a.nota, b.factura, b.fecha, b.debe
> from pafacpag a left join (select c.idedoc as ide, c.factura, c.fecha,
> c.debe from pafacpag c where (((c.entidad)='2578')
> and ((c.debe)>0) and ((c.clave)='PS'))) as b on a.idedoc = b.ide where
> (((a.fecha)<'2009/12/31') and
> ((a.debe)>0) and ((a.entidad)='2578') and ((a.factura)='PA') and
> ((a.clave)='PO')) order by a.docemp, a.fecha, b.fecha
>

You are joining a table to itself. Even so, I don't think there is a need
for the inner select subquery.

Try this: (not tested)

select a.idedoc, a.documento, a.docemp, a.fecha as fec, a.debe as ded,
a.antdeb, a.nota, b.factura, b.fecha, b.debe
from pafacpag a

left join pafacpag b on (a.idedoc = b.idedoc) and (b.entidad = '2578') and
(b.debe > 0) and (b.clave = 'PS')

where (((a.fecha) < '2009/12/31') and ((a.debe) > 0) and ((a.entidad) =
'2578') and ((a.factura) = 'PA') and ((a.clave) = 'PO'))

order by a.docemp, a.fecha, b.fecha

HTH
Woody (TMW)





[Non-text portions of this message have been removed]