Performance of SELECT - get a line for employee with max begda

[Former Member]

what's the best way to get better performance ?

1)

SELECT pernr ktart begda anzhl INTO TABLE gt_2007

FROM pa2007 CLIENT SPECIFIED

WHERE mandt EQ sy-mandt

AND pernr IN s_pernr

AND ktart EQ '01'

ORDER BY begda DESCENDING.

LOOP AT gt_2007 INTO wa_2007.

AT NEW pernr.

CONTINUE.

ENDAT.

DELETE gt_2007 INDEX sy-tabix.

ENDLOOP.

2)

SELECT pernr ktart begda anzhl INTO TABLE gt_2007

FROM pa2007 CLIENT SPECIFIED

WHERE mandt EQ sy-mandt

AND begda EQ ( SELECT MAX( begda ) FROM pa2007 AS d

CLIENT SPECIFIED

WHERE d_{mandt = pa2007}mandt

AND d_{pernr = pa2007}pernr

AND d_{ktart = pa2007}ktart )

AND pernr IN s_pernr

AND ktart EQ '01'.

[Former Member]

Hi Eliram,

I would go with the first option with a few changes.

1) Never use order by in a select statement. It slows it down. Instead sort the table the way you want after the select statement retrieves the data.

2) Your loop is totally unnecessary. Sort the internal table by PERNR ASCENDING and BEGDA DESCENDING and delete adjacent duplicates from the internal table comparing PERNR. This will will give you the latest record for each employee.

3) If you are looking for KTART = '01' why do you need to include this field in the field list. You are not likely to get any value other than '01'

Look at the sample code below.

SELECT pernr
*      ktart "You don't need this field
       begda
       anzhl
INTO TABLE gt_2007
FROM pa2007
CLIENT SPECIFIED
WHERE mandt EQ sy-mandt
AND   pernr IN s_pernr
AND   ktart EQ '01'.

IF sy-subrc EQ 0.
  SORT gt_2007 BY pernr ASCENDING
                  begda DESCENDING.
  DELETE ADJACENT DUPLICATES FROM gt_2007 COMPARING pernr.
ENDIF.

[Former Member]

Why the first option ?

[Former Member]

Nested selects usually are not efficient. You can try it out physically. Run the second option open (for all the employees) in your system. View the performance in transaction SE30. Once you have done that use the code I gave you and check the performance the same way. You will see the difference.

[Former Member]

Eliram,

Did you try it out physically to find the difference. Did my code run faster. Did my post help. Please let me know.

[Former Member]

Hi,

As code given by MARK is good when perfomance wise and you asked why.

I am giving some of SELECT statement TIPS while fetching data form d/b

• Select Statements within Loop processing is not recommended. Preferred approach is to select data into an itab and then read the itab to access specific records

• Do not use Nested Selects, Selects within Loops. or SELECT...ENDSELECT

• Do not use Select * unless at least 70% of fields are needed

• Select only the fields you require.

• Do not use INTO CORRESPONDING

• Do not do Order By on non key fields

• Force optimizer to use the index where possible

• If primary index can not be used, look for alternate indexes or alternate index tables

• Avoid Use of LIKE in the Where clause on index fields. It will force a non index read.

• Avoid Use of NOT conditions in the Where clause on index fields. It will force a non index read.

• Select Single MUST have the primary key fully specified in the WHERE clause. Otherwise use Select.. Up to 1 Rows.

• Avoid DISTINCT see performance standards for usage

• Consider filtering on the appserver rather than in a WHERE statement

• SAP Recommendation on Joins - try not to exceed a 3 Table Join

• When using "Select.. For all Entries". The following 4 rules MUST be followed:

o Check to make sure driver itab is not empty

o Always SORT the itab (driver table) by keys. Specify all keys used in the Where clause

o DELETE Adjacent Duplicates Comparing the keys that were sorted.

o All Primary Key Fields must be in the Select List

Hope you can understand what r the tips u need to follow while using SELECT .

<b>Rewards with points if helpful.</b>

Regards,

Vijay

[former_member194613]

CLIENT SPECIFIED togehter with WHERE mandt EQ sy-mandt

does not make sense!

What is the problem with that option?

SELECT pernr ktart MAX(begda)

INTO TABLE gt_2007

FROM pa2007

AND pernr IN s_pernr

AND ktart EQ '01'

GROUP BY pernnr.

Siegfired

[Former Member]

Mark you right i try on se30 and nested select is less efficient.

[Former Member]

Mark you right i try on se30 and nested select is less efficient.

-

---

Eliram,

If you found my post helpful could you please award points and mark the post answered.