Svein:

Many, many, many thanks!! The last suggestion works really fast!! As
requested:

Plan
PLAN (CA2 INDEX (CANCELACIONES_IDX1))
PLAN JOIN (I CANCELACIONES ORDER CANCELACIONES_IDX1,JOIN (C INDEX
(PK_CUENTAS_CORRIENTES_CLIEN),T INDEX (PK_TIPOS_MOVIMIENTOS_CTA_CT)))
PLAN JOIN (T NATURAL,C INDEX (FK_CUENTAS_CTES_TIPO_MOV))

Adapted Plan
PLAN (CA2 INDEX (CANCELACIONES_IDX1)) PLAN JOIN (I CANCELACIONES ORDER
CANCELACIONES_IDX1,JOIN (C INDEX (PK_CUENTAS_CORRIENTES_CLIEN),T INDEX
(PK_TIPOS_MOVIMIENTOS_CTA_CT))) PLAN JOIN (T NATURAL,C INDEX
(FK_CUENTAS_CTES_TIPO_MOV))

------ Performance info ------
Prepare time = 0ms
Execute time = 313ms
Avg fetch time = 31.30 ms
Current memory = 1,460,172
Max memory = 1,529,872
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 193,936

Have a nice day!!

Svein Erling Tysvær escribió:

> Hi again, Jorge!
>
> --- 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
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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]