Find slow SQL in OrmLite: db.Explain and the Slowest Queries view
ServiceStack v10.4 adds db.Explain() to OrmLite, which returns the query plan of a typed query without running it. The Profiling Admin UI also gets a Slowest Queries view where you can explain, analyze and re-run an app's slowest SQL from the browser. Together they make it easier to spot missing indexes.
Key points
- db.Explain(q) returns the query plan as text without running the query; use ExplainAsync for async code.
- analyze: true executes the statement, so only use it on SQL that is safe to run, such as SELECT.
- A full table read such as SCAN Book or Seq Scan signals a likely missing index; add it with the [Index] attribute.
- Assert on the plan text in a test to catch a dropped index before it reaches production.
- ProfilingFeature.SlowQueriesLimit keeps the 50 slowest queries by default; set it to 0 to disable.
- AdminDatabaseFeature.RunQueryLimit controls how many rows Run Query returns (default 20).
A query can be fast on a developer machine and slow in production, and the SQL text alone rarely shows why. ServiceStack v10.4 adds db.Explain() to OrmLite. It takes a typed query and returns the database's query plan as text without executing it. Passing analyze: true runs the statement and adds the actual rows and timing for each step, which helps spot missing indexes and wasted table scans.
The Profiling Admin UI also gains a Slowest Queries view that lists the slowest database calls since the app started, with timing, connection name and SQL. With the Database Admin plugin, each captured SELECT can be explained, analyzed and re-run from the browser. These actions require the Admin role, run in a rolled-back transaction, and only work on queries the app has already executed, so SQL cannot be typed in by hand.
Real-world use
- Investigating a slow list page against real data to find a missing index on the filtered column.
- Adding a regression test to a repository that fails when a query goes back to a full table scan.
- Monitoring a live ServiceStack app's slowest SQL queries from the Profiling Admin UI and checking fixes in place.
Why it matters
Teams using OrmLite can now read query plans from code and inspect slow queries in the browser without opening a database console. Adding plan assertions to tests catches a dropped index before it reaches production.