45 Ways To Speed Up Firebird Database

Here you can find the list of performance tips for Firebird database in different areas - from hardware/OS and Firebird configuration tuning to SQL optimization recommendations. This list is not the complete reference how to optimize Firebird, and it assumes that you understand basics of Firebird functioning, such as execution plans, transactions management, and queries performance statistics.

Please apply these tips with caution and verify their effect before putting to the production.

Our company (IBSurgeon) offers the comprehensive database performance optimization service and Firebird training through Skype.

 

16. Deactivate indices for bulk inserts

If you insert or update many records (more than 25% of the table), deactivate indices for the table where records are inserted and reactivate them after insert or update. The index rebuild operation can be faster than many updates of the index.

17. Use Global Temporary Tables for fast inserts

To speed up inserts and updates, use Global Temporary Tables for bulk inserts of the large recordsets, and then transfer records into the permanent table. It can be very effective to insert records to GTT, pre-process them and then move to the persistent table.

18. Avoid unnecessary indices

Use fewer indices for tables with intensive inserts and updates. Each index adds significant overhead for insert, update, delete, and garbage collection operations – there could be 3-4 additional page reads and writes when the single record is being inserted/updated/deleted/cleaned for each index.

19. Replace UDFs with embedded functions calls

Replace UDF calls with embedded functions calls. Many embedded functions were added in the recent versions of Firebird, which offer functionality previously available only in UDF libraries. Replace such functions where possible, since embedded functions work up to 3 times faster than UDFs. The shorter writeable transaction is, the better. The short transactions retain proportionally less number of record versions from garbage collection than long-running. Unfortunately, even the single long-running transaction (from the development tool left open, for example) can screw the good effect of all other short writeable transaction. That's why you need to monitor long-running transaction and fix the appropriate places in the source code. Use HQbird DataGuard tool to receive alerts about the oldest active transaction in Firebird database (what applications started it, what IP address, the timestamp of its start), and HQbird MonLogger tool to see the complete list of the long-running active transactions and their IO statistics. Also, if you are using database access components/libraries which can cache record sets, use cached updates.

22. Avoid long record chains

Avoid situations when one record has many record versions – Firebird works much slower with long record chains. (to see how many record versions some tables has, and what is the longest record chain you can use HQbird IBAnalyst tool, tab Tables, sort on "Max Version"). Use the combination of inserts and scheduled delete of old records instead of multiple updates of the same record.

23. Use PREPARE correctly

Use prepared statements to run SQL queries where only parameters are changed – for example, make prepare before the loop of such queries. Prepare can take significant time (especially for big tables), and preparing the query only once will greatly increase the overall performance.

24. Don't COMMIT too often during bulk insert/update operation

In the case of bulk INSERT/UPDATE/DELETE operation, don't commit the transaction after each change (it can happen if you are using auto commit option in your database driver) - commit transactions at least after 1000 operations or more. Each transaction commit runs several read/write IO operations against the database, that's why often commits decrease database performance.

25. "Turn off" indices if you are using IN with many constants

If you are using construction WHERE fieldX IN (Constant1, Constant2,… ConstantN), and there is an index on fieldX, Firebird will use an index as many times as many constants are in the IN list. Disable index search by turning fieldX into expression +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), or, for strings, use fieldX||''

26. Replace IN with JOIN

Avoid using queries with nested WHERE IN(SELECT... WHERE IN (SELECT.. WHERE IN() )), it can confuse Firebird optimizer. Transform nested INs into joins.

27. Use LEFT JOIN in the correct way

If you are using LEFT OUTER joins, explicitly put tables in the join from the smallest one to the largest one.

28. Limit fetch of SELECT queries

Always try to limit the large output for SELECT queries with FIRST… SKIP or ROWS clauses. If the query is not designed specifically as a report (which requires all records to be printed/exported), usually it is enough to show top 10-100 records. Fetch only necessary records.

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

Select id, department, salary, salary / (select sum(salary) from employee) percentage
from employee

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk