Hmm, I'm not yet convinced that the view is fast, a view that is fast
by itself, may become unbearably slow when executed a lot of times due
to being joined to other tables. And aggregations like SUM is pretty
often the cause when something is too slow.

I'll destroy your query to get it to something more easily understood
by someone more used to aliases and left joins. Let's start with this
(guessing that importe belongs to TIPOS_MOVIMIENTOS_CTA_CTE):

SELECT C.SUCURSAL_MOVIMIENTO, C.NUMERO_MOVIMIENTO,
C.FECHA_MOVIMIENTO, C.CODIGO_CLIENTE,
(T.IMPORTE - coalesce(I.Importe_Imputado,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
LEFT JOIN vw_importes_creditos_imputados I
ON I.NUMERO_MOVIMIENTO = C.NUMERO_MOVIMIENTO
AND I.SUCURSAL_MOVIMIENTO = C.SUCURSAL_MOVIMIENTO
WHERE T.debito_credito = 'C' AND
(T.IMPORTE - coalesce(I.Importe_Imputado,0))>0

So far, we've only made the query more easily readable and specified
which table IMPORTE belongs to (switch it with C if my guess is wrong).

I'll guess a bit further, IMPORTE and Importe_Imputado is never negative.

If so, you can change your WHERE clause:

WHERE T.debito_credito = 'C' AND
T.IMPORTE > 0 AND
(T.IMPORTE > I.Importe_Imputado OR I.Importe_Imputado IS NULL)

I doubt that changing this will help you much. It does give the
optimizer a chance to use an index on IMPORTE, but I think this will
help only if there are many records where IMPORTE IS NULL. I hope
CANCELACIONES.SUCURSAL_MOVIMIENTO and CANCELACIONES.NUMERO_MOVIMIENTO
are indexed and that the index can be used, but I must admit I've
limited experience with views and how indexes work.

I don't know how many records each table has, but my guess is that it
is your view that is the prime source of your speed problem and that
it will be difficult to make this query quick. How many records are
there in CANCELACIONES and with
TIPOS_MOVIMIENTOS_CTA_CTE.debito_credito = 'C' and how long time does
the entire query take?

Set

--- In firebird-support@yahoogroups.com, Jorge Andrés Brugger wrote:
> Hi all.
>
> I've the following query:
>
> SELECT CUENTAS_CORRIENTES_CLIENTES.SUCURSAL_MOVIMIENTO,
> CUENTAS_CORRIENTES_CLIENTES.NUMERO_MOVIMIENTO,
> CUENTAS_CORRIENTES_CLIENTES.FECHA_MOVIMIENTO,
> CUENTAS_CORRIENTES_CLIENTES.CODIGO_CLIENTE, (IMPORTE -
> coalesce(Importe_Imputado,0)) AS Importe_No_Imputado,
> CUENTAS_CORRIENTES_CLIENTES.DESCUENTO_POR_PLANILLA
> FROM vw_importes_creditos_imputados right JOIN
> (CUENTAS_CORRIENTES_CLIENTES INNER JOIN TIPOS_MOVIMIENTOS_CTA_CTE ON
> CUENTAS_CORRIENTES_CLIENTES.CODIGO_TIPO_MOVIMIENTO_CTA_CTE =
> TIPOS_MOVIMIENTOS_CTA_CTE.CODIGO) ON
> (vw_importes_creditos_imputados.NUMERO_MOVIMIENTO =
> CUENTAS_CORRIENTES_CLIENTES.NUMERO_MOVIMIENTO) AND
> (vw_importes_creditos_imputados.SUCURSAL_MOVIMIENTO =
> CUENTAS_CORRIENTES_CLIENTES.SUCURSAL_MOVIMIENTO)
> WHERE TIPOS_MOVIMIENTOS_CTA_CTE.debito_credito = 'C' AND (IMPORTE -
> coalesce(Importe_Imputado,0))>0
>
> plan is: PLAN JOIN (JOIN (TIPOS_MOVIMIENTOS_CTA_CTE
> NATURAL,CUENTAS_CORRIENTES_CLIENTES INDEX
> (FK_CUENTAS_CTES_TIPO_MOV)),VW_IMPORTES_CREDITOS_IMPUTADOS
CANCELACIONES
> ORDER CANCELACIONES_IDX1)
>
> The referenced view is defined as:
> SELECT CANCELACIONES.SUCURSAL_MOVIMIENTO,
> CANCELACIONES.NUMERO_MOVIMIENTO,
> Sum(CANCELACIONES.IMPORTE_CANCELADO) AS Importe_Imputado
> FROM CANCELACIONES
> GROUP BY CANCELACIONES.SUCURSAL_MOVIMIENTO,
> CANCELACIONES.NUMERO_MOVIMIENTO
>
> Plan is PLAN (CANCELACIONES ORDER CANCELACIONES_IDX1)
>
> Well ... the first query is imposible to use, because it is so slow.
> The referenced view is fast. How can I improve that query do get
> results in "decent" time? (I´m using FB 1.5.3)
> Please, if you need more info, just tell me.
>
> Thanks!!
>
> Jorge Brugger