Find the Slowest Queries Accessing a Table

A new macro - ‘Slowest SQLs using this table’ is available in Visual Expert 2019 to find out which SQL queries are taking longer to execute.

This helps in eliminating or modifying the slower performing SQLs in order to improve the overall performance of code.

How is this different from traditional DBA tools?

DBA tools can identify slow queries affecting DB response times. But this information is often useless for developers, as they usually can't locate the origin of this query in their code. In such cases, poor performing SQLs remain unchanged in the code.

By combining SQL execution statistics with a static analysis of the code, Visual Expert can go further and pinpoint the procedures or functions where the SQL should be improved.

1. Select a table. Click on ‘Slowest SQLs using this table’ in the ‘Performance’ section of the navigation bar.
   
   



2. You can also access the option “Slowest SQLS” from the ribbon menu in the Performance Tab.
   
   



3. As a result, the SQLs are ranked from slowest to fastest.
   
   



4. In order to find the code where a SQL comes from, select it and click on “Locate” in the navigation bar
   
   

This feature comes with 2 parameters to let you customize the result (click the small wrench on the right to set them):

• Number of the item displayed in the result (20 by default)
• Minimum execution time, to show only SQLs with a significant response time

Pre-requisites:

1. Your VE project should read database code via a database connection (as opposed to reading DB code from files)
2. Activate VE performance monitoring
3. Wait for your database to execute SQL queries and log some execution statistics.

Read More

This article will tell you more about analyzing and optimizing the performances of your code.