Optimization of Firebird queries

The course covers the main principles of Firebird queries optimizations: SQL plans, detailed statistics of queries, access methods, tuning approaches, and how to track slow and problematic queries. Please see the detailed schedule of the course below:

Performance of SELECT queries

How query is executed:

Client-server approach
Prepare
Plan generation
Execution
Fetches
Buffering

Performance execution statistics counters:

Reads, Writes, Fetches, Time
Database cache, Disk, CPU
How garbage impacts on queries execution
Cache hits (Classic vs SuperServer

Access methods/data paths

NATURAL scan
INDEX search
- How indices work: B-Trees, record pointers
- Indices direction
- Examples
Forced INDEX usage
Bit merge of indices
Table cardinality, index selectivity
Turning off indices in WHERE
Index ORDER
SORT
- Firebird.conf settings
Differences between index ORDER and SORT
Turning off indices in ORDER BY, GROUP BY
Plans in stored procedures
Plans in VIEWs

Queries tuning

PLAN – how to read and understand it
Firebird optimizer tweaks
WHERE FIELD IN (X1, X2, X3...
LIKE, CONTAINING, STARTING WITH
JOIN
- Implicit, explicit
- Wrong JOINS
- LEFT, RIGHT
- Self-JOINS
UPDATE+SELECT
MAX, MIN, COUNT, AVG, SUM
IN
- Exists
- Merge
WHERE (X and Y) or (K and N)

Tracking slow and resource-consuming queries through the MON$ and Trace API

What are MON$ and Trace
MON$ tables fields – what they mean
TraceAPI parameters and output
Tools