Whilst Query runners get to enjoy the benefits of running SAP reports to extract data directly from familiar interfaces such as Excel or SharePoint, Query designers have to carefully build their queries with performance in mind. Therefore, to truly master writing SAP reports and data extracts using Winshuttle Query, you need to understand how to best optimize your queries for optimal performance improvement.
Below is a list of the top 10 tweaks designers can employ for performance improvement of Winshuttle Query reports for SAP:
- Use AQT (Adaptive Query Throttling) mode when running queries. This will throttle the query execution to ensure the SAP server performance is not affected. Direct Execution mode (Non AQT) will provide faster performance for small sized queries.
- For complex multi-table Query scripts with large data sets, it is advisable to run as an SAP background process. This can be done in real-time or as a scheduled job and will not hamper the SAP server performance.
- Ensure that at least one key field “Selection criteria” is set to mandatory.
- Avoid joins between non-indexed fields to circumvent performance degradation.
- Joins between Primary Key fields will always yield the best performance; next will be Indexed field joins.
- Executing queries using the Data Chunking feature for large data downloads is an effective way to avoid network bottlenecks.
- Creating INNER JOIN queries is always advisable over LEFT OUTER JOINs.
- Queries using View and Transparent tables would perform better than Cluster and Pool tables because the latter are normally heavier in size.
- Do not include unnecessary tables, fields and joins in a query script
- Due to lack of overhead Text output type is always faster than Excel, Access, XML, SQL and SharePoint lists.
Questions or comments about this article?
Tweet @Winshuttle to continue the conversation!