Reducing Network Chatter with Firebird Selectable Stored Procedures

The Cost of the Round-Trip

When an application needs to perform a complex calculation—such as aggregating monthly sales, applying tiered discounts, and updating a ledger—the common approach is to fetch data to the application layer, process it, and send updates back. This creates "network chatter": a sequence of multiple requests and responses that introduce latency and increase the risk of transaction timeouts.

The most effective way to eliminate this overhead in Firebird SQL is by moving the logic into the database using Stored Procedures written in Procedural SQL (PSQL). By encapsulating the logic on the server, you replace ten network round-trips with one single call.

Selectable Procedures and the SUSPEND Keyword

Unlike standard functions that return a single value, Firebird stored procedures can act as virtual tables. This is achieved through the SUSPEND statement. When the engine hits a SUSPEND command, it "freezes" the procedure's current state and returns the current values of the output parameters to the client as a single row.

The procedure then resumes exactly where it left off. If the SUSPEND command is inside a loop, the procedure will yield multiple rows, allowing you to use the procedure directly in the FROM clause of a SELECT statement.

Worked Example: Implementing a Tiered Commission Engine

Imagine a scenario where you need to calculate commissions for sales staff based on varying percentage tiers. Doing this in the application requires fetching every sale and the current tier rules. Instead, we can build a selectable procedure.

The Implementation

Run the following command in your Firebird database tool (e.g., FlameRobin or isql) with sysdba or administrative permissions:

CREATE OR ALTER PROCEDURE GET_COMMISSIONS (
    START_DATE DATE, 
    END_DATE DATE
)
RETURNS (
    EMPLOYEE_ID INTEGER, 
    TOTAL_COMMISSION NUMERIC(15,2)
)
AS
DECLARE VARIABLE SALE_AMT NUMERIC(15,2);
DECLARE VARIABLE TIER_RATE NUMERIC(5,2);
BEGIN
    /* Iterate through employees who had sales in the period */
    FOR SELECT E.ID, SUM(S.AMOUNT) 
        FROM EMPLOYEES E 
        JOIN SALES S ON E.ID = S.EMP_ID
        WHERE S.SALE_DATE BETWEEN :START_DATE AND :END_DATE
        GROUP BY E.ID 
        INTO :EMPLOYEE_ID, :SALE_AMT
    DO
    BEGIN
        /* Logic to determine tier rate */
        IF (:SALE_AMT > 10000) THEN TIER_RATE = 0.10
        ELSE IF (:SALE_AMT > 5000) THEN TIER_RATE = 0.05
        ELSE TIER_RATE = 0.02;

        TOTAL_COMMISSION = :SALE_AMT * :TIER_RATE;
        
        /* Return the row and wait for the client to request the next */
        SUSPEND;
    END
END

Execution and Verification

To verify the result, execute the procedure as if it were a table. This runs on the server and returns only the final calculated set:

SELECT EMPLOYEE_ID, TOTAL_COMMISSION 
FROM GET_COMMISSIONS('2023-01-01', '2023-12-31');

Expected Result: A result set containing one row per employee with their calculated commission. If the procedure returns no rows, verify that the date range matches your SALES table data.

Engineering Trade-offs

While moving logic to PSQL improves performance, it introduces specific constraints:

• CPU Distribution: You are shifting the computational burden from the application server (which is easily scalable) to the database server (which is typically a single point of scale). If your logic involves heavy string manipulation or complex math, monitor the DB server's CPU usage.
• Observability: PSQL is harder to debug than Java, Python, or C#. There is no native "step-through" debugger in the core engine; you often have to rely on logging values to a temporary table to trace execution.
• Portability: PSQL is specific to Firebird. Heavy reliance on stored procedures creates a strong dependency on the Firebird engine, making a future migration to another SQL dialect a rewrite task rather than a porting task.

Practical Summary

Use stored procedures when the volume of data being moved to the client is significantly larger than the final result set, or when a sequence of dependent queries is causing latency. To roll back the changes in the example above, run DROP PROCEDURE GET_COMMISSIONS;