145
Views
2
Comments
How to find a query on the code when looking at database executed queries
Discussion

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.

mvp_badge
MVP
Rank: #72

Nice feature Fernando! I will be checking this out!

Thanks for sharing :-)

Rank: #403

Hi Fernando,

I had found this post very useful in terms of issues developers encounter generally with the performance of the different queries build and used by them into the different projects. Also, agree with you that whenever we make request for help from DBA they send the statistics or graphs which would never be easy for a developer to understand and to find the solution based on that. But, this feature would really help to developers.

Thanks a lot for this informative post!

Kind Regards,

Sachin