Firebird Relational Database Management System

The Firebird Profiler allows you to measure the performance cost of SQL and PSQL (Procedural SQL) code. It collects statistics such as execution counts, minimum, maximum, and accumulated execution times (with nanosecond precision) for each line of code, as well as cursor statistics (open/fetch) for implicit and explicit SQL cursors.

Key Concepts

• Sessions: Profiling is managed via sessions. A session can be local (the same attachment/connection) or remote (targeting another attachment).
• Remote Profiling: To profile a remote attachment, the target must be in an idle state. If the remote attachment belongs to a different user, you must have the PROFILE_ANY_ATTACHMENT system privilege.
• Data Collection: Statistics are collected in memory and aggregated per request (statement execution). To view the data, it must be flushed to the snapshot tables.
• Flushing: Data is moved from memory to snapshot tables using an autonomous transaction. This means if you call FLUSH within a transaction, the results may not be immediately visible in that same transaction.

When using declared local temporary tables in complex PSQL structures:

• Recursion: Recursive calls reuse the same compiled table structure, but each recursive execution frame has its own separate rows. Data does not leak between recursive levels.
• Local Subroutines: Local procedures or functions declared within a scope can access and modify the rows of the containing execution frame's declared local temporary table.

-- Example of local subroutine accessing outer table
set term !;

execute block returns (n integer, s integer)
as
    declare local temporary table t (
        id integer
    );

    declare procedure p_add(v integer)
    as
    begin
        insert into t values (:v);
    end

    declare function f_count returns integer
    as
        declare variable ret integer;
    begin
        select count(*) from t into ret;
        return ret;
    end
begin
    insert into t values (1);
    execute procedure p_add(2);

    n = f_count();
    select sum(id) from t into s;
    suspend;
end!

set term ;!

To avoid repetitive window definitions, you can use the WINDOW clause to name a window and reference it in subsequent OVER clauses.

Rules for Named Windows

• Referencing: A named window can be used in OVER to reference its definition. It can also serve as a base window for another named or inline window.
• Limitations:
  • A window that includes a frame clause (ROWS, RANGE, or GROUPS) cannot be used as a base window, though it can be used with OVER <window_name>.
  • A window that uses another window as a base cannot have its own PARTITION BY clause, nor can it override the base window's ORDER BY.
• Scope: The scope of a named window is limited to its specific query context (e.g., it is not visible to outer or sibling subqueries).

-- Example: Defining and nesting named windows
select
    id,
    department,
    salary,
    count(*) over w1,
    first_value(salary) over w2,
    last_value(salary) over w2
  from employee
  window w1 as (partition by department),
         w2 as (w1 order by salary)
  order by department, salary;