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
 

Reply via email to