Unlocking Digital Twin Performance: How ECSQL Query Optimization & `PRAGMA explain_query` Turbocharge Bentley iTwin Applications

Another blog on the background of where our investigations took us this week.

From Steam Engine Queries to F1 Performance with PRAGMA explain_query

Why Query Performance is the Bottleneck in Digital Twins

When building applications on the Bentley iTwin platform, user experience lives and dies by query responsiveness. Whether an engineer is navigating a 3D model of a high-speed rail line, clicking on a structural column, or running automated asset compliance checks across a hospital complex, every interaction triggers ECSQL queries against the underlying iModel.

In small models with 50,000 elements, almost any query feels instant. But real-world infrastructure models regularly exceed 5,000,000 to 10,000,000+ elements with millions of associated aspects, relationships, and property definitions. I have seen a trend towards modelling of work-packs, but there are still generally approaching 1 million elements in these iModels.

An unoptimized query scanning polymorphic relationship tables across the entire model can easily take 1.5 to 4+ seconds. In an interactive web viewer, that means frozen dashboards, sluggish element selection, and server quota warnings. Multiply that by a hundred ... ouch I just lost my ability to generate a dashboard.

Here is what we learned optimizing ECSQL queries in production, how to uncover bottlenecks using PRAGMA explain_query, and the techniques that slashed query runtimes by over 85%.

1. The Hidden Architecture of ECSQL

To optimize ECSQL, you have to understand what happens when a query executes:

Object-Relational Schema Mapping: ECSQL is an object-relational dialect that abstracts the BIS (Bentley Infrastructure Standard) schema. When you query bis.Element or bis.GeometricElement3d, ECSQL automatically resolves polymorphic class hierarchies.
Compilation to SQLite: Under the hood, the iTwin engine compiles ECSQL statements into SQLite queries executed directly against the SQLite database (regardless of whether it is in te cloud, your backend or on your workstation).
The Pitfall: Because of this translation layer, it is very easy to write an ECSQL statement that looks completely innocent, but compiles into a catastrophic full-table scan (SCAN TABLE) in SQLite.

2. The Secret Diagnostic Weapon: `PRAGMA explain_query`

You cannot optimize what you cannot measure. While standard database engines support EXPLAIN QUERY PLAN, Bentley ECSQL exposes its internal planner through a dedicated pragma:

PRAGMA explain_query('SELECT * FROM bis.GeometricElement3d')

While developing the Class Index we noticed a serious degradation of performance when querying aspects.

We have a Query API client, which is the funnel for all iTwin ecSQL queries, so we added explainQuery to allow one-line query inspection with automatic quote escaping and ES2017 compatibility for long running queries:

