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. "

[christian_wohlfahrt]

Hi folks,

I'm not sure, if views are (without doubt) always faster than joins - runtime depends much more on join and where-clause, usable indices and so one.

But if an DB-table is buffered, at least projection views should make buffer accesses - joins will always bypass buffers (at least, views are not excluded in online documentation of 'SELECT').

Best regards,

Christian

[Former Member]

creating a VIEW without a doubt will be faster than the runtime JOIN created by a SELECT stmt. Obcourse, the benefits in terms of performance will be obvious if you are accessing large number of records otherwise the a normal JOIN stmt will serve the purpose.

[ChristianFi]

Why would a view be faster than a join?

Isn't a view not exactly the same?

Christian

[Former Member]

Hi,

I am not sure whether I have understood ur question or not. But for example, you are joing two tables and storing result in a view. For further operation, you can use this view instead of firing query on two tables, directly to the database. Also, whatever changes you make to database are reflected in the underlying view as well. so u cam have latest data also.

[Former Member]

Hi Kavya!

Your statement "you are joing two tables and storing result in a view" needs a bit of elaboration. Views do not store data. The data stays in the tables. As per SAP documentation <b>"The data of a view is derived from one or more tables, but not stored physically"</b>. It is accessed at runtime from these underlying tables.

As far as the performance issues are concerned, the point has already been made that performance depends on indexes, where clause, select statement etc.

Regards

[christian_wohlfahrt]

Views might be cached by application servers, joins are always accessed by database directly.

But most times joins (/views) are anyway complex and need a lot of entries -> caching is not useful.

Different approach (especially, when some indices are not used which you would prefer): select first table(s) into an internal table, read the rest 'for all entries' (which again is not cached).

Christian

[eddy_declercq]

Perfomance is more determined by the way you define the tables (keys, order of fields in select, etc.) More info at service.sap.com/performance.

PS Donn't forget to reward the answers with these yellow stars;-)

Message was edited by: Eddy De Clercq

[ChristianFi]

Christian,

there is no way to buffer views on application level (SAP Table Buffering) - is there?

At least I have always missed the technical settings button, so I assumed it would not be possible.

Christian

[Peter_Inotai]

As far as I know there is no difference in performance, only in reusability. DB View can be resued in several program. If you want to use it only in one of your program, join is easier....and you can use inner/outer join in you program, based on your needs.

Peter