FireDAC Prepared Statements: What TFDQuery.Prepared Actually Changes

A TFDQuery that opens cleanly the first time and throws EDatabaseError the second time is usually not a broken query. It is a query whose parameters were never bound. That error is the visible edge of a larger design decision in FireDAC: whether you let the driver prepare a statement once and execute it many times, or whether you rebuild and re-parse SQL on every call.

The practical takeaway: Prepared := true is a request to the driver, not a guarantee of speed. The reliable win comes from binding parameters instead of concatenating literals, and the prepare/execute split is what makes that binding safe. Whether you also get a server-side plan cache depends on the DBMS and the FireDAC driver version you have installed.

What Prepared asks the driver to do

Setting TFDQuery.Prepared := true asks FireDAC to prepare the statement at that moment rather than leaving the timing to the driver. On engines that support server-side statement caching, the SQL text is transmitted once and the compiled plan is reused for later executions of the same statement.

Two behaviors are worth knowing before you rely on this:

• Changing SQL.Text discards the prepared state. FireDAC has to prepare again, and any parameters you assigned are regenerated from the new text.
• Not every FireDAC driver honors the hint. Some ignore it, and some require an explicit Close before re-preparing to avoid plan-cache conflicts. Treat the property as advisory.

Parameter binding is the part that always matters

FireDAC exposes parameters through the Params default array or by name. Assigning by name is usually clearer:

FDQuery1.ParamByName('CustomerID').AsString := 'ALFKI';
FDQuery1.ParamByName('MinTotal').AsCurrency := 100;

If a parameter in the SQL text is left unpopulated, FireDAC raises EDatabaseError when the query executes. That is a feature, not an obstacle: it stops you from silently running a query with a missing filter. The same discipline is what keeps user input out of the SQL string, which is the more important reason to bind rather than concatenate.

A worked example: one query, many executions

The snippets below are illustrative and were not executed for this article. Treat them as a starting point and confirm behavior against your installed RAD Studio version and DBMS.

// Delphi. Assumes FDConnection1 is configured and Connected,
// and FDQuery1 is a TFDQuery already linked to that connection.
FDQuery1.Close;                       // required before changing SQL text
FDQuery1.SQL.Text :=
  'select OrderID, CustomerID, Total from Orders ' +
  'where CustomerID = :CustomerID and Total > :MinTotal';
FDQuery1.ParamByName('CustomerID').AsString := 'ALFKI';
FDQuery1.ParamByName('MinTotal').AsCurrency := 100;
FDQuery1.Prepared := true;            // prepare at a point you choose
FDQuery1.Open;

Reusing the same query object in a loop is where the split pays off. The SQL text does not change, so the statement stays prepared; only the parameter values change between executions.

// Inline var requires Delphi 10.3 Rio or later. Use a declared
// loop variable on older compilers.
for var i := 0 to High(Customers) do
begin
  FDQuery1.ParamByName('CustomerID').AsString := Customers[i];
  FDQuery1.Open;
  // read column values here
  FDQuery1.Close;
end;

Order matters in one specific way: assign SQL.Text first, then parameters, then open. Setting Prepared := true before you assign parameter values is generally fine, because values are bound at execution rather than at prepare time — but if the SQL text changes, everything downstream of it is rebuilt.

Where prepared statements do not pay off

Three limitations are worth planning around.

• Driver and DBMS dependence. The performance gain depends on the specific driver version and connection settings. If the driver ignores the hint, you still get the safety benefit of binding, but not the parse savings.
• Plan reuse can be wrong reuse. Cached plans are built from the first execution's parameters. On engines with parameter sniffing or bind peeking, a plan that is good for one value distribution can be poor for another. Prepared statements are not automatically faster.
• Large parameter sets. Extensive IN lists and LIKE patterns do not map cleanly to a single bound parameter. Building a literal list gives up the prepare benefit; very large parameter sets can also exceed driver packet limits and produce fallback errors or truncated data.

There is also a resource angle: prepared statements occupy server-side memory until they are released. Mixing many ad-hoc queries and many prepared queries on the same TFDConnection can bloat the plan cache and degrade overall performance. Keep the prepared set small and long-lived rather than setting the property everywhere.

For bulk inserts, FireDAC offers Array DML through Params.ArraySize, which is a different mechanism from statement preparation. Do not assume one substitutes for the other.

How to check that it is actually working

Two verification routes are practical. Both require you to confirm the exact names against your installed version rather than trusting a snippet.

1. FireDAC tracing. FireDAC ships a tracing unit — in recent versions fdwTrace — and TFDQuery exposes trace flags such as fdqtPrepared and fdqtExec. Add the unit to your uses clause, enable the flags, run the app, and read the Messages window. You are looking for one prepare event followed by repeated execute events. If the flag identifiers do not compile, search the RAD Studio source folder for fdqtPrepared to find the correct names for your version.
2. DBMS-side logging. SQL Server Extended Events or Profiler, PostgreSQL's log_statement, and Oracle's V$SQL can all show prepare and execute activity. Setting names differ by engine and version, so check your DBMS documentation. The signal is the same: one prepare, many executes.

Measure before and after on a query that actually runs in a loop or at high frequency. A single-row lookup executed once per user action will not show a difference you can detect.

What to do next

Pick the handful of queries that run repeatedly with changing filter values. Bind their parameters by name, set Prepared := true explicitly so the prepare point is visible in your code, and reuse the same query object instead of recreating it. Then confirm with a trace or a DBMS log that you are seeing one prepare and repeated executes. If you are not, the driver is likely ignoring the hint — and you have still removed string concatenation from your SQL, which was the more valuable change.