Database XP / Operating

Slow is usually one query, not the database

Measure before changing anything.

Reports of a slow database are usually reports of a slow application, and the cause is usually a small number of statements rather than a general condition. Tuning configuration or adding hardware before identifying them is expensive and frequently changes nothing.

Every mainstream database can report the statements consuming the most total time, and that report resolves the question in minutes. The result is often surprising: not the complex query anybody suspected, but a trivial one executed a hundred thousand times because it sits inside a loop.

That pattern, one query per row where one query would do, is the most common performance defect in application code and it is invisible in the application because each individual call is fast.

The other frequent finding is a query whose plan changed because the data grew past a threshold. It was fine for two years and is now scanning, and nothing in the code changed, which is why nobody suspects it.

The other common cause of an unexplained slowdown is statistics rather than data. A table whose statistics have not been updated after a large change gives the optimiser a false picture, and the resulting plan can be dramatically wrong while nothing about the query or the schema has changed at all.