Firebird - ускорение БД

По материалам: https://www.ib-aid.com/br/articles/45-ways-to-speed-up-firebird-database/


-- Мое скромное обобщение всеобщего опыта

1. SSD + RAID 10 + Disk Write-back cache + Disk Read cache + TEMP on SSD

2. SuperServer 3.0 for Firebird 3

3. DefaultDBCachePages>=50000 pages for Firebird 3

4. TempCacheLimit>=1Gb for Firebird 3

5. Forced Writes=OFF (3 times speed up) - BBU && UPS devices only!!!

6. LockHashSlots=30011 (big prime number)

7. Switch off indices, use Global Temporary Tables for fast and bulk inserts

8. Use NO_AUTO_UNDO transaction option for bulk inserts/updates

9. Read only transactions for read, short transactions for write

10. "Turn off" indices for SQL IN: WHERE fieldX+0 IN (Val1, Val2, ValN)

11. Replace IN-IN-IN with JOIN

12. Use LEFT JOIN: from the smallest table to the largest one.

13. Limit fetch of SELECT (SELECT FIRST OFFSET)

14. Use less number of columns in SELECT 

15. Use derived tables and WITH sub-queries

16. Use BIGINT for primary and unique keys

17. Make SET STATISTICS for indice optimization

18. Use HASH JOINs: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0

19. Make functions as DETERMINISTIC

20. Use analytical (window) functions

21. Use WHERE CURRENT OF search in cursor sub-cycles

22. Use switch -se for gbak

#