Optimization of Firebird queries

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
Contact us for the quote now.