public async explainQuery(query: string): Promise<any[]> {
  const cleanQuery = query.trim().replace(/^PRAGMA\s+explain_query\('([\s\S]*)'\)$/i, '$1');
  const escapedQuery = cleanQuery.replace(/'/g, "''");
  return this.executeQuery(`PRAGMA explain_query('${escapedQuery}')`, false);
}

Reading the Execution Plan: `SCAN` vs `SEARCH`

When you run PRAGMA explain_query, the iModel engine returns plan records:

`id`	`parent`	`detail`	Meaning
`0`	`0`	`SCAN TABLE bis_Element`	⚠️ Full Table Scan: Scans every row in the iModel sequentially. As the model grows, latency scales linearly.
`0`	`1`	`SEARCH TABLE bis_Element USING INDEX ix_bis_Element_ECClassId`	✅ Indexed Lookup: B-tree index seek. Executes in sub-milliseconds regardless of model size.
`0`	`2`	`USE TEMP B-TREE FOR GROUP BY`	⚠️ Memory Spilling: Creates an in-memory temporary B-tree for sorting or unindexed grouping.

3. Two Real-World Anti-Patterns (And How We Fixed Them)

Anti-Pattern A: Unfiltered Relationship Table Scans

In our class metadata inspector, we needed to find all aspect classes and their properties.

The initial query scanned the polymorphic relationship tables bis.ElementOwnsMultiAspects and bis.ElementOwnsUniqueAspect:

-- ❌ BEFORE: Naive Query
SELECT sourceecclassid, targetecclassid, ...
FROM (
  SELECT DISTINCT sourceecclassid, targetecclassid FROM bis.ElementOwnsMultiAspects
  UNION
  SELECT DISTINCT sourceecclassid, targetecclassid FROM bis.ElementOwnsUniqueAspect
)
JOIN meta.ECClassDef ecs ON ecs.ECInstanceId = sourceecclassid
WHERE LOWER(ec_className(ecs.ECInstanceId, 's.c')) LIKE 'ifcwall'

The Plan via `PRAGMA explain_query`:

SCAN TABLE bis_ElementOwnsMultiAspects
USE TEMP B-TREE FOR DISTINCT
SCAN TABLE bis_ElementOwnsUniqueAspect
USE TEMP B-TREE FOR DISTINCT
COMPOUND SUBQUERIES 2 AND 3 (UNION)

Because the filter WHERE ... LIKE 'ifcwall' was in the outer query, the engine performed a full scan of every aspect relationship across the entire iModel (~600,000 rows) and built temporary distinct tables before applying the filter!

The Fix (Subquery Filter Pushdown):

We pushed the class predicate directly into the subqueries:

-- ✅ AFTER: Pushed Down Subquery
SELECT sourceecclassid, targetecclassid, ...
FROM (
  SELECT DISTINCT SourceECClassId, TargetECClassId
  FROM bis.ElementOwnsMultiAspects
  WHERE SourceECClassId IN (
    SELECT ECInstanceId FROM meta.ECClassDef WHERE LOWER(ec_className(ECInstanceId, 's.c')) LIKE 'ifcwall'
  )
  UNION
  SELECT DISTINCT SourceECClassId, TargetECClassId
  FROM bis.ElementOwnsUniqueAspect
  WHERE SourceECClassId IN (
    SELECT ECInstanceId FROM meta.ECClassDef WHERE LOWER(ec_className(ECInstanceId, 's.c')) LIKE 'ifcwall'
  )
)

The Result: SQLite used the index on SourceECClassId, skipping 99.8% of the relationship table.

Anti-Pattern B: Functions in `WHERE` Clauses Breaking Indexing

In our property aggregation dashboards, we queried instance property statistics for selected classes:

-- ❌ BEFORE: Function on indexed column
SELECT ec_className(ECClassId, 's.c'), COUNT(*)
FROM bis.PhysicalElement
WHERE ec_className(ECClassId, 's.c') IN ('IFCDynamic.ifcdoor', 'IFCDynamic.ifcwindow')
GROUP BY ECClassId;

The Catch: Wrapping ECClassId in ec_className() prevents SQLite from using the B-Tree index on ECClassId. Every single row in bis.PhysicalElement was scanned and formatted as a string.

The Fix:

Filter by integer class IDs resolved via metadata subquery:

-- ✅ AFTER: Indexed integer comparison
WHERE ECClassId IN (
  SELECT ECInstanceId FROM meta.ECClassDef 
  WHERE ec_className(ECInstanceId, 's.c') IN ('IFCDynamic.ifcdoor', 'IFCDynamic.ifcwindow')
)

4. Concrete Performance Results

Benchmarking against our test iModel (IFC Hospital iTwin Testing iModels):

Query Scenario	Before (Unoptimized)	After (Optimized)	Speedup	Plan Change
Class Aspect Mapping	`1,420 ms`	`115 ms`	12.3x	`SCAN TABLE` ➔ `SEARCH USING INDEX`
Filtered Physical Class Counts	`890 ms`	`95 ms`	9.4x	Full table scan ➔ Index seek
Classes & Properties Discovery	`2,150 ms`	`340 ms`	6.3x	Cartesian join ➔ Pre-aggregated subquery
Root Spatial Parent Resolution	`50 sequential HTTP calls (3.2s)`	`1 batch CTE (140 ms)`	22.8x	Network N+1 ➔ Batch query

5. Automating Query Quality in CI/CD

To ensure queries stay fast as the application evolves, we introduced:

Slow Query Threshold Logging: Inside our API client, every query execution records timing. Any statement taking longer than 1,000ms automatically logs:
```
[ECSQL SLOW QUERY 1420.5ms, 2500 rows]:
SELECT ...
```
Standalone Explain CLI Tool: We implemented an explain tool that can benchmark and inspect query plans directly from the terminal before shipping:
```
npx tsx scripts/explain-ecsql.ts "SELECT * FROM bis.GeometricElement3d" --token "Bearer ..."
```

Key Takeaways for iTwin Developers

Never guess—run PRAGMA explain_query('...'): A 10-second inspection tells you whether your query will scale to 1M elements or stall your application.
Push predicates into subqueries: Don't join relationships across the whole model and filter at the end.
Never apply scalar functions to indexed columns in WHERE clauses: Compare raw ECClassId values whenever possible.
Batch network calls: Replace iterative for loops querying individual element hierarchies with recursive Common Table Expressions (WITH RECURSIVE).

Remember you do not need a CLI tool, I only used it to feed into an optimisation tool, but you can you iModelConsole to run the pragam explain tool. Give it a go on your longest running queries ... if you don't understand the output ... there are plenty of helpers out there that will.

Have you encountered unexpected performance bottlenecks in ECSQL? What query optimization patterns have made the biggest difference in your digital twins?

Unlocking Digital Twin Performance: How ECSQL Query Optimization & PRAGMA explain_query Turbocharge Bentley iTwin Applications