view vs table wich select is faster?

[Former Member]

Hello,

Im improving performance on a select stm.

I found out that the table Im using has a view.

Which select statement is faster??

      SELECT SINGLE matnr charg
      INTO  (g_matnr, g_charg)
      FROM V_MM_CHVW  "view
      WHERE  werks = '2472'
      AND  ( bwart = '321'
      OR     bwart = '309'
      OR     bwart = '311'
      OR     bwart = 'Z09' )
      AND   umwrk = '2472'
      AND   ummat = i_lotes-matnr
      AND   umcha = i_lotes-charg.

or

      SELECT SINGLE matnr charg
      INTO  (g_matnr, g_charg)
      FROM CHVW " table used by the view
      WHERE  werks = '2472'
      AND  ( bwart = '321'
      OR     bwart = '309'
      OR     bwart = '311'
      OR     bwart = 'Z09' )
      AND   umwrk = '2472'
      AND   ummat = i_lotes-matnr
      AND   umcha = i_lotes-charg.

What do you think?

Bye

Gabriel P.-

[Former Member]

I don't think it would make much difference either way. Have you tried it?

Your performance problem is caused by not using an index effectively.

Rob

Edited by: Rob Burbank on Sep 16, 2008 5:04 PM

[Former Member]

Hello Rob,

Thanks for your input....I tried the tcode with se30, and the result is:

With the view is slower... I thougth that it could be faster . I wanted to know a few abapers opinions.

My initial problem began when trying to improve a select stm I have to mseg table....i found this table but also when checking on se30 the performance is about the same.

Thanks again!

Gabriel P,.

Edited by: Gabriel Fernando Pulido V. on Sep 16, 2008 4:06 PM

[Former Member]

Any difference is likely due to buffering.

Rob

[Former Member]

Hello,

How do I do it?, I have not done something like that?, do you have a code sample?

Thanks for your help.

Gabriel P,.

[christine_evans]

A database view is effectively a join between tables and is defined as a bit of SQL stored in the database. Consequently, a select from a view will usually take longer than a select from one of the view's base tables. Where possible, always select from the table.

[Former Member]

>

> A database view is effectively a join between tables and is defined as a bit of SQL stored in the database.  Consequently, a select from a view will usually take longer than a select from one of the view's base tables.  Where possible, always select from the table.

Christine - do you have any test results that you can share to support this?

Rob

[Former Member]

>

> A database view is effectively a join between tables and is defined as a bit of SQL stored in the database.  Consequently, a select from a view will usually take longer than a select from one of the view's base tables.  Where possible, always select from the table.

Hi Christine,

a view though can include joins to other tables; but it is much more: it can include whatever you can define as a SELECT statement.

So to say a view is slower than a table access is not true - depends on your SELECT.

bye

yk

[christine_evans]

>

> >

> > A database view is effectively a join between tables and is defined as a bit of SQL stored in the database.  Consequently, a select from a view will usually take longer than a select from one of the view's base tables.  Where possible, always select from the table.

>

> Christine - do you have any test results that you can share to support this?

>

> Rob

No I've not run any specific tests, which is why I qualified my statement with a 'usually' (then again I usually qualify everything just in case I am wrong.....). But I think it is a reasonable assumption to make to say that a select from a single table will take less time than the same select from that table joined to another table - which would be the case if you use a view. And, in any case, if you can get by with a select from one of the base tables, I do not see that there is any point using a view instead.

In many cases, of course, the difference between the two will be so miniscule as to be unnoticeable. And also in a lot of cases, a single table will not give you all the data that you want in which case views and joins are a better way to go (in my opinion) than a lot of separate selects from single tables.

[christine_evans]

>

> >

> > A database view is effectively a join between tables and is defined as a bit of SQL stored in the database.  Consequently, a select from a view will usually take longer than a select from one of the view's base tables.  Where possible, always select from the table.

>

>

> Hi Christine,

>

> a view though can include joins to other tables; but it is much more: it can include whatever you can define as a SELECT statement.

>

> So to say a view is slower than a table access is not true - depends on your SELECT.

>

> bye

> yk

Yes, everything depends on variables such as the definition of the view and the contents of your select. And, of course, if the view contains restrictions that are needed by the select, then a single table select won't do the same job. But in the examples posted, the field lists and where clauses were identical and I judged from what the OP said that the results were the same from both. In this situation, I would never choose the view over the table.

[Former Member]

>

> No I've not run any specific tests...

I think if you test this you'll see that one or the other has a slight statistical advantage (measurable in microseconds), but the real problem with the SELECT is that there is no effective use of an index. This is the cause of the problem. So looking at base tables vs. views vs JOINs is simply a waste of time.

Rob

[Former Member]

Hello All,

I have tested all possible select statements with tcode SE30 and st05.

The results were just what I was expecting, at least for the sense I had when I tried the stm just by executing with out any measurement tool.

The select direct to a table or a view take about the same time, when the stm is direct to the table is just a little bit faster, on microseconds time as Rob suggested. ( I took the time with SE30 ).

The index use is up to the bd system, but when I changed the order on the WHERE stm, the index changed, so the only way to "force" the bd to use an specific index is by using HINT, but I think is not recommended, because it is the system optimizer work.

