Maximize Your Query Optimization Options in BW

Get expert advice for when, how, and why you should optimize your BW queries. Also, determine whether to optimize them automatically or manually.

---

Key Concept

Query optimization is a process that BW runs to examine the current table structure, data volume, database indexes, and shared memory. This process ensures that the BW query runs using the most efficient path based on the current environment. This can increase the efficiency and performance of the query significantly by taking advantage of new indexes and joins based on sizing and changes to the overall BW data landscape.

While BW automatically optimizes queries the moment users save them, the query optimization can become outdated quickly because BW is an ever-changing environment. This process is virtually identical in SAP BW 3.x and SAP NetWeaver 2004s. During the generation of the query, the system runs an optimization process to generate the best SQL based on the known system information. The system uses this SQL code to access the requested data from the various tables in the database.

In addition to optimizing the query at the time of generation, by default, the system also automatically optimizes queries every 31 days when the first user runs the query. This optimization happens in the background when running the query. For example, say that no one has changed a query and thus, the system has not regenerated it in 31 days. The next time BW runs the query, BW automatically optimizes it based on the existing environment.

The user does not see or is not even aware that the system is optimizing the query. Often, the only noticeable difference for the user is the increased efficiency and performance of the query because of the optimization. All users benefit from this optimization and it continues every 31 days for each query unless the query is generated earlier. Generation of the query occurs automatically whenever a query is saved or during a manual generation in transaction RSRT. When the system generates a query, it automatically runs optimization on that query. Understanding how this optimization works allows you to determine if automated optimization would benefit your system’s performance.