Performance

Syntra ODBC is designed for fast query performance out of the box. This page covers how to get the most out of the system and troubleshoot slow queries.

Read Path Selection

Syntra has three read paths, each with different performance characteristics:

Read Path	Latency	When Used
Local cache	< 1 ms	Default for all queries
Live reads (Enterprise)	10–50 ms	QuickBooks Enterprise only
Live QuickBooks fetch	200 ms – 10 s	When QB_MAX_STALENESS = 0 or cache stale

Recommendations

• Use the cache for dashboards, reports, and analytical queries. The sub-millisecond read times make it ideal for tools like Power BI and Tableau that issue many queries.
• Use live fetches sparingly. SET QB_MAX_STALENESS = 0 forces every query to go through QuickBooks itself, which is significantly slower and single-threaded.
• Enable live reads on QuickBooks Enterprise when you need low-latency reads with guaranteed freshness. See Live Data Reads (Enterprise).

Query Optimization

Use WHERE Clauses

Narrow your queries to reduce the amount of data scanned:

-- Slow: scans all invoices
SELECT * FROM invoices;

-- Fast: scans only recent invoices
SELECT * FROM invoices WHERE txn_date >= '2025-01-01';

Use LIMIT

When exploring data or building queries, always add a LIMIT:

SELECT * FROM invoice_lines LIMIT 100;

Select Only Needed Columns

Avoid SELECT * in production queries. Selecting specific columns reduces data transfer:

SELECT name, balance, phone FROM customers;

Avoid Unnecessary Live Fetches

Every SET QB_MAX_STALENESS = 0 query goes through the QBFC COM SDK, which processes requests sequentially. A burst of live-fetch queries will queue up.

Connection Pooling

Each connection to Syntra holds a cache reader handle. For applications that open many connections:

• Use connection pooling in your client (e.g., SQLAlchemy pool, pg Pool).
• Close connections promptly when done.
• Avoid leaving idle connections open indefinitely.

Cache Performance

Warm-Up Time

On first startup, the initial full sync may take several minutes for large company files. Subsequent startups resume from the persisted cache almost instantly.

Incremental Sync Impact

Incremental sync runs in the background and does not block queries. However, very large change sets (e.g., importing thousands of transactions in QuickBooks) may cause a brief increase in sync time.

Cache Memory Usage

The cache engine uses memory-mapped I/O for the cache file. Windows will allocate virtual memory proportional to the cache size, but actual RAM usage depends on query patterns. For most company files, 512 MB of available RAM is sufficient.

Concurrent Queries

• Cache reads: Fully concurrent. Multiple clients can query simultaneously without blocking.
• QBFC live fetches: Serialized through the COM SDK. Only one live query executes at a time.
• Writes: Serialized through the COM SDK.

For maximum throughput, rely on the cache for reads and minimize live fetch usage.

Monitoring

Check query execution times in the Syntra log:

C:\ProgramData\SyntraODBC\logs\syntra.log

Look for entries with query_ms to identify slow queries. Queries taking more than 100 ms from the cache may indicate complex JOINs or very large tables.