Queries, Connections and Performance

Four things that prevent most problems

1. Connection pooling — opening a connection per request does not scale
2. Parameterised queries, always, without exception
3. Explicit relation loading rather than lazy loading in loops
4. Indexes on the columns you filter, join and sort by

Lazy loading inside a loop is the most common performance problem in any framework with an ORM. One query becomes four hundred and nobody notices until the data grows.

Query multiplication is the usual cause

Fetching a list and then accessing a related object per item issues one query per row. On ten rows nobody notices; on a thousand the page takes twenty seconds.

• Load relations explicitly with the parent query
• Log query count per request during development
• Set a threshold that fails a test if exceeded
• Check the count on any page that handles lists

Transactions where they matter

Anything that writes several related records should do so in a transaction, so a partial failure does not leave inconsistent data.

Keep transactions short. A transaction held open while calling an external API blocks other work and can exhaust the connection pool.

Migrations need discipline

Watch the growth

Queries that were fast at ten thousand rows can be slow at a million. Applications frequently slow down with no change in usage, purely because data accumulated.

Monitor query duration over time, and archive data that is no longer needed in the working set.

Frequently asked questions