How to find a query on the code when looking at database executed queries

Developing apps is an art in various ways. From building beautiful UI and experiences to making complex calculation engines and business rules, there's a bit of everything. And don't forget about databases and all the queries that are executed. All pieces play a big part in an app's success.

The goal of every app is to be loved by users, and for this to happen, it must provide a friendly and easy to use experience, be reliable and behave as expected, and most importantly, it must be fast. To achieve this, the development team must rely on good product designers that can build a top-notch experience and good developers that can assemble performant applications. On top of that, a solid development process that keeps gateways for code validation to avoid bugs is essential.

While this is a fascinating and important topic, here I'll focus on the role of reliability and performance-related to database troubleshooting.


Improve performance through query optimization

To properly troubleshoot or optimize at the database level, you'll have to look at query execution on the database server and check elements such as deadlocked queries, execution time, and frequency. OutSystems provides the ability to check queries performance through Service Center - Analytics slow queries report. Also, performance issues can be pinpointed on Lifetime Analytics, where you can drill down to see what are the queries that run to load specific screens.

While some information can be obtained directly through OutSystems console reports, there are situations in which you may need to dig deeper and get insights straight from the database server.

Detailed query execution information can be gathered on the database engine using SQL Server Profiler, SQL Server Management Studio, or Oracle SQL Developer. Additionally, you can use third-party tools such as Solarwinds.



However, the information that you get from a DBA team or SQL Profilers is often the query itself and its statistics, which is not a big help to trace back to the code when you have a factory of more than 600 modules. Developers may end up spending the majority of their time trying to identify where on the code is that specific query rather than being focused on optimizing the query.

That's when the new OutSystems Query Origin information feature comes to the rescue!

When you turn the new Query Origin Information feature on, each executed query will have a commented breadcrumb with the path where you can find the query on the code.

<module_name>.<action/screen/other>.<aggregate/sql_element>

If you need help enabling this feature, check Trace Executed Queries Back to Your OutSystems Applications.

Nice feature Fernando! I will be checking this out!

Thanks for sharing :-)