The life cycle of a Firebird SQL statement

A simplified life cycle of any Firebird DSQL statement (cursor) is handled internally as follows.

Allocation

(isc_allocate_statement) - Allocates a statement handle.

Preparation

(isc_dsql_prepare) - The statement is validated, access rights are checked, as are links to metadata, the statement is then compiled into BLR (Firebird’s internal language representation which describes the actions that the database engine needs to perform on behalf of the database) along with the establishment of an execution plan.

This step can take some time as it may require the initialization of database metadata (if it’s not already in the metadata cache), access right checks to all the database objects that are used in the query and the calculation of an optimal execution plan. The time taken to prepare a statement is related to the statement’s complexity rather than table sizes.

Execution

BLR is then used to execute the statement, and the output data structures are allocated and filled.

Bind

(isc_dsql_describe and isc_dsql_describe_bind) – Provides a description of the columns (XSQLDA data structure) that make up the rows returned by a select statement (isc_dsql_describe) and description of input parameters (isc_dsql_describe_bind). Not always necessary.

Execute

(isc_dsql_execute2) – Executes a previously prepared statement. If statement has any input parameters, it's necessary to fill XSQLDA data structure initialized in previous step (isc_dsql_describe_bind) with new values before execution.

Fetch

(isc_dsql_fetch) – Data is retrieved and sent to the client.

Close

(isc_dsql_free_statement) – Frees a statement handle and all resources associated with it. To finish, either a "soft" (close - DSQL_close) or "hard" (drop – DSQL_drop) is done via isc_dsql_free_statement using different parameter. A "soft" close releases only the data structures that are related to the actual execution, and it keeps the BLR, which allows for the subsequent execution of the same statement in its given context (connection) without having to go through first step (preparation) again. This means that any statement that's not permanently closed (drop) at the end of its use can be reused again. The isc_dsql_prepare call allows you to initialize the cursor (first step) for execution without doing the actual execution (second step).

The “Prepared Statements” are typically managed by the client connectivity libraries, they determine how to manage the lifetime of statements and therefore whether they use a "soft" versus. "hard" close. In general most libraries keep statements that are created by prepare by closing them using dsql_close, other statements are dropped on close. Other libraries (the Python driver for example) close all statements. The statements that were created for direct immediate execution are reused via an internal cache if required, while the statements created using prepare are managed within the application using an independent PreparedStatement instance. All statements are only dropped when the cursor (or independednt PreparedStatement) instance is destroyed i.e. when the connection itself is closed.

In Firebird V3 it is planned that the database engine at an internal level will maintain a cache of prepared statements, i.e. these statements once prepared are not dropped but stored in an internal cache and then reused if the same SQL statement is sent again to the engine within a given connection.

Based on the above it becomes fairly obvious that using prepared statements and using a “soft” close is more efficient than using simple execute statements, especially if the same SQL construct is used repeatedly. However holding prepared statements for a long time has potentially two drawbacks. One, the execution plan is not recalculated, so if the data distribution changes significantly over the "lifetime" of a prepared statement, it could impact performance. Two, since a prepared statement is bound to a connection, it shares the drawbacks of having long open connections (in regard to possible memory leaks perhaps). Therefore It's considered good practice to close connections occasionally. So, it's fine to have open connections (and thus prepared statements available for a long time (hours, even days), but it's good practice to close (and if necessary reopen) them on a fairly regular basis.