Optimization of Firebird queries

Optimization of Firebird queries covers how SELECT statements run: plans, statistics, access methods, tuning patterns, and how to catch slow queries with MON$ and Trace API.

About 5-6 hours · English · For developers · All courses

Syllabus

Performance of SELECT queries - how a query is executed
- Client-server approach
- Prepare, plan generation, execution, fetches, buffering
Execution statistics
- Reads, writes, fetches, time
- Database cache, disk, CPU
- How garbage affects execution
- Cache hits (Classic vs SuperServer)
Access methods / data paths
- NATURAL scan
- INDEX search: B-trees, record pointers, direction, examples
- Forced index use, bit merge of indices
- Table cardinality and index selectivity
- Turning indices off in WHERE
- Index ORDER vs SORT (firebird.conf settings)
- Turning indices off in ORDER BY / GROUP BY
- Plans in stored procedures and views
Query tuning
- How to read a PLAN
- Optimizer tweaks
- WHERE FIELD IN (…)
- LIKE, CONTAINING, STARTING WITH
- JOIN: implicit vs explicit, bad joins, LEFT/RIGHT, self-joins
- UPDATE + SELECT
- MAX, MIN, COUNT, AVG, SUM
- IN vs EXISTS vs MERGE
- WHERE (X AND Y) OR (K AND N)
Tracking slow queries with MON$ and Trace API
- What MON$ and Trace are
- MON$ fields that matter
- Trace API parameters and output
- Tools (including HQbird monitoring)