Often, a SQL query is just one part of a larger application, and it's difficult to optimize it without understanding the context in which it's used. This is where the comments and tags in the SQL query come in handy when combined with EverSQL Performance Sensor.
By including information such as the component, class, and line number in the query's comments, developers can quickly identify which part of the code the query belongs to. This makes it easier to track down the relevant code and understand the context in which the query is being used.
How to tag queries (comments syntax)
EverSQL Performance Sensor will automatically analyze commented tags from SQL queries headers and assign them to your queries. The tags, which are in the form of "key=value" (no spaces in value fields) are extracted from the top one-line comment, and the tags are auto assigned to the query.
The comment formats supported are -- tags and /* tags */
For example:
-- component=auth class=perm line=241 SELECT * FROM tbl ....
OR
/* component=auth class=perm line=241 */ SELECT * FROM tbl ....
Once tags are available, you can easily filter queries based on the tags assigned to them. For example, if a user wants to see all queries related to a specific component, they can filter by the "component" tag. If they want to see all queries related to a specific class or line number, they can filter by the "class" or "line" tag respectively.
The tags are extracted automatically from the top comment of a SQL query, so users don't need to do anything special to enable this feature, other than adding the comments to the top of the SQL queries.
The queries can be sent to EverSQL from any submission path, whether it's a single query submission to EverSQL, the performance sensor, or the optimization API.
This new capability makes it easier for users to organize and analyze their queries, which can help them identify performance issues and optimize their SQL queries. By filtering queries based on specific tags, users can quickly find queries that are related to a specific component, class, or line number, which can save them time and effort in their performance analysis.
Demonstration of auto-tagging via SQL comments
Tracking origin of ORM-based SQL queries
If you're using an ORM framework (such as Hibernate, Django ORM and others), you're probably looking for a more automated way to auto-tag all queries generated by the framework. This can be done easily using Google's free and open source library SQLCommenter.
Google SQLCommenter is a library that can be integrated once in your application, and enables developers to automatically tag SQL queries. The library will automatically add comments to any framework-generated SQL query, with information about the framework, controller, route and more details about the origin of the query. More information about the usage can be found here.
The library supports MySQL, PostgreSQL and MariaDB databases, with support for a wide list of ORM frameworks such as Django, KnexJS, Sequelize, SQLAlchemy, Hibernate, Express, Spring, Flask, Rails, Laravel and more.
To enable auto tagging, you can just integrate SQLCommenter as part of your application, and comments added by the ORM framework will be automatically tagged and added to your EverSQL dashboard.