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)
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 > 0
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.
5) "Select * from vw_importes_creditos_imputados" is pretty fast (7980
records):
Plan
PLAN (VW_IMPORTES_CREDITOS_IMPUTADOS CANCELACIONES ORDER CANCELACIONES_IDX1)

Adapted Plan
PLAN (VW_IMPORTES_CREDITOS_IMPUTADOS CANCELACIONES ORDER CANCELACIONES_IDX1)

------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 1.36 ms
Current memory = 801,172
Max memory = 876,952
Memory buffers = 2,048
Reads from disk to cache = 223
Writes from cache to disk = 2
Fetches from cache = 37,257

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?

Regards!

Svein Erling Tysvær escribió:

> 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
>>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>

--
Jorge Andrés Brugger
Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446-4444 int. 103
Correo electrónico: jbrugger@...
Website: www.dasu.com.ar



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