I'm trying to optimize a query using EXISTS, but it seems that i'm doing something wrong:

Server: Pentium IV 3Ghz. 512MB ram
SO: Ubuntu server 7.10
DB server: Firebird CS 2.1 RC2 (17755)

The following query:

SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND codpostal not in ( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)

Starting transaction...
Preparing query: SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND codpostal not in ( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)
Prepare time: 00:00:00.
PLAN SORT ((DETCP INDEX (CPUNICOENZONA)))
PLAN (CUBOLABO NATURAL)

Executing...
Done.
39585217 fetches, 0 marks, 40024 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 15363036 index, 2194830 seq.
Delta memory: 39776 bytes.
Execute time: 00:01:46.

Returns 1360 (the correct value)


If I change to:

SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND NOT EXISTS( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)


Executing...
Done.
39585217 fetches, 0 marks, 40024 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 15363036 index, 2194830 seq.
Delta memory: 39776 bytes.
Execute time: 00:01:46.
Prepare time: 00:00:00.
PLAN SORT ((DETCP INDEX (CPUNICOENZONA)))
PLAN (CUBOLABO NATURAL)


Executing...
Done.
4469585 fetches, 0 marks, 39994 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 10 index, 2194830 seq.
Delta memory: -1112 bytes.
Execute time: 00:00:04.

But returns 0.

Thanks in advance.
Eduardo




Yahoo! Encuentros.

Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration