Increase performance of the following SELECT statement.

[Former Member]

Hi All,

I have the following select statement which I would want to fine tune.

CHECK NOT LT_MARC IS INITIAL.

SELECT RSNUM

RSPOS

RSART

MATNR

WERKS

BDTER

BDMNG FROM RESB

INTO TABLE GT_RESB

FOR ALL ENTRIES IN LT_MARC

WHERE XLOEK EQ ' '

AND MATNR EQ LT_MARC-MATNR

AND WERKS EQ P_WERKS

AND BDTER IN S_PERIOD.

The following query is being run for a period of 1 year where the number of records returned will be approx 3 million. When the program is run in background the execution time is around 76 hours. When I run the same program dividing the selection period into smaller parts I am able to execute the same in about an hour.

After a previous posting I had changed the select statement to

CHECK NOT LT_MARC IS INITIAL.

SELECT RSNUM

RSPOS

RSART

MATNR

WERKS

BDTER

BDMNG FROM RESB

APPENDING TABLE GT_RESB PACKAGE SIZE LV_SIZE

FOR ALL ENTRIES IN LT_MARC

WHERE XLOEK EQ ' '

AND MATNR EQ LT_MARC-MATNR

AND WERKS EQ P_WERKS

AND BDTER IN S_PERIOD.

ENDSELECT.

But the performance improvement is very negligible.

Please suggest.

Regards,

Karthik

[Former Member]

To add to the below query.

Please note that the table has a secondary index which is being used for selection. So non usage of primary key is not an issue.

[christian_wohlfahrt]

Hi Karthik!

If you have a problem with memory consumption, the splitting with package size should help - otherwise I would prefer the into table version.

With a runtime of 76 hours you have hugh influence of general server loads - so some hours difference (maybe 5%) can happen.

Index M of RESB has a field KZEAR (in my ECC 6.0 version) - you should use as criteria, too - otherwise the restriction for the time with BDTER will not be part of the index access.

Maybe having thousands of articles in LT_MARC is stressing the database too much - I had better results splitting such a table e.g. in 5000-packages and selecting 'appending table gt_resb'. But this depends on how much additional selection criteria are used and which database you have - make some tests with different package sizes.

In general: when you have already a way to get a 1-hour version manually, make similiar programmed splits to get the same way automatically.

Regards,

Christian

[Former Member]

Two things:

when you

CHECK NOT LT_MARC IS INITIAL

You are checking the header area only, not the table.

Also, there is a secondary index for RESB that includes BDTER but you aren't using it because you aren't using all of the fields before it.

So try using:

CHECK NOT lt_marc[] IS INITIAL.
SELECT rsnum rspos rsart matnr werks bdter bdmng
  FROM resb
  APPENDING TABLE gt_resb PACKAGE SIZE lv_size
  FOR ALL ENTRIES IN lt_marc
  WHERE matnr EQ lt_marc-matnr
    AND werks EQ p_werks
    AND xloek EQ ' '
    AND kzear IN ('X', ' ')
    AND bdter IN s_period.
ENDSELECT.

Rob

[Former Member]

Hi Rob/Christian,

Thanks for the suggestion with the field KZEAR. I will include that and get back with the feedback.

The programatical splitting into smaller periods may not yield the results as there can be cases where a 15 day period can have a huge number of records.

Rob,

My table LT_MARC does not have a header line so the check will work fine.

Regards,

Karthik

[valter_oliveira]

Hi Karthik!

To have a global view for your situation, it would be necessary to see the entire problem (or at least a bit more of it).

If you use an index as suggested fine, it could solve your problem, but perhaps the problem is not the select! What i'm saying is that the FOR ALL ENTRIES statement is usefull until certain amount of entries, and LT_MARC shoud be a big big table.

So, my question is ... is LT_MARC realy needed in your program? If you only need it's data to do that select, why to waste time and memory creating the internal table and stressing the select with FOR ALL ENTRIES statement?

I repeat that this is just an idea, and the entire program would be necessary for a really helpfull advice.

Best regards.

Valter Oliveira.

[Former Member]

if you have too many entries in your LT_MARC it may cause problem with select.

