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