Improve performance on statement select endselect retrieval on tables

[Former Member]

Hi Experts,

Good day!

May I ask some help regarding a custom program on how to improve the retrieval on tables using select endselect.

SELECT atinn INTO v_abv_classification

UP TO 1 ROWS FROM cabn WHERE atnam = c_abv.

ENDSELECT.

CHECK sy-subrc = 0.

CLEAR ausp.

SELECT clint INTO kssk-clint

FROM kssk

WHERE objek EQ l_matnr

AND mafid EQ c_o

AND klart EQ c_001.

SELECT imerk klart INTO (ksml-imerk, ksml-klart)

FROM ksml

WHERE clint EQ kssk-clint

AND imerk = v_abv_classification.

SELECT atwrt INTO ausp-atwrt

FROM ausp

WHERE objek EQ l_matnr

AND atinn EQ ksml-imerk

AND mafid EQ c_o

AND klart EQ ksml-klart.

ENDSELECT.

EXIT.

ENDSELECT.

EXIT.

ENDSELECT.

Answer's will be a great help.

~God Bless,

Lourd

[Former Member]

Hi Experts,

Good day!

May I ask some help regarding a custom program on how to improve the retrieval on tables using select endselect.

SELECT atinn INTO v_abv_classification

UP TO 1 ROWS FROM cabn WHERE atnam = c_abv.

ENDSELECT.

CHECK sy-subrc = 0.

CLEAR ausp.

SELECT clint INTO kssk-clint

FROM kssk

WHERE objek EQ l_matnr

AND mafid EQ c_o

AND klart EQ c_001.

SELECT imerk klart INTO (ksml-imerk, ksml-klart)

FROM ksml

WHERE clint EQ kssk-clint

AND imerk = v_abv_classification.

SELECT atwrt INTO ausp-atwrt

FROM ausp

WHERE objek EQ l_matnr

AND atinn EQ ksml-imerk

AND mafid EQ c_o

AND klart EQ ksml-klart.

ENDSELECT.

EXIT.

ENDSELECT.

EXIT.

ENDSELECT.

Answer's will be a great help.

~God Bless,

Lourd

[Former Member]

Hi,

Please DO NOT make use of SELECT .. ENDSELECT.

Instead retrieve data in one shot into an internal table and then LOOP it.

Regards,

Danish.

[Former Member]

Hi,

why not start with running this throught the Performance Analyzer? That will tell you which of these selects are troublesome. Then, when you have found your troubled select, apply common performance logic: select on key or index, fields in proper order, etc.

Roy

[marcin_cholewczuk]

Hi,

In first select, as I understand it, you're only reading number of entries in DB table so it rather should look like this

SELECT count(*)
FROM cabn
WHERE atnam = c_abv. 

