|Subject||Re: [firebird-support] Performance issues with ORDER BY|
ORDER BY fetches records (fields in SELECT and ORDER BY) first and then sort them all.
Without Order by only several first records fetched.
Try to reduce number of fetched fields and increase TempCacheLimit parameter in firebird.conf to some big value (or just use our optimized configuration files http://ib-aid.com/en/optimized-firebird-configuration/).
I'm trying to optimize a request and cannot explain what I observe.
I have a relatively simple request using 3 tables returning around 1.8M
rows. On each table I have an index on: the primary key (ascending,
unique), the foreign keys (ascending) and the primary key (descending).
I'm using Firebird 2.5.3 (embedded and super server).
When I use ORDER BY ... DESC here is the plan I get:
PLAN JOIN (Table1 ORDER Index_on_primary_key_desc, Table2 INDEX
(RDB$PRIMARY888), Table3 INDEX (RDB$PRIMARY886))
8012 fetches, 0 marks, 43 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 2003 index, 0 seq.
Delta memory: 43832 bytes.
Total execution time: 0.076s
Very fast, excellent. When I switch to ORDER BY ... ASC I have:
PLAN JOIN (Table1 ORDER RDB$PRIMARY896, Table2 INDEX (RDB$PRIMARY888),
Table3 INDEX (RDB$PRIMARY886))
16164861 fetches, 4 marks, 58282 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 4033950 index, 5802 seq.
Delta memory: 1427232 bytes.
Total execution time: 7.272s
Execution time is multiplied by 100 even when indices are used.
I tried to recompute statistics for all indices but results are weird:
ORDER BY ASC went from 7.2s to 3.5s (faster but still slow) and ORDER BY
DESC went from 0.076s to 3.5s (slowdown by a factor 50).
I don't really understand what might cause this huge performance
difference even when using indices, if I would be better off using another
type of index, why the slowdown after statistics are updated or if I'm
missing something basic.
Here is the full request if needed:
Table1.ID, Table1.FKID, Table1.MD... some other fields
FROM Table1, Table2 , Table3
WHERE 1 IS NOT NULL
AND (Table2.ID = Table1.FKID)
AND (Table2.FKID = Table3.ID)
AND (Table3.ComputationID = 9)
ORDER BY Table1.ID ASC;
I'd welcome any insight as to what I might be doing wrong.
Boris HERBINIÈRE-SÈVE | Software developer