Trace SQL Query in Rails

Tram Ho

1. Introduction

When developing or optimizing Rails applications, one of the important jobs is understanding and optimizing SQL queries because most of the slow web speed is due to inappropriate DB query / processing logic. We are going to ask questions like: How many SQL queries are called after each request, how long does it take to complete an SQL query? Are the query repeatedly called in different places in the code? No ?, which query is called more times than others?
Normally, we usually find them by checking log files, but if we want to analyze a request in detail or if we want to track multiple requests at the same time, it will be difficult for us to find and filter out the messages. related information in log file.
To answer those questions, Steven Yue created a gem called sql_tracker . In this article, we will learn how the sql_tracker gem works and its features to help track and analyze SQL queries.


Basically, sql_tracker uses instrumentation API to track SQL queries called from Active Record through the monitoring of the sql.active_record hook:

Instead of using a block, we can provide an instance object, implemented by the method call:

sql_tracker is declared and tracks queries when a Rails process runs, and stops tracking when the Rails process finishes.


We may not want to listen for all the queries. For example, we just want to track SELECT statements of queries, or we want to track queries that are called with a certain directory.
By default, sql_tracker tracks and record all 4 commands (SELECT, INSERT, UPDATE and DELETE), but we can change it by changing the settings tracked_sql_command

By default, sql_tracker tracks the app directory and lib folders to ignore unrelated queries (eg queries called from the tests folder).


sql_tracker will try to simplify and assemble queries by replacing the special value with xxx. For example, if we execute the following SQL query:

And then another query:

sql_tracker will combine them into 1 query:

sql_tracker uses regular expressions to rewrite SQL queries. Here for example, it has found and replaced values ​​after operators like =, <>,>, <and even BETWEENAND … Once grouped, it becomes easy. It’s easier to analyze queries, and calculate total queries, average time, and more.


sql_tracker keeps all data in memory, and exports data in a JSON file when the Rails process finishes. All data contained in a hash, the format will have the form:

When the keys are md5 or the sql queries have been simplified, and the values ​​are full sql query + some statistical information.
By default, the file will be saved in the tmp directory in Rails roots folder, you can also change it using config:

If we have used the puma app server, and set more than 1 worker, we will see more than 1 JSON output file in the output folder because each worker will track and store data separately.


Finally, we can generate the report using 1 or more JSON dump files:

The report will take the form:

It will show us the total number of calls for each query, the average time of each query, and which line of code calls that query. From this report, we can understand how each query was called, where to call it, … If simple queries are called too many times, think of a solution to caching it. And if the same query is repeated in different places in the source code, it is time to refactor the code.
Usually, we choose some controller tests or integration tests to run, and after the tests have run, open sql_tracker tmp/sql_tracker-*.json again to dumped data and check the queries.


Thus, the article has learned about tracking query in rails with gem sql_tracker. Hope the article helps everyone to debug or improve the quality of their Rails application. See you again!

Reference .

Share the news now

Source : Viblo