Hello,

I was querying a database and I excuted the next:

SELECT i.CLAVEUNICA
FROM INVENTARIOVENTA i
WHERE i.CLAVEUNICA NOT IN (
SELECT DISTINCT d.CLAVEUNICA
FROM DOCUMENTOSALIDA doc
JOIN DETALLECONTRATOSALIDA d ON DOC.IDDOCUMENTOSALIDA=D.IDDOCUMENTOSALIDA
WHERE doc.TIPOSALIDA <> 'T'
)
AND (i.SALDOINICIAL + i.ENTRADAS - i.SALIDAS)= 0

Table INVENTARIOVENTA has 2k records. DOCUMENTOSALIDA and DETALLECONTRATOSALIDA are almost the same.

This query takes 1 minute to execute.

I think they are small tables yet, so the default plan is not correct.

This is the output from FlameRobin:

Starting transaction...
Preparing query: SELECT i.CLAVEUNICA
FROM INVENTARIOVENTA i
WHERE i.CLAVEUNICA NOT IN (
SELECT DISTINCT d.CLAVEUNICA
FROM DOCUMENTOSALIDA doc
JOIN DETALLECONTRATOSALIDA d ON DOC.IDDOCUMENTOSALIDA=D.IDDOCUMENTOSALIDA
WHERE doc.TIPOSALIDA <> 'T'
)
AND (i.SALDOINICIAL + i.ENTRADAS - i.SALIDAS)= 0

Prepare time: 00:00:01.
PLAN SORT (JOIN (DOC NATURAL, D INDEX (FK_DETALLECONTRATOSALIDA_2)))
PLAN (I NATURAL)


Executing...
Done.
40861804 fetches, 4 marks, 263 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 6813291 index, 6528916 seq.
Delta memory: 52548 bytes.
Execute time: 00:01:03.



I've solved the slowness using a NOT EXISTS clause, but I wanted to tell you experts in order to analize the "why".

Thanks and regards.



_______________________________
I'm free, I use GNU/Linux.
Close your Windows, Open your mind.


¡Todo sobre Amor y Sexo!
La guía completa para tu vida en Mujer de Hoy.
http://mx.mujer.yahoo.com/