Skip to Content
author's profile photo Former Member
Former Member

View Vs Join

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Posted on Dec 16, 2004 at 03:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 16, 2004 at 04:22 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 17, 2004 at 09:00 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • Posted on Dec 20, 2004 at 12:47 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 30, 2004 at 07:36 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.