Performance

<< Command-line tools | Firebird 2 Migration & Installation | Firebird API >>

Performance

The following changes should be noted as possible sources of performance loss:

Existence predicates `NOT IN` and `ALL` may be slow

Firebird and, before that, InterBase^Ž, have produced incorrect results for the logical existence predicates ALL and NOT IN for many years. That problem has been corrected in Firebird 2.0, but the change means that indexes on the inner tables cannot be used and performance may be slow compared to the same query's performance in v.1.5. "Inner tables" are the tables used in the subquery argument inside an ALL or NOT IN expression.

Note: NOT EXISTS is approximately equivalent to NOT IN and will allow Firebird to use indexes.

Indexes involving data type coercion may be ignored

(v.2.1) In cases where an indexed field and an argument have different data types and the implicit conversion cannot be performed consistently, the index will be ignored. Prior versions that appeared to "work" in these cases could return wrong results.

A common example would be a predicate like STRING_FIELD = INTEGER_ARGUMENT. The indexed scan is for this predicate is now disallowed since a numeric can be converted to a string in different ways.

However, for the reverse case, INTEGER_FIELD = STRING_ARGUMENT, index scanning is allowed because the conversion is deterministic.

(v.2.1) Some date/time expressions in Dialect 1 can not benefit from available indices either, e.g., DATE_FIELD > 'NOW' + 1.

Resolution of this issue is expected in the first point release. In the meantime, a workaround is to use CAST or specify the explicit data type prefix, viz., DATE_FIELD > TIMESTAMP 'NOW' + 1.

Superserver garbage collection changes

Formerly, Superserver performed only background garbage collection. By contrast, Classic performs "cooperative" GC, where multiple connections share the performance hit of GC.

Superserver's default behaviour for GC is now to combine cooperative and background modes. The new default behaviour generally guarantees better overall performance as the garbage collection is performed online, curtailing the growth of version chains under high load.

It means that some queries may be slower to start to return data if the volume of old record versions in the affected tables is especially high. ODS10 and lower databases, having ineffective garbage collection on indices, will be particularly prone to this problem.

The GCPolicy parameter in firebird.conf allows the former behaviour to be reinstated if you have databases exhibiting this problem.