Test that a database query is fast enough
Add a time budget to a SQL step of a zriz pipeline. The step fails when the query needs more milliseconds than your limit.
The problem
A query is slow when an index is missing or a table grows. The result stays correct, so a test of the rows does not see it. The page that uses the query gets slow.
The test
The example is a shop API with a MySQL database. The query counts the rows of the table users. It must take under 50 ms.
Start from a project that zz init made: the quick start gives the commands. Add the database value and the database resource.
.zriz/environments/local.json:
{
"values": {
"TARGET_URL": "http://localhost:9080",
"SHOP_DB": "user:pass@tcp(localhost:3306)/shop"
},
"sensitive": ["SHOP_DB"]
}
.zriz/resources/shopdb.json:
{
"type": "sql",
"connection": "${env.SHOP_DB}",
"read-only": true,
"actions": {
"count-users": { "query": "SELECT COUNT(*) AS total FROM users" }
}
}
.zriz/pipelines/query-is-fast.json:
{
"description": "The user count takes under 50 ms",
"steps": [
{
"call": "shopdb/count-users",
"expect": [
["row-count", "==", 1],
["timing.target-ms", "<", 50]
]
}
]
}
Run it
zz run query-is-fast
"status":"pass"
What it proves
-
timing.target-ms < 50: the query and the read of the rows were fast enough. A slow query fails the step. The reason isassertion:timing.target-ms is 212 and the pipeline expects 50. -
row-count == 1: the query returned its result. A query that returns nothing fast does not pass. -
The runner measures the time next to your database, in your network.
-
The number has no time for the connection and none for the prepare of the statement. A query with a syntax error has no number.
Common mistake: A small test table is always fast. Use a database with data like production.
Next
- Test the response time of an API
- Pipelines gives each rule of the time budget, also for browser and command steps.