With new-relic transaction traces, I detected a N+1 query (n plus one query) issue in one of my API endpoint. Meaning that for every row return by a SQL query, I do another query per row to enrich the data.
More reading on this problem here https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89
To detect this issue, I looked at the count of database call on a particular query. In the picture, we can see 25 calls to Postgres job_application.select, which is not normal in this case.
I was wondering wether it was possible or not to detect this automatically with a query.
I played a bit with query builder but got nothing really useful.
What I tried so far:
- Look for transaction with many database count, but this is not the only sign of N+1, indeed, we’re looking at high count of database call on the same query, which is not the case here
SELECT databaseCallCount, databaseDuration, appName, name FROM Transaction WHERE databaseCallCount > 25 ORDER BY databaseCallCount DESC
- I also try to look at metrics with filter on
metricTimesliceNamebut I didn’t understood exactly how it worked
I know some APM like appSignal have this feature, and I’m quite sure I can find a way with new-relic, but I’m stuck for now