| Subject | Re: Optimizing query |
|---|---|
| Author | Svein Erling |
| Post date | 2010-07-09T22:57:26Z |
--- In firebird-support@yahoogroups.com, "Ismael L. Donis GarcĂa" <ismael@...> wrote:
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 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
This should give a plan accessing 'a' first and then 'b' and that cannot be changed when using LEFT JOIN. Any optimization will then only be to prevent the use of nonselective indexes, in your case that would typically be if a considerable amount of records match the fecha or debe criteria and the optimizer chose to use indexes for these fields (it could also be relating to the other criteria, but it is less likely that the optimizer makes a bad choice when you do an equality comparison).
HTH,
Set
>Don't know whether it helps the response time or not, but I think you can make your query a lot simpler for the human brain to understand by changing to:
> 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
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 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
This should give a plan accessing 'a' first and then 'b' and that cannot be changed when using LEFT JOIN. Any optimization will then only be to prevent the use of nonselective indexes, in your case that would typically be if a considerable amount of records match the fecha or debe criteria and the optimizer chose to use indexes for these fields (it could also be relating to the other criteria, but it is less likely that the optimizer makes a bad choice when you do an equality comparison).
HTH,
Set