Performance Profiler
The WEM Query Performance Profiler helps identify and analyze performance bottlenecks in data queries, providing insights to optimize application efficiency.
Last updated
Was this helpful?
The WEM Query Performance Profiler helps identify and analyze performance bottlenecks in data queries, providing insights to optimize application efficiency.
Last updated
Was this helpful?
This functionality is only available in the DevOps environment for private clouds.
The WEM Query Performance Profiler is a powerful tool designed to identify and analyze performance bottlenecks related to data queries. It monitors all read and write operations to SQL databases, OData sources, and transient data, providing insights into query execution times and highlighting potential performance issues.
To access the Performance Profiler, follow these steps:
Navigate to the Applications tab within the DevOps environment.
Locate the application you want to profile.
Click the three dots (⋮) on the right to open the context menu.
Select Performance Profiler from the context menu. This will open the profiler overlay.
Note: Ensure you are selecting the context menu for the application that corresponds to the hostname of the environment you wish to test.
The first overlay you'll encounter is the session overlay, where you can manage profiling sessions to monitor query performance. The Performance Profiler uses sessions to track queries, allowing you to:
Start a New Session: Begin a new profiling session to monitor query performance in real-time. You can choose to profile all activity or filter the session based on a specific client IP address. This filtering helps in isolating performance issues related to particular clients or users.
View Active Sessions: See currently active profiling sessions and their statuses.
Access Historical Data: Review past profiling sessions to analyze trends and identify recurring issues.
When a session is created the follow screen appears:
Once a session is active, the Profiler will collect data on query execution times and the nodes on which they are executed. Use this data to:
Identify Slow Queries: Pinpoint queries that are taking longer than expected to execute.
Locate Bottlenecks: Determine which expressions or components are causing slowdowns.
Understand Queries: Use the information from the performance profiler to better understand how queries are optimized and how to leverage this better.
Optimize Queries: Use the insights gained to optimize queries and improve overall application performance.
The Profiler provides detailed reports and visualizations to help you interpret the performance data. There are a few different parts of the performance profiler to focus on when analyzing query results. The follow image will be used to go through the different parts of the profiler:
At the top you find information about the project the query profiler is running on. This holds the portal id, project id and hostname.
On the left you have a feed of all queries executed by the application during the profiling session. Each item has the type of action, ID of the node where the query is executed and the total time the query took in seconds. The following list has some types that can be expected:
Get row: used when a loop was not able to prefetch the right rows because of a inefficient expression
Update: Used when the "save directly to database" property is used with import nodes for example.
Query: Default query items.
Bulk copy: Used by the copy data node.
Commit changes: When a save database changes node is passed.
Delete: For deleting items directly from the database.
Get descendant row id's: When you have a row position on a nested list and the parent is removed.
Get File: For retrieving specific files or when showing.
The query inspector has four sections for different parts of the query process.
The general information includes the type of process, the timestamp of query execution in UTC, the total execution time, the node where the query was executed, and a trace ID.
Details section This section depends on the type of activity selected in the query feed. Generally, this section contains information on the source list, including the list ID. If a filter is used, it provides the filter ID. When paging is involved, the "limit to parent" setting indicates whether the query is restricted to a parent or applies to the full list.
Different types of activities provide different information in this section. For example, the "CommitChanges" type will show the number of inserts and updates, indicate whether an activity was successful, and display how many retries were needed, for example.
Query execution details This section details the execution of a query on the runtime server. This is the optimized query that can be directly performed on the database. The execution time displayed here is one of the key aspects to examine. Another important detail is the number of rows the query processes, both individually and in total.
The image above shows the SQL query from the start of this section. This shows the actual SQL query as is executed on the database. Every item in this query displayed as f12345 corresponds with a field from the datamodel with the number as ID. This can be used to find the field using the search of the modeler data tab. The table names are usually the same as the list names in the modeler datamodel, with a prefix "tbl".
Query post processing details The post-processing section represents the remaining tasks that the database service could not handle. This part of the process is executed within a user's session and can significantly increase processing time.
In this section, the execution time, number of rows, and total row count can indicate inefficient processes that may cause slowdowns. When the amount of rows goes up the time this part will take can exponentially increase causing the application to slow down quickly. The remaining filter expression can also cause issues. If this query is too inefficient, it will significantly contribute to long execution times.
Optimize SQL Compatibility:
Ensure your expressions are SQL compatible. This means that parts of the expression can be transformed into native SQL statements, allowing the database server to handle them directly. This reduces the need for retrieving excessive data and executing additional expressions in memory to further narrow down the results.
Avoid Complex Expressions:
Simplify expressions and avoid using complex calculations within SQL queries. Perform calculations in your application logic instead of within the query to reduce the load on the database server.
Batch Updates and Inserts:
When performing multiple updates or inserts, batch them together instead of executing them one by one. This reduces the overhead of multiple database round-trips. Also, make sure to fine-tune the batch size—sometimes a batch size of 250 records is optimal, while in other cases, you can go up to 5,000. It depends on the situation, so ensure that your batch size is parameterized within your flows, and experiment with different settings to find the optimal value.
Monitor and Profile Queries:
Regularly use the Performance Profiler to monitor query execution times and identify bottlenecks. This helps in pinpointing slow queries and optimizing them.
The WEM Query Performance Profiler is an essential tool for maintaining optimal performance in your applications. By regularly using the Profiler, you can ensure that your data queries are efficient and your application runs smoothly.
