Organizations may feel some anxiety when discussions arise around querying data from SAP. They may be concerned that these queries might generate excessive network traffic, potentially leading to performance problems.
With Studio's Query module, developers have the option to control the network activity caused by the query by using the Adaptive Query Throttling (AQT) capability. What is AQT?
Adaptive Query Throttling
One major performance concern around giving users the ability to create queries against the live transactional system is that the query execution could take substantial computing resources that might compete against regular SAP transactions.
Automate Query addresses this issue in a unique way by allocating only available computing resources to execute queries in the live SAP system. Using a patent-pending Adaptive Query Throttling™ technology, Query dynamically optimizes the execution of the queries based on the current load on the SAP server at any given time. The available server resources are calculated from the total number of open dialog processes. When over 50 percent of the dialog processes on the SAP system are available, the query is executed at full speed. However, when the available server load starts falling below 50 percent, the execution of the query is slowed down.
The graph below displays the query performance time against the system load with or without the adaptive query throttling algorithm applied. As this clearly shows, when the throttling algorithm is applied, the query execution slows down as the system load is increased. And without the algorithm, the query executes in constant time competing for resources with other transactions on the system.
Scheduled query extraction
Even with the Adaptive Query Throttling feature, a long-running query should ideally be scheduled to run at off-hours.
Automate QUERY allows users to schedule queries so that these queries can be run at off-hours or at pre-set times.
AQT - Did you know?
- When a developer writes a query script, using AQT may not be necessary if the results yield fewer than 1,000 records. This often occurs when very targeted criteria are applied, such as selecting a range of material numbers for a specific plant or specific journal entries for a certain company code within a designated date range.
- Queries that return smaller datasets might not need AQT, and relying on it could actually hinder their performance. Other factors that influence query performance include:
- The number of criteria
- Network activity
- The count of records in the results
- The number of data fields included in the results
- To optimize memory usage, consider sending data in chunks instead of retrieving everything at once. This way, the information will be delivered to the Excel file, web form, or other destinations in segments.
- We suggest experimenting with various options to determine which yields the best results for your needs.
- To add AQT to your script, choose the options you want in the Studio Run screen
AQT Best Practices
- Use Adaptive Query Throttling (AQT) mode for execution, so that the Query run will not affect the SAP server performance.
- Use Direct Execution mode (non-AQT) to run queries that will extract fewer than a thousand items.
Note: See this link for these and additional best practices.
------------------------------
Tammy Lake
Sales Engineer
*Precisely Software Inc.
------------------------------