View Vs Join

[Former Member]

Dear Experts,

What is the best way of improving the performance in a selection, VIEW(creating a DB View) or JOIN (Joining the DB Tables)?

Thanks,

Bala

[Former Member]

From "Design, Implementation, and Analysis of Performance-Optimized Programs" on service.sap.com/performance

"With group requests from different tables, use JOINs or VIEWs. The JOIN is coded directly in ABAP. You can easily adapt it to the requirements of a specific application. The VIEW, however, is specified in the DDIC, which enforces definition uniformity throughout the program. VIEWs, like any table definition, may be used in multiple areas of the program, thus reducing the number of different statements in the statement cache.

Don't use JOINs or VIEWs over tables that are buffered with their technical settings. It is possible, in principle, to buffer a VIEW, but only in rare cases when you can buffer all concerned tables as well. Buffering a VIEW means not only less performance improvement, but also a significant rise in memory allocation and overhead during buffer synchronization. If you mix buffered and unbuffered tables in one access rather than reading both table types, you always lose performance because the database interface will not use the existing buffers.

In the SAP environment, there is a small but significant difference between JOIN and VIEW: In client dependent tables, every table in the JOIN MANDT = SY-MANDT is generated as part of the WHERE clause.

For VIEW, however, this WHERE clause is generated only for the table that provides MANDT in the VIEW projection list. Even though MANDT from each of the tables can join together the VIEW tables, significant processing differences in these tables can occur within VIEW if MANDT also appears in the indexes of the other tables that are included. These differences result from the fact that database optimizers only interpret the WHERE clause of VIEW to determine the first access. Therefore, you should take MANDT from the field list of the VIEW from the table providing the most selective first access. If the secondary tables include MANDT in their indexes and a selective primary table cannot be determined, then you should not use a VIEW. "