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
