The “SetFilter” Query Trap: Why Wildcard Text Filters Destroy SQL Index Seek Plans
A custom customer search API was timing out under load. Each lookup by name spiked SQL CPU to 100% and dragged query times from 50ms to 8+ seconds.
The hidden culprit? Using SetFilter() with leading wildcards (@*text*). SQL Server can’t use the secondary index on Name when the filter starts with a wildcard, so the optimizer abandons index seeks and runs a full table scan across millions of records.
⚠️ The Problem: The Leading wildcard index killer
When you write Rec.SetFilter(Name, ‘@*%1*’, SearchTerm), the AL platform translates this into a SQL LIKE ‘%term%’ clause (with case-insensitivity handling).
Because the wildcard is placed at the beginning of the search string, B-tree indexes on SQL Server become completely useless for location lookup. The database engine cannot perform an Index Seek; it must traverse every single page in the table schema using an Index Scan or Table Scan. Multiply this by thousands of daily API calls, and your database throughput plodes into an O(n) execution wall.
🛠️ The Fix: Prefix filtering with SetRange or Full-Text indexing constructs
Shift your default search patterns from substring matching (*text*) to prefix matching (text*) whenever possible. Using Rec.SetFilter(Name, ‘%1*’, SearchTerm) or Rec.SetRange(Name, SearchTerm) allows SQL Server to perform an index-seek, jumping directly to the matching root node in the B-Tree.
If deep, substring-level full-text searching across large tables is an absolute business requirement, bypass standard SetFilter() loops on main tables. Instead, leverage Search Indexes, BC Search Pages, or offload full-text indexing queries to dedicated search endpoints or temporary memory buffers (Dictionary).
Technical Grounding
➡️ SaaS Available: Yes
➡️ Runtime/upgrade: Runtime
➡️ Bypasses Triggers: No, standard filtering operates cleanly at the database protocol layer without bypassing record triggers.
Why It Matters (The Senior Impact)
✅ Instant B-Tree index seeks: Replaces O(n) full table scans with O(log n) index seeks, reducing SQL execution times from seconds to milliseconds.
✅ Minimal SQL Buffer Churn: Prevents SQL Server from loading thousands of unrelated data pages into memory buffer pools just to evaluate string matches.
✅ Lower Telemetry Cost: Eliminates high-duration Long Running SQL Query telemetry warnings in Application Insights.
☣️ The Trap
Even with @ case‑insensitive modifiers (e.g., @Value*), index seeks can fail if the SQL column collation isn’t case‑insensitive. Always test telemetry to confirm seek behavior.
💡 The Bottom Line
Leading wildcards are a silent performance tax. Keep filters prefix‑based, preserve your B‑Tree index seeks.