Other 3 Selects should be combined with JOIN or even better would be to create a view and use it (if it wasn't created so far)

@Danish This is not always true. According to SAP

If you process your data only once, use a Select-Endselect-loop 
instead of collecting data in an internal table with Select Into 
Table. Internal table handling takes up much more space.

Best regards

Marcin Cholewczuk

[Former Member]

Hi,

Strictly regarding your SELECTs:

The first one is OK.

The other ones should use the same option 'UP TO 1 ROWS' since your are exiting after each query...

something like:

SELECT atinn INTO v_abv_classification
UP TO 1 ROWS FROM cabn WHERE atnam = c_abv.
ENDSELECT.
CHECK sy-subrc = 0.
CLEAR ausp.
SELECT clint INTO kssk-clint
FROM kssk
UP TO 1 ROWS 
WHERE objek EQ l_matnr
AND mafid EQ c_o
AND klart EQ c_001.

SELECT imerk klart INTO (ksml-imerk, ksml-klart)
FROM ksml
UP TO 1 ROWS 
WHERE clint EQ kssk-clint
AND imerk = v_abv_classification.

SELECT atwrt INTO ausp-atwrt
FROM ausp
UP TO 1 ROWS 
WHERE objek EQ l_matnr
AND atinn EQ ksml-imerk
AND mafid EQ c_o
AND klart EQ ksml-klart.
ENDSELECT.
ENDSELECT.
ENDSELECT.

However, I didn't check if a JOIN can be used between those tables... If this is the case, don't hesitate

Kr,

m.

[yuri_ziryukin]

And as far as I can see from the coding, the JOIN should be possible.

You just have to look at the number of the returned entries from the affected tables for your selection criteria.

If you don't want to do the JOIN, the change proposed by Manu is fine.

[volker_borowski2]

Hi,

a join makes only sense, if a result-hit of the inner selects is guaranteed.

I do not know the involved tables from the application side, so I would not know.

If it is possible, that these SELECTs might be empty, I think this might need to be an OUTER join then ?

I think the UP to 1 ROWS approach fits best in this case.

Volker

[Former Member]

>In first select, as I understand it, you're only reading number of entries in DB table so it rather should look like this

>SELECT count(*)

>FROM cabn

>WHERE atnam = c_abv.

This is completely wrong, the SELECT UP TO 1 ROWS does not count, but does an existence check, steps after 1 record is found. The COUNT(*) is often used for the same purpose, but does not stop after the first record is found, but counts all which can take a long time. The combination of both is also no recommended, as it is handled differently on different db platforms.

=> correct: replace COUNT(*) if only n>0 is checked by UP TO 1 ROWS.

[Former Member]

eidting is half of the solution:

SELECT atinn 
             INTO v_abv_classification
             FROM cabn 
            WHERE atnam = c_abv
            UP TO 1 ROWS
ENDSELECT.

CHECK sy-subrc = 0.
CLEAR ausp.

SELECT clint
              INTO kssk-clint
              FROM kssk
              WHERE objek EQ l_matnr
              AND mafid EQ c_o
              AND klart EQ c_001.

SELECT imerk klart  
              INTO (ksml-imerk, ksml-klart)
              FROM ksml
              WHERE clint EQ kssk-clint
              AND imerk = v_abv_classification.

SELECT atwrt
              INTO ausp-atwrt
              FROM ausp
              WHERE objek EQ l_matnr
              AND atinn EQ ksml-imerk
              AND mafid EQ c_o
             AND klart EQ ksml-klart.
ENDSELECT.
EXIT.
ENDSELECT.
EXIT.
ENDSELECT.

I do not really understand the logic of the nesting, because the handling of the result is missing. Which SELECT can find several records? Or are these only SELECT UP TO 1 ROWS not nested but in a sequence?

Siegfried

[Former Member]

Hi,

it looks like that the 3 SELECT ... ENDSELECT loops can be replaced with a

SELECT SINGLE ... FROM KSSK ... INNER JOIN KSML ... INNER JOIN AUSP ...

Regards,

Klaus

[surajarafath]

Hi,

I think your first select statement should be ok,

Try this code, hope it works...

Types:  begin of ty_tabl,
	clint type kssk-clint,
	imerk type ksml-imerk,
	klart type ksml-klart,
	atwrt type ausp-atwrt,
	end of ty_tabl.

data :  it_tabl type table of ty_tabl,
	wa_tabl type ty_tabl.

select atinn into v_abv_classification
up to 1 rows from cabn where atnam = c_abv.

if sy-subrc = 0.
select  a~clint
	b~imerk
	b~klart
	c~atwrt
	into corresponding fields of table it_tabl
	from kssk as a
	inner join ksml as b 
	on b~clint eq a~clint
	inner join ausp as c
	on  c~attin eq b~imerk 
	and c~klart eq b~klart
	where   a~objek eq l_matnr
	and	a~mafid eq c_o
	and	a~klart eq c_001
	and 	b~imerk	eq v_abv_classification.
	and 	c~objek eq l_matnr
	and 	c~mafid eq c_o.
endif.

Edited by: playsuji on Oct 5, 2011 3:43 PM

[Former Member]

Hi playsuji,

Just want to check with you. Is below 2 statement means the same?

1) select atinn into v_abv_classification up to 1 rows from cabn where atnam = c_abv.

2) SELECT SINGLE atinn INTO v_atinn FROM cabn WHERE atnam = c_abv.

Juli.

[Former Member]

They are identical, lots of discussion can be found.

Use them to make code better readable:

+ SELECT SINGLE with fully specified primary key, => only one record can fulfill condition

+ UP 1 ROWS, all other WHERE-clauses => many can fulfill condition only one is needed, for example existence check.

Unique secondary key, ... they are rare, so choose what you prefer.

Siegfried

[Former Member]

Hi Siegfried,

Thanks you very much for the clarification.

Juli.

[surajarafath]

HI Julie,

Sorry for late reply.

Yes they same, somekind.

Already siegfried has cleared it.

Hope your problem solved. If yes, give points to helpful posts.

Thanks Both.