I tried also using on my SQL stm "BYPASSING BUFFER", but it also slower than the others, because it doesnu2019t use the BUFFER to fastly fetch the data.

In conclusion, SQL select stm use depending on the context you are working on (as always je je). What I finally did was to use the fastest index I measured, and try to have all statistics up to date.

What do you guys think?

Bye, and thanks all for your inputs!!

[Former Member]

Here's a simple test:

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: m_debia.

SELECT-OPTIONS s_s1 FOR m_debia-sortl.

DATA: itab TYPE TABLE OF m_debia WITH HEADER LINE.

DATA: start TYPE i,
      end TYPE   i,
      dif TYPE   i.

SELECT sortl pstlz mcod3 mcod1 kunnr
  FROM m_debia
  INTO CORRESPONDING FIELDS OF TABLE itab
  WHERE sortl IN s_s1.

SELECT sortl pstlz mcod3 mcod1 kunnr
  FROM kna1
  INTO CORRESPONDING FIELDS OF TABLE itab
  WHERE sortl IN s_s1.

DO 10 TIMES.

  GET RUN TIME FIELD start.
  SELECT sortl pstlz mcod3 mcod1 kunnr
    FROM m_debia
    INTO CORRESPONDING FIELDS OF TABLE itab
    WHERE sortl IN s_s1.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for view :', dif, 'microseconds'.

  GET RUN TIME FIELD start.
  SELECT sortl pstlz mcod3 mcod1 kunnr
    FROM kna1
    INTO CORRESPONDING FIELDS OF TABLE itab
    WHERE sortl IN s_s1.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for table:', dif, 'microseconds'.

  SKIP 1.
ENDDO.

The differences are a few microseconds and either may be faster.

Rob

[christine_evans]

I must apologise. That is a single table view, as is the original now I come to look at it. Everything I've said applies to multi-table views only which are the type of views I mostly use. Sorry for the confusion.

Though I still stand by the fact (because I'm stubborn that way) that if you've got a view and a base table and there is no difference between the two, why confuse things by using the view rather than the base table.....

[Former Member]

Hi Christine ,

I prefer using views

Imagine

• renaming fields

• aliasing fieldnames

• plain table structure should not be exposed to developers or reporting systems

• implementation of a customized row-level security

• ...

what do you think the ABAP dictionary is all about?

It establishs a kind of view layer to different databases - they would never use

the table structure as it is in the different databases.

When you use a table in ABAP its a meta data definition (a kind of basic view) that points to a table structure in the database. Same for these views we are talking about - so you are not forced to use views but sometimes they make your live easier (as a developer).

If you working mostly in custom defined applications views are a main key of creating an abstraction layer

for exposing data to different consumers.

Bye

yk

[christine_evans]

>

> Hi Christine , 
> 
> I prefer using views 
> 
> Imagine
> 
> * renaming fields
> * aliasing fieldnames
> * plain table structure should not be exposed to developers or reporting systems
> * implementation of  a customized row-level security
> * ...
> 
> what do you think the ABAP dictionary is all about?
> It establishs a kind of view layer to different databases - they would never use
> the table structure as it is in the different databases.
> 
> When you use a table in ABAP its a meta data definition (a kind of  basic view) that points to a table structure in the database. Same for these views we are talking about - so you are not forced to use views but sometimes they make your live easier (as a developer).
> 
> If you working mostly in custom defined applications views are a main key of creating an abstraction layer 
> for exposing data to different consumers.
> 
> Bye
> 
> yk

Yes I see what you mean. You can use views to 'customise' the data lower down at database level rather bringing the data to the application and applying the customisation there by copying it into structures with different field names, applying extra validation checks etc. Not something I've looked at doing in SAP up until now but I'll consider it for my next development.....

[Former Member]

It is better to used with the table,

I used BYPASSING BUFFER on my select stm...to avoid the buffer, but it is also slower.

Bye

[former_member190578]

Why do you use just "BYPASSING BUFFER"???

BYPASSING BUFFER:
This addition guarantees that the data you read is the most up to date. However, as a rule, only data that does not change frequently should be buffered, and using the buffer where appropriate improves performance. You should therefore only use this option where really necessary. 

[Former Member]

Hi

whats's the SQL of the view? If it contains additonally SELECT and where's (joins i.e.) you can't hardly compare both statements.

From a database point of view the ABAP runtime creates the view in it's dictionary AND in the databases dictionary:

Here's an example for ORACLE:

select view_name from dba_views where view_name like '%your_view%'

you will find it not only as a definition in ABAP dict. but also on database level.

If you use the view in ABAP for a SELECT the Sql is propagated to the database and it will

examine the view.

This means there is NO difference in executing the SELECT on the view or table.

Check what the view's hiding (if any) or some other artificial effects (as mentioned from

others here).

bye

yk

[former_member194613]

> Though I still stand by the fact (because I'm stubborn that way) that if you've got a view and a base table and there is no difference between the two, why confuse > things by using the view rather than the base table.....

the single table views are used to get only some field without writing long fields lists

select   key1 key2 field4 field7
           into ...
           FROM dbtable
           WHERE ...

is the same as

select  *
           into ...
           FROM v_dbtable
           WHERE ...

when the view consists only of key1 key2 field4 field7.

Improves reuse. Should not have any performance implications, because internally the first select is executed.

Siegfried