optimization of code

[Former Member]

Hi ,

Please help me to optimize this code w.r.t performance tuning. thanks in advance

SELECT   v~aufpl
         v~aplzl
         V~FSAVD
         V~FSEDD
         V~ISDD
         V~IEDD
         V~MGVRG
         V~GMNGA
         V~MEINH
         c~ltxa1
         c~objnr
         C~ARBID
         s~werks
         s~arbpl
         s~vornr
         s~aufnr
         s~matnr
         o~cy_seqnr
         o~dispo
         D~VERWE
         u~usr04
         u~use04
         INTO  TABLE itab
         FROM ( ( ( ( ( afvv AS v INNER JOIN afvc AS c  ON v~aufpl EQ c~aufpl and  v~aplzl eq c~aplzl )
         inner join s022 as s  on c~objnr eq s~objnr  ) INNER JOIN
         AFko  AS O ON v~AUFPL EQ O~AUFPL AND S~AUFNR EQ O~AUFNR AND S~MATNR EQ O~PLNBEZ ) INNER JOIN CRHD
         AS D ON C~ARBID EQ D~OBJID AND S~ARBPL EQ D~ARBPL AND S~WERKS EQ D~WERKS )
         inner join afvu as u on v~aufpl eq u~aufpl and v~aplzl eq u~aplzl )
                                      WHERE  s~vornr in s_vornr
                                      and    s~werks in s_werks
                                      and   s~arbpl in s_arbpl
                                      AND   D~VERWE  IN S_VERWE
                                      and    o~aufnr in s_aufnr
                                      and    o~cy_seqnr in s_seqnr
                                      and   o~dispo in s_dispo
                                      and   c~ltxa1 in s_ltxa1
                                      and  V~fsavd in p_fsavd
                                      and  V~Fsedd in p_fsedd.

Anoop

[ravi_lanjewar]

Hi Anoop,

Optimization is depend on your input condition. Here you have many fileds as selection option. Which fileds or manditary we don't know or optional.

Pl. specify mandatory fields an optional fields according that we process different logic for different input criteria.

Pl. Specify minumum input if possible selection option and parameter.

Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jun 8, 2010 5:55 PM

[Former Member]

Welcome to SDN.

restrict your JOIN to a max of 3 tables. So break your SELECT into 2 or more...

Kiran

[Former Member]

See if you can you Database views, CAUFV may be useful. Also depending on the number of return entries see if you should use FOR ALL ENTRIES.

[Former Member]

the select-option are the central point.

Specify how they are filled? You can not expect that any combination can be optimized. But try to optimize the most important.

Rest of the analysis is straightforward test of combinations ... however there are a lot of combinations

[Former Member]

Moderator message - Welcome to SCN. Please see before posting. You need to do some further work before posting here. Also, Please read and and [Asking Good Questions in the Forums to get Good Answers|/people/rob.burbank/blog/2010/05/12/asking-good-questions-in-the-forums-to-get-good-answers] before posting again. Rob

[Former Member]

Hi Ravishankar Lanjewar,

this is my selection screen pls help

SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME.
PARAMETERS: p_vari LIKE disvariant-variant. " ALV Variant
SELECTION-SCREEN END OF BLOCK blk1.

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME title text-001.
SELECT-OPTIONS :  S_werks FOR s022-werks,
                  s_seqnr  FOR afko-cy_seqnr,
                  s_aufnr  FOR afko-aufnr,
                  s_arbpl  for s022-arbpl,
                  S_VERWE   FOR CRHD-VERWE,
                  s_vornr  for s022-vornr,
                  s_dispo  for  afko-dispo,
                  s_ltxa1  for  afvc-ltxa1.
SELECTION-SCREEN BEGIN OF BLOCK blk2 WITH FRAME title text-002.
 SELECT-OPTIONS : p_fsavd  FOR  s022-fsavd,
                  p_fsedd  FOR  s022-fsedd.
 PARAMETERS :CB AS checkbox.
 SELECTION-SCREEN END OF BLOCK blk2.
PARAMETERS :   rb_teco AS CHECKBOX.
SELECTION-SCREEN END OF BLOCK blk.