We experienced slow selection in similar case, it was significally improved when we defined range (in your case - for material) and then inside loop/endloop at your lt_marc -> populate this range with materials and once you add ~100 materials to the range -> select from RESB without package size, BUT using APPENDING table GT_RESB, then refresh range & clear counter....something like :

ranges r_matnr for marc-matnr.

r_matnr-option = 'EQ'.

r_matnr-sign = 'I'.

loop at lt_marc.

z_count = z_count + 1.

r_matnr-low = lt_marc-matnr.

append r_matnr.

if z_count = 100. " select every 100 materials.

select .... appending table gt_resb

from resb

where matnr in r_matnr

and ....

refresh r_matnr.

clear r_matnr.

clear z_count.

r_matnr-sign = 'I'.

r_matnr-option = 'EQ'.

endif.

endloop.

if not r_matnr[] is initial. " select remaining materials

....... same select from resb

endif.

Make sure you check which one (for all entries or posted above) is faster in SQL trace (ST05). Would be great if you check how many index fierlds are used in your select from RESB in ST05.... we had cases when index field was not used if there was a space as one of possible values in select-option... but when we used where field = ' ' it used index... so in our case it was faster when we actually used 2 selects one with where field in s_option (all values except ' ') and another select with where field = ' '.

[Former Member]

Firstly, why is the package-size option recommended? It is because after selecting a whole lot of data, we might land up throwing out most of it due to other filters.

For example, if your RESB selection yields a million records, and then based on some filter condition, in a LOOP-ENDLOOP you end up deleting about 50 thousand records. In such a case, it is best to use a SELECT-ENDSELECT with PACKAGE SIZE, and then do the LOOP-ENDLOOP within the select-endselect.

The reason you gain an advantage is because your program would have slowed down because it was holding a million records, which you're avoiding.

So, if you're anyway going to hold the million, then using the package-size won't really help.

If there's any chance that you dont land up using your 3million records, then definitely go for a package-option. In fact, declare the package-size as a variable/selection-screen parameter, transport your program to a QA system which has realistic data, and then play around with the size to optimize the performance.

How many records does LT_MARC have? Is it already filtered based on P_WERKS? As mentioned in another reply, do you really need the LT_MARC filter?

Also, can you think of using RKPF first, and then using FOR ALL ENTRIES IN LT_RKPF? That will ensure that RESB's primary, unique index is used, and will speed up your selection...

[Former Member]

Hi Karthik,

<b>Do not use the appending statement</b>

Also you said if you reduce period then you get it quickly.

Why not try dividing your internal table LT_MARC into small internal tables having max 1000 entries.

You can read from index 1 - 1000 for first table. Use that in the select query and append the results

Then you can refresh that table and read table LT_MARC from 1001-2000 into the same table and then again execute the same query.

I know this sounds strange but you can bargain for better performance by increasing data base hits in this case.

Try this and let me know.

Regards

Nishant

> I have the following select statement which I would

> want to fine tune.

>

> CHECK NOT LT_MARC IS INITIAL.

> SELECT RSNUM

> RSPOS

> RSART

> MATNR

> WERKS

> BDTER

> BDMNG FROM RESB

> INTO TABLE GT_RESB

> FOR ALL ENTRIES IN LT_MARC

> WHERE XLOEK EQ ' '

> AND MATNR EQ LT_MARC-MATNR

> AND WERKS EQ P_WERKS

> AND BDTER IN S_PERIOD.

>

> e following query is being run for a period of 1 year

> where the number of records returned will be approx 3

> million. When the program is run in background the

> execution time is around 76 hours. When I run the

> same program dividing the selection period into

> smaller parts I am able to execute the same in about

> an hour.

>

> After a previous posting I had changed the select

> statement to

>

> CHECK NOT LT_MARC IS INITIAL.

> SELECT RSNUM

> RSPOS

> RSART

> MATNR

> WERKS

> BDTER

> BDMNG FROM RESB

> APPENDING TABLE GT_RESB

> PACKAGE SIZE LV_SIZE

> FOR ALL ENTRIES IN LT_MARC

> WHERE XLOEK EQ ' '

> AND MATNR EQ LT_MARC-MATNR

> AND WERKS EQ P_WERKS

> AND BDTER IN S_PERIOD.

> the performance improvement is very negligible.

> Please suggest.

>

> Regards,

> Karthik

Hi Karthik,