--- In firebird-support@yahoogroups.com, Jorge Andrés Brugger wrote:
> Svein:
>
> Thanks for your quick answer. Sorry for posting the query "as is", I
> use to do it in Access and copy/adapt the SQL later in FB.
>
> Some facts about that queries:
> 1) If I create a local Access query
> "vw_importes_creditos_imputados", using the linked FB Cancelaciones
> table, and use that "view" from the slow query ... it´s really fast!
> (That's mainly because I wrote the original message, how can an
> Access query with FB linked tables being fast when FB view doesn´t)

Well, I don't know enough about views to give a proper answer, I can
just guess. Maybe Access just calculate the result of the view once
and use that thereafter, whereas Firebird does the same thing for
every potential row - thereby doing the same thing lots of times.

In general, Firebird and Access are very different - with one user and
small tables, Access will be superior to Firebird regarding speed.
With many concurrent users and tables with millions of rows, it is
completely different.

> 2) Importe belongs to Cuentas_Corrientes_Clientes
> 3) Importe and Importe_Imputado cant be negatives
> 4) About your sugestion "WHERE T.debito_credito = 'C' AND C.IMPORTE
> AND (C.IMPORTE > I.Importe_Imputado OR I.Importe_Imputado IS NULL)",
> Importe_Imputado cant be null, because the use of "coalesce"
> previously (there are many nulls because the right join, but they
> are zeroes after coalesce). C.Importe cant be null or zero.

The right join was the reason that I wrote IS NULL, and I didn't use
coalesce.

> 5) "Select * from vw_importes_creditos_imputados" is pretty fast
> (7980 records):

When executed once, joining to this view makes it completely
different!

> I´m convinced that it should be a faster query, because Access do it
> fast, or, there is a bug in FB 1.5.3 optimizer?

Following Radus mail, I have to suggestions for you to try: First, the
obvious one:

SELECT C.SUCURSAL_MOVIMIENTO, C.NUMERO_MOVIMIENTO,
C.FECHA_MOVIMIENTO, C.CODIGO_CLIENTE,
(C.IMPORTE - coalesce((SELECT Sum(CA.IMPORTE_CANCELADO)
FROM CANCELACIONES CA
WHERE CA.NUMERO_MOVIMIENTO = C.NUMERO_MOVIMIENTO
AND CA.SUCURSAL_MOVIMIENTO = C.SUCURSAL_MOVIMIENTO), 0)) AS
Importe_No_Imputado,
C.DESCUENTO_POR_PLANILLA
FROM CUENTAS_CORRIENTES_CLIENTES C
JOIN TIPOS_MOVIMIENTOS_CTA_CTE T ON
C.CODIGO_TIPO_MOVIMIENTO_CTA_CTE = T.CODIGO
WHERE T.debito_credito = 'C' AND
(NOT EXISTS(SELECT(*) FROM CANCELACIONES CA2
WHERE CA2.NUMERO_MOVIMIENTO = C.NUMERO_MOVIMIENTO
AND CA2.SUCURSAL_MOVIMIENTO = C.SUCURSAL_MOVIMIENTO) OR
(C.IMPORTE > (SELECT Sum(CA3.IMPORTE_CANCELADO)
FROM CANCELACIONES CA3
WHERE CA3.NUMERO_MOVIMIENTO = C.NUMERO_MOVIMIENTO
AND CA3.SUCURSAL_MOVIMIENTO = C.SUCURSAL_MOVIMIENTO))

Then, you could try to force CANCELACINES to be the first table in the
plan (this will also ascertain that it is not executed too many times)
. To do this, you cannot use any outer join (left or right) and have
to use UNION to get both the cases where there is a match to
vw_importes_creditos_imputados and where there is no such match. And
there is no need to use COALESCE. The +0 are there to strongly hint
the optimizer that this table should be put first in the plan:

SELECT C.SUCURSAL_MOVIMIENTO, C.NUMERO_MOVIMIENTO,
C.FECHA_MOVIMIENTO, C.CODIGO_CLIENTE,
(T.IMPORTE - I.Importe_Imputado AS Importe_No_Imputado,
C.DESCUENTO_POR_PLANILLA
FROM CUENTAS_CORRIENTES_CLIENTES C
JOIN TIPOS_MOVIMIENTOS_CTA_CTE T ON
c.CODIGO_TIPO_MOVIMIENTO_CTA_CTE = T.CODIGO
JOIN vw_importes_creditos_imputados I
ON I.NUMERO_MOVIMIENTO+0 = C.NUMERO_MOVIMIENTO
AND I.SUCURSAL_MOVIMIENTO+0 = C.SUCURSAL_MOVIMIENTO
WHERE T.debito_credito = 'C' AND
T.IMPORTE > I.Importe_Imputado+0
UNION
SELECT C.SUCURSAL_MOVIMIENTO, C.NUMERO_MOVIMIENTO,
C.FECHA_MOVIMIENTO, C.CODIGO_CLIENTE,
T.IMPORTE AS Importe_No_Imputado,
C.DESCUENTO_POR_PLANILLA
FROM CUENTAS_CORRIENTES_CLIENTES C
JOIN TIPOS_MOVIMIENTOS_CTA_CTE T ON
c.CODIGO_TIPO_MOVIMIENTO_CTA_CTE = T.CODIGO
WHERE T.debito_credito = 'C' AND
NOT EXISTS(
SELECT(*) FROM CANCELACIONES CA2
WHERE CA2.NUMERO_MOVIMIENTO = C.NUMERO_MOVIMIENTO
AND CA2.SUCURSAL_MOVIMIENTO = C.SUCURSAL_MOVIMIENTO)

Tell us the plans and what the results are with these queries, I have
no idea how they will perform (I guess the latter should be faster)!

HTH,
Set