Database performance from the GeneXus perspective

Official Content
This documentation is valid for:

When you are coding, you often find that a program can be coded in different ways to do the same job. So it is important to understand how to improve your GeneXus coding to get the most of it and make sure the generated code is optimized.

This guideline resumes some helpful tips to take into account in relation to the GeneXus language, associated coding and what to do next if you want to improve the database access for certain navigations.

Detailed navigation

Enable a detailed navigation report.

Warning messages

Carefully review all warnings detailed on the navigation detail.

For Each Optimizations

Based on your code, check if you can apply any of the following optimizations.

Index and Order

Check index and Order usage for filtering data. If needed, create new indexes but be careful; more indexes on a tables will slow the INSERT, UPDATE, DELETE operations.

See also

Server Paging

Use Server Paging whenever possible for grid navigation.

Enable management

Enable the Enable Management property and monitoring your application to analyze SQL usage, number of executions and related parameters to know on what statements to concentrate the tuning.

DBMS specific

To run the database economically and optimally you need to identify the long-duration queries, isolate them, examine the query execution plan, analyze the individual execution steps and change the steps as required to improve the performance of the system.

Each DBMS comes with a host of performance monitoring / tuning tools. If you need to go further to effectively optimize some queries, check you DBMS documentation and follow these steps:

  • Identify the high load or top SQL statements that are inefficient and are responsible for a large share of the application workload and system resources.
  • Verify that the execution plans produced by the query optimizer for these statements are performing satisfactorily.
  • Implement corrective actions to generate better execution plans to rectify the poorly performing SQL statements.

Network I/O

If you have long batch processes execution, take into account how your program will be deployed and where it will be executed.
If your application runs in a different location from your database server; your performance may be decreased.

Dynamic Translation

Whenever possible use the bang character (!) to minimize the usage of the dynamic translation functions only when is needed.
Another option is to use the Translation Type environment property with the "static" value.

See Also

What do we talk about when we talk about performance
Performance problems diagnosis in Java applications
Stress Test Tips
Blocking clause in a 'For each' command
Order Clause Specification
Navigation Reports for Procedures, Web Panels and Data Providers
Navigation Report for Transactions