Hi,

I'm using FB 2.5.5 CS under Windows 8.1. Selectivity of indices has been updated before asking for help :)

I'm trying to optimize the following query :

UPDATE TVERSIONS v1 SET ... WHERE RD_BACKUPID=:BackupID

             AND NOT EXISTS (SELECT 1 FROM TVERSIONS v2 WHERE v2.FILEID=v1.FILEID AND DDATE IS NULL)

             AND NOT EXISTS (SELECT 1 FROM TVERSIONS v3 WHERE v3.FILEID=v1.FILEID AND v3.DDATE >= :DelTreshold)

Plan

PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))

PLAN (V2 INDEX (RDB$FOREIGN10))

PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))

PLAN (V3 INDEX (RDB$FOREIGN10))

PLAN (V1 INDEX (TVERSIONS_ID_RDBACKUPID))

Adapted Plan

PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))

PLAN (V2 INDEX (INTEG_18))

PLAN (TBACKUPVERSIONS INDEX (PK_BACKUPVERSIONS))

PLAN (V3 INDEX (INTEG_18))

PLAN (V1 INDEX (TVERSIONS_ID_RDBACKUPID))

35 record(s) was(were) updated in TVERSIONS

------ Performance info ------

Prepare time = 0ms

Execute time = 14s 875ms

Current memory = 2 907 024

Max memory = 3 022 112

Memory buffers = 90

Reads from disk to cache = 1 012 731

Writes from cache to disk = 33

Fetches from cache = 8 677 201

There is 21 895 records in the TVERSIONS table satisfying the condition (RD_BACKUPID=:BackupID), but IBExpert shows 1 251 930 indexed reads from this table while it contains only 378 595 records... 

* RD_BACKUPID is indexed,

* FILEID is a foreign key so its also indexed,

* DDATE is a computed field : DDATE = (SELECT TBACKUPVERSIONS.SERVERSTARTDATE FROM TBACKUPVERSIONS WHERE ID = TVERSIONS.CVERSION)

I don't understand why this query takes so much time (most of the other queries take less than 500ms to execute). Is it because of the use of a computed field in the subqueries ? What can I do in order make things faster ?

Best regards,

Adrien