[ravi_lanjewar]

Hi Anoop,

You can used the following stratagey to read data from multiple table having multiple select option.

1) Don't used more than 3 table in join.

2) Used for all entries to read data from other table.

3) check the table is not empty while using in for all entries otherwise it will give timeout runtime error.

4) Read data first from most index fields in select-option input, it minimuze the data read and fast fetching the data. After then use select for all entires to read data from other table.

For your program you should you following execution plan.

1. if s_vornr[] or s_werks[] or s_arbpl[] is not initial

First Read data from table s022 and there read data from afvC & afvc using join

after then read data from table AFKO using for all entries and also read other data using for all entires

2. elseif S_AUFNR[] or S_SEQNR[] or s_DISPO[] is not INITIAL.

First Read data from AFKO and then read data from table AFvv & AFVC using join and then s022

after read other data from other table.

3. elseif P_FSAVD[] or P_FSEDD[] is not initial

Read data from AFVV and AFVV using join and after thern read other data from s022 and after then read from afko table

4. else used your query to here

Rgds

Ravi Lanjewar

[Former Member]

You should be aware that there is big difference between the coding and the actual usage of select-options.

[Former Member]

hi Anoopk ,

create a view with all the tables you mentioned in the query using INNER JOIN and select data from that view, it should improve the performance. Note: join tables only on key fields for better performance.

[Former Member]

Unless it's a materialized view (which SAP doesn't support), the performance gain through a view is more or less insignificant (as far as my experience goes). It may profit a little bit from datebase buffering though, but only if the view is frequently used.

You can gain more if you analyze how the optimizer executes the statement, and maybe create an usefull index (or two).

[Former Member]

> It may profit a little bit from datebase buffering though, but only if the view is frequently used.

Not even that is the case, the view exist only on ABAP side, the DB interface sends the join to the database. For the DB join and view are 100% identical.

So it is hard work you must analyse the coding in detail.

[Rui_Dantas]

just to add even more confusion..

>

> Unless it's a materialized view (which SAP doesn't support) 

it does "support" if SAP doesn't know it is a materialized view...

>

> Not even that is the case, the view exist only on ABAP side, the DB interface sends the join to the database.

a database view created in SE11 does exist in the database (hence the name database view..

[HermannGahm]

Hi Siegfried,

>

> > It may profit a little bit from datebase buffering though, but only if the view is frequently used.
> Not even that is the case, the view exist only on ABAP side, the DB interface sends the join to the database. .

that's a surprising statement. Which platform are you talking about? For me on ORACLE:

If i go to SE16 and select from KNA1VV (which is a view in the SAP DDIC) this SELECT goes to the database:

SELECT

/*+

FIRST_ROWS (200)

*/

*

FROM

"KNA1VV"

WHERE

"MANDT" = :A0 AND ROWNUM <= :A1

So no join is sent to the database but the select to the view.

and i can confirm that the VIEW KNA1VV is defined on the database as well in the

database catalog:

select view_name from dba_views where view_name = 'KNA1VV'

(no hit in dba_tables for that object name)

>

> For the DB join and view are 100% identical.
So it is hard work you must analyse the coding in detail.

However, i agree with this statement. If we do an explain plan for this select on the view

we get a join which is 100% identical to a join that would have been done in OPEN SQL.

And yes it is hard work to analyze the execution details for joins / views.

Kind regards,

Hermann

[Former Member]

Please do not confuse a view which is only a definition in the data dictionary and a view which actually

contains data in the database.

My comment above was correct, if you read the statement in the comment above:

>Unless it's a materialized view (which SAP doesn't support), the performance gain through a view is more or less insignificant

>(as far as my experience goes). It may profit a little bit from datebase buffering though, but only if the view is frequently used.

two different kinds of views !!

And for the KNA1VV I see a join in the explain (DB6 database):

SELECT FROM kna1vv :

    0 SELECT STATEMENT ( Estimated Costs =  2,292E+03 [timerons] )
           1 RETURN
               2 MSJOIN
                   3 [O] FETCH KNA1
                       4 IXSCAN KNA1~0 #key columns:  1
                   5 <i> FILTER
                       6 FETCH KNVV
                           7 IXSCAN KNVV~0 #key columns:  1

same for DD03VV now on Oracle

 SELECT STATEMENT ( Estimated Costs = 11 , Estimated #Rows = 201 )
        5 COUNT STOPKEY
          Filter Predicates

            4 TABLE ACCESS BY INDEX ROWID DD03L
              Estim. CPU-Costs = 4.509 Estim. IO-Costs = 0

                3 NESTED LOOPS
                  ( Estim. Costs = 10 , Estim. #Rows = 201 )
                  Estim. CPU-Costs = 133.703 Estim. IO-Costs = 10

                    1 TABLE ACCESS FULL DD02L
                      ( Estim. Costs = 2 , Estim. #Rows = 69.173 )
                      Estim. CPU-Costs = 39.023 Estim. IO-Costs = 2
                      Filter Predicates
                    2 INDEX RANGE SCAN DD03L~1
                      Search Columns: 2
                      Estim. CPU-Costs = 1.824 Estim. IO-Costs = 0
                      Access Predicates

There are materialized views, but I doubt that this is valid performance optimization in most cases.

Siegfried

[HermannGahm]

Hi Siegfried,

I'm a little bit confused now, you said:

> the view exist only on ABAP side, the DB interface sends the join to the database.

> And for the KNA1VV I see a join in the explain (DB6 database):

and what SQL is sent to the database? To my understanding it should be

the SELECT on the VIEW and no join. The join appears in the execution plan.

But in the SAP DBI it should be a SELECT on the view (no join).

The translation from the select on the view is not done in the SAP DBI which

just sends the SQL on the view, but in the database (where view definition

exists as well for materialized AND 'normal' views).

Whether we have a materialized view or not is not visible in the SAP DDIC

and should make no difference here.

Kind regards,

Hermann

[Former Member]

o.k., you are right, the database does know the structure of view. It is possible to write direct change statements on the view.

But still; i am not aware of any performance difference between the join and the not materialized database-view.

Siegfried

[ravi_lanjewar]

performance difference between the join and the not materialized database-view

If we used the view database know the join condition, but ABAP JOINs are formulated in ABAP directly.

If you want the database system to determine the resulting set of the JOIN operator, you can use DB views, ABAP JOINs or subqueries. DB views are first created in the ABAP Dictionary, and are created on the database when the Dictionary object is activated. DB views created by other developers can also be used (reusability).

But there is not any performance difference between join and view.

The following important access strategies are available to the database optimizer for processing ABAP JOINs, subqueries, or SQL statements against database views:

Nested loop: This strategy is relevant for database views and ABAP JOINs. First, the WHERE clause is used as a basis for selecting the (outer) table to be used for access. Next, starting from the outer table, the table records for the inner tables are selected according to the JOIN condition.

Sort merge join: First, the WHERE clause is evaluated for all tables in the join, and a resulting set is produced for each table. Each resulting set is sorted according to the JOIN conditions and then merged, also according to the JOIN conditions.

It is possible to write direct change statements on the view

Yes, Some cases it is possble to direct changes on view, but not all cases.

Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jun 23, 2010 11:07 AM

[HermannGahm]

Hi Siegfried,

Hi Ravi,

yes, there is no difference with respect to performance in processing a select with join or a view.

I would like to add the hash join.

First, the WHERE clause is evaluated for the table that is supposed to have the smaller result set (optimizer estimated rows). This result set is stored in a hashed table (hashing the join keys). Then the remaining resultset (2nd table with where / join condition applied or other resultset e.g. from anohter join) is read and probed again the hash table.

>>It is possible to write direct change statements on the view

>Yes, Some cases it is possble to direct changes on view, but not all cases.

I'm not sure whether Siegfried meant "it is" or "it is not"... . Up to my knowledge

within OPEN SQL only projection views (a view that has only ONE table) can be

updated. In standard SQL i think views with more than one table can be updated too,

but I'm not sure... .

Kind regards,

Hermann

[Former Member]

Hermann wrote:
yes, there is no difference with respect to performance in processing a select with join or a view.

Here's a strange comment from the SAP help on [minimize the number of data transfers|http://help.sap.com/saphelp_nw70ehp2/helpdata/en/aa/47349a0f1c11d295380000e8353423/frameset.htm]:

You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. Such views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server. 

I don't have a system to do any tracing right now, but does this actually mean that with a select * on a join the join fields are really returned multiple times by the database? If this would be true, this might contribute to some performance degradation when lots of or larger fields are joined and we have a bigger result set...

Also, on a theoretical level I'm wondering if parsing and preparing the SQL statement (by the DBI, i.e. database interface) has any impact (if though, I doubt it's measurable). I.e. let's say I have an ABAP program with an Open SQL statement containing a join with 5 tables. Could it be that the parsing and preparation of the SQL join statement by the DBI is more time consuming than if I'd had used a dictionary view?

I guess to some degree it depends how efficiently the Open SQL statements are compiled and what is done at ABAP compile time versus DBI runtime (e.g. are conditions for client handling added for the join at compile time and the client value is filled at run time or are even the conditions only added at runtime?). I'm pretty sure this is all negligible, but it still would be interesting to understand how it works (couldn't find any details).

Cheers, harald

[HermannGahm]

Hi Harald,

>

You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. Such views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server. 

> I don't have a system to do any tracing right now, but does this actually mean that with a select * on a join the join fields are really returned multiple times by the database? If this would be true, this might contribute to some performance degradation when lots of or larger fields are joined and we have a bigger result set...

in a view we can not have columns with the same (duplicate) names. So we normally have 1 field matnr (not 2 or more) and so on. But a view is a join (if you look in the object log, how a view is created...

CREATE VIEW "KNA1VV"

("MANDT",

"KUNNR",

"VKORG",

...

AS SELECT

T1."MANDT",

T1."KUNNR",

...

T1."STKZN",

T1."UMSA1"

FROM

"KNA1" T1,

"KNVV" T2

WHERE

T1."MANDT" = T2."MANDT" AND

T1."KUNNR" = T2."KUNNR"

This is a join.

In the join you can as well not select 2 columns with the same name ... (select f1 , f1... does not work but select f1 as n1, f1 as n2 works)... they would be ambigious otherwise.

Therefore i don't think that this statement is correct. A view is processed like a join (since the view is created as a join stored in the database dictionary).

Kind regards,

Hermann

[HermannGahm]

Hi Harald,

> Also, on a theoretical level I'm wondering if parsing and preparing the SQL statement (by the DBI, i.e. database interface) has any impact (if though, I doubt it's measurable). I.e. let's say I have an ABAP program with an Open SQL statement containing a join with 5 tables. Could it be that the parsing and preparation of the SQL join statement by the DBI is more time consuming than if I'd had used a dictionary view?

>

> I guess to some degree it depends how efficiently the Open SQL statements are compiled and what is done at ABAP compile time versus DBI runtime (e.g. are conditions for client handling added for the join at compile time and the client value is filled at run time or are even the conditions only added at runtime?). I'm pretty sure this is all negligible, but it still would be interesting to understand how it works (couldn't find any details).

i don't know about these details. The join is in any case a longer SQL string with more memory consumption in the cursor cashes and theoretically higher parse times (since it is a longer string then a select on a view). But... i never faced performance issues where a view led to better performance than a join... yet...

Kind regards,

Hermann

[Former Member]

In the join you can as well not select 2 columns with the same name ... (select f1 , f1... does not work but select f1 as n1, f1 as n2 works)... they would be ambiguous otherwise.

Well, within their name space the column names are always unique. I.e. you could always reference the field as <schema>.<table>.<field> or something like <schema>.<table>.*. As far as I remember a select * on a join returns all fields, thus also duplicate join fields...

[HermannGahm]

Hi Harald,

>

 or something like <schema>.<table>.*. As far as I remember a select * on a join returns all fields, thus also duplicate join fields...

your are right... ALL fields are selected. In OPEN SQL we need still ar target with unambigious names (duplicate fields with different names or so). To me defining the structure with duplicate fields but different names is similar to the view definition with duplicate fields and different names. But yes, you are right, all fields are selected from all involved tables if we do a

select * from taba join tabb...

Kind regards,

Hermann