Schema validation checks that your entities match the database. Statement advice checks how the application uses them. Storm builds every statement, so it knows the shape, the entity graph, the indexes and what each transaction repeated, and the log says what to change, with the fix and a link to the trade-off.
A pet clinic application passes its tests on a small database. In production, page two of the owner list shows an owner who was already on page one, the reminder job takes minutes, deleting an owner takes seconds, and the connection pool runs dry when the front desk opens. Each problem is visible in the code, on one specific line. You want to find those lines while you write the code, and have production point at the rest.
Storm does not parse logs or guess from query text. It builds the statements, so it has four kinds of evidence:
distinct over a join that repeats rows, a deep offset, a page that counts every row.None of this slows the statements down. Advice on a statement's shape is decided when Storm builds the statement and is cached with it, so a statement without advice pays one check. The index checks run only for an execution that was already slow. Repeats are counted only while the advice log reports performance advice, which is during development. Watching transactions for idle time costs two clock reads per transaction.
In development there is nothing to do. Spring Boot with DevTools, Ktor in development mode and every @StormTest report all three categories: correctness, performance and design. In production, correctness advice is on at INFO, because rows in no stated order are wrong wherever they run. Performance and design advice reach production on the lines of the slow statement log, once you set a threshold:
# application.yml storm: sql-log: slow: threshold: 200ms # a slow line carries all advice its statement has advice: categories: correctness # the default outside development logging: level: st.orm.sql.advice: INFO # once per piece of advice; DEBUG: once per statement shape
At INFO the advice log reports each piece of advice once. At DEBUG it reports it once per statement shape, which gives you a list to work through after a test run.
The front desk wants every owner with a pet called Leo. A join plus distinct is the usual way to write that:
// Owners with a pet called Leo val owners = orm.entity(Owner::class) .select() .innerJoin<Pet>().on<Owner>() .where(Pet_.name eq "Leo") .distinct() .resultList
In development, the advice log reports it the first time the statement runs:
SQL advice (SELECT Owner): 0.46 ms in database, 3 rows, OwnerService.kt:42 SELECT DISTINCT o.id, o.first_name, o.last_name, … FROM owner o INNER JOIN pet p ON p.owner_id = o.id WHERE p.name = ? shape: 919b615157cb9fda advice: the join to Pet repeats each row once per related row, and DISTINCT removes the copies afterwards. fix: semiJoin(Pet) filters by Pet without repeating rows, so the query needs no DISTINCT. see: https://orm.st/docs/advice#distinct-over-join
The join returns an owner once per matching pet, and DISTINCT sorts or hashes the whole result to remove the copies. A semi-join keeps each owner that has a matching pet, once:
val owners = orm.entity(Owner::class) .select() .semiJoin<Pet>().on<Owner>() .where(Pet_.name eq "Leo") .resultList
SELECT o.id, o.first_name, o.last_name, … FROM owner o WHERE EXISTS (SELECT 1 FROM pet p WHERE p.owner_id = o.id AND p.name = ?) -- each owner once, nothing to remove afterwards
Every line has the same layout: what the statement costs, a fix:, and a see: link to a section that explains the trade-off. The links stay valid as long as Storm gives the advice. The same mechanism catches a limit without an orderBy (correctness), an offset of a thousand rows or more (use scroll), and an IN list that runs in many lengths and fills the database's plan cache.
No single statement shows an N+1 loop. Every lookup is fast; there are just forty of them. Storm counts the statements on each transaction's connection and reports the repeats when the transaction completes:
class VisitReminderService(private val pets: PetRepository) { fun remindersDue(petIds: List<Int>) = transactionBlocking { // line 41 petIds.map { id -> pets.getById(id) } // line 52 .filter { it.dueForVaccination() } } }
SQL advice (transaction): VisitReminderService.kt:41 advice: getById of Pet ran 40 times, 120 ms, 3.0 ms each, one row each. at: VisitReminderService.kt:52 -> PetRepository.getById fix: findAllById over the keys reads the rows in one round trip. see: https://orm.st/docs/advice#repeated-select
The block is headed by the line that began the transaction, and at: names the loop and the repository method it called, in the direction of the call. The fix names the list form of the call. The same block reports a Ref fetched once per row, and a single-row insert or update run in a loop instead of as one batch. A repeat that cost less than 50 ms in total carries no advice, because most short repeats are correct and would bury the ones worth changing. Loops that open a transaction per item show up in the performance log summary instead.
Whether an index is worth having depends on how many rows a table holds and how few a condition selects, and Storm reads no row counts. So index advice waits for a slow execution and then checks the columns the statement used against the indexes the database reported. Deleting an owner is a good example, because the statement never names the table that makes it slow:
SQL slow (DELETE Owner): 2840 ms in database, 1 rows, OwnerService.kt:77 DELETE FROM owner WHERE id = ? shape: 3f0a6c1d92be4471 parameters: 1 advice: every row deleted from owner is checked against the foreign key pet(owner_id), and no index on pet starts with owner_id, so the database reads pet in full for each deleted row. fix: an index on pet(owner_id) serves the check. see: https://orm.st/docs/advice#unindexed-foreign-key
The database checks every foreign key that references owner. Without an index on pet(owner_id), each check reads all pets. For a statement that filters, groups or sorts by a column no index starts with, missing-index advice names the index to create, with the column order that also serves the sort or the grouping. A search that reads every row gets unindexed-search advice naming the index that serves it, such as a trigram index on PostgreSQL. The missing-index advice is tested against every supported database: a conformance test per dialect creates the index the advice names and reads the database's plan to confirm it is used.
This one has its own threshold and runs in production. A transaction whose statements are all fast can still hold its connection, and the row locks of its writes, for seconds:
fun book(visit: Visit) = transactionBlocking { visits.insert(visit) mailer.sendConfirmation(visit) // a 2-second SMTP call inside the transaction }
SQL slow (transaction): held its connection 2300 ms, 4.0 ms in its statement, wrote, VisitService.kt:42 advice: the transaction held its connection 2300 ms, 2290 ms of it idle after its last statement, with its row locks held for 2290 ms of that. last statement: the INSERT of Visit fix: the work after the INSERT of Visit can run after the commit; an onCommit callback runs it there. see: https://orm.st/docs/advice#idle-transaction
Storm times when the transaction took its connection and when it returned it, and subtracts what the statements spent. The advice says where the idle time went, before, between or after the statements, and the remedy follows from that. Here the mail goes after the commit:
fun book(visit: Visit) = transactionBlocking { visits.insert(visit) onCommit { mailer.sendConfirmation(visit) } // runs once the visit is committed }
A transaction may hold its connection idle for one second by default; storm.sql-log.slow.transaction changes that.
Every inline foreign key joins its table into every read of the entity. Graphs grow over time, usually through entities nobody was editing. In development, Storm checks each entity once against a design budget of 10 joins and 96 columns beyond its own:
SQL advice (design Visit) graph: Visit(Pet(Owner(City), PetType, Clinic(Address, Organization(Country)))) 12 joins, 140 columns beyond its own advice: every read of Visit joins 12 tables and selects 140 columns beyond its own, past the design budget of 10 joins and 96 columns. fix: a Ref for Clinic.organization removes 4 joins and 52 columns. see: https://orm.st/docs/advice#entity-budget
The advice names the field where a Ref brings the read back within budget, choosing the deepest field that is enough on its own. It never names an identifying foreign key or a lookup table. Within the budget, only real use advises: a slow read of a wide entity says what a Ref would save, and a Ref fetched in every unit of work says it should be inline. See Entity Design.
Advice is a suggestion. Where the code is as intended, accept it by its anchor, the part of the link after #:
interface OwnerRepository : EntityRepository<Owner, Int> { // The admin screen shows "page 3 of 120", so it needs the total. @AcceptAdvice("page-count") fun adminList(pageable: Pageable): Page<Owner> = select().page(pageable) }
@AcceptAdvice works on an entity, a repository, a class or a method, including a service method whose lambdas and transaction { } blocks issue the statements. Whether a shape's advice is accepted is decided once per shape. The slow statement log still prints accepted advice, since a slow statement is a reason to look again. To turn one piece of advice off everywhere, set its logger to OFF, such as st.orm.sql.advice.deep-offset.
| Advice | Category | Reported by |
|---|---|---|
unordered-limit | correctness | advice log, everywhere |
distinct-over-join, deep-offset, in-list-shapes | performance | advice log in development; every slow line |
repeated-select, repeated-write, repeated-fetch | performance, design | advice log when a transaction completes; performance log across transactions |
missing-index, unindexed-foreign-key, unindexed-search, page-count | performance | slow statement log |
idle-transaction | performance | slow statement log, against its own threshold |
entity-budget | design | advice log, once per entity, in development |
writes-without-transaction | correctness | performance log summary |
The lines are written for a developer and for a coding agent alike. An agent running your tests reads the same advice, with the fix and the link, and can act on it without being told what to look for. See Agentic coding.
The reference documentation covers every piece of advice and its trade-offs: