SQL Server Indexing for IDO Queries
The IDO runtime translates every LoadCollection request into a SQL SELECT statement against the underlying tables and views. Performance depends heavily on SQL Server's ability to use indexes for the WHERE clause, JOIN conditions, and ORDER BY columns. Analyze the most frequent IDO queries using SQL Server's Query Store or Extended Events, then create targeted indexes that cover the filter and sort columns.
For custom IDOs on user-defined tables, design the indexing strategy during table creation rather than reactively after performance complaints.
- Create covering indexes on columns used in IDO filters: CREATE INDEX IX_Uf_Orders_Status ON Uf_Orders(Status) INCLUDE (OrderDate, CustNum)
- Review the SQL execution plan for each high-frequency IDO: look for Table Scan and Key Lookup operators
- Add indexes on RowPointer for all custom tables; SyteLine uses it extensively for concurrency checks
- Index foreign key columns to speed up IDO joins: e.g., index CustNum on order line tables
- Avoid over-indexing: each index adds overhead to INSERT and UPDATE operations on the same table
IDO-Level Caching and Request Optimization
SyteLine supports several caching mechanisms that reduce redundant IDO calls. The IDO runtime caches metadata and extension class instances, but data caching must be implemented at the application level. For static or slowly changing reference data (units of measure, warehouses, status codes), implement application-level caching in your extension classes using ConcurrentDictionary or MemoryCache.
On the request side, always specify minimal PropertyList, apply selective Filters, and set appropriate RecordCap values to keep response payloads small.
- Cache reference data in extension classes: static ConcurrentDictionary<string, string> for UOM lookups
- Implement cache expiration: refresh cached data every 15-30 minutes or on UpdateCollection events
- Reduce PropertyList to exactly the columns needed by the consuming form or integration
- Pre-filter on indexed columns: never rely on client-side filtering after loading large datasets
- Use RecordCap with OrderBy to implement efficient server-side pagination for large result sets
Monitoring and Continuous Performance Management
Performance optimization is an ongoing process, not a one-time effort. Establish baselines for your most critical IDO operations and monitor them continuously. Use SQL Server Query Store to track query plan regressions over time.
, 2 seconds for interactive forms, 30 seconds for background processes). Regularly review the top N slowest IDO queries and address them proactively before users escalate complaints.
- Baseline top 20 IDO queries by execution frequency and average duration from Query Store
- Set SQL Server alerts for queries exceeding 5 seconds in the SyteLine application database
- Monitor IDO Runtime performance counters: Requests/sec, Average Request Duration, Queue Depth
- Schedule monthly review of Query Store top resource consumers and investigate regressions
- Use SyteLine's built-in Performance Monitor utility to correlate slow forms with specific IDO operations
Try it here, free
SyteLine IDO and SQL Performance Assessment
Score the performance risk of a SyteLine 9 or 10 on-prem install across data growth, IDO and extension code, background tasks and SQL Server configuration, with the specific fix for each weak answer.
Netray AI agents continuously monitor your SyteLine IDO performance, identify degradation patterns, and recommend targeted optimizations. Get proactive performance management for your ERP.