performance issues due to FOR ALL ENTRIES

[former_member202335]

Hi,

I had the following select query in my code: -

SELECT vbeln posnr FROM vbup

INTO TABLE lt_del_item

FOR ALL ENTRIES IN lt_del_nos

WHERE vbeln = lt_del_nos-vbeln

AND wbsta NE gc_goods_mvmt_stat_comp

AND pdsta NE gc_pod_complete.

The above query was taking up a lot of execution time which I thought was because the 'lt_del_nos' table has about 800+ records.

In order to optimize my code, I replaced the query above with the one below: -

LOOP AT lt_del_nos INTO ls_del_header.

ls_vbeln-low = ls_del_header-vbeln.

ls_vbeln-sign = gc_inclusive_sign.

ls_vbeln-option = gc_equal_option.

APPEND ls_vbeln TO lr_vbeln.

CLEAR ls_del_header.

CLEAR ls_vbeln.

ENDLOOP.

SELECT vbeln posnr FROM vbup

INTO TABLE lt_del_item

WHERE vbeln IN lr_vbeln

AND wbsta NE gc_goods_mvmt_stat_comp

AND pdsta NE gc_pod_complete.

I found that the execution time improved significantly.

Why did the performance improve? After all the table lt_del_item and the range lr_vbeln have the same number of records. Is there any difference in the manner the the first query and the second query work?

Thanks in advance,

Divyaman

[Former Member]

try

delete adjacent duplicates from lt_del_nos comparing vbeln

before using for all entries.

[ThomasZloch]

quite interesting...800+ entries does not sound that much to me.

was your table LT_DEL_NOS sorted by VBELN before the FOR ALL ENTRIES statement?

If not, that might be the reason for the difference in execution time. It seems that when using ranges as in your second code version, the values are sorted by the DB interface before the select is executed. Maybe somebody knows this for sure.

Cheers

Thomas

[former_member202335]

Yes Thomas, lt_del_nos was sorted by vbeln.

The range is also sorted.

What my questions really is that why should one query do better than the other when the range and the internal table being used have the same contents and both of them are sorted?

I am myself searchin on the internet to find an answer to this question. Will post here if I find anything.

Cheers,

Divyaman

[Former Member]

Hello Divyman,

The performance would get improved futher if you can do away with NE Condition in where Condition. Can you modify your logic in such a manner so that

<b>wbsta NE gc_goods_mvmt_stat_comp

AND pdsta NE gc_pod_complete</b>

can have EQ instead of not NE. Also you have gone for a range object and the query give a dump if the number of entries in the range object is large say 4000 or more.

Regards,

<b>Reward Points if Useful</b>

Saket Sharma

[former_member202335]

Hi Saket,

Thanks for the warning! Didn't think about that.

Btw, how would changing the conditions to EQ from NE help?

Cheers,

Divyaman

[Former Member]

Hi Diyaman,

In general ,In WHERE conditions, you should use EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search. NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.

Regards,

Saket Sharma

[former_member202335]

Thanks, that clears my doubt!

Regards,

Divyaman

[former_member194613]

The ranges is of course faster than the FOR ALL ENTRIES, that is no suprise.

The FOR ALL ENTRIES is split into blocks.

But still, FOR ALL ENTRIES is the one you should use when the table becomes large. The range is more intended for munual selection screens, and will dump if the ranges table becomes too large.

Check index suppoort, the NE conditions will not help, so there is only vbeln.

Are there identical vbeln in lt_del_nos?

<b>Run the SQL trace, what the is average and minimal time per record for this statement?</b> See SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Siegfried

[former_member202335]

Hi Siegfried,

Thanks for your feedback! The screenshot is quite useful!

Regards,

Divyaman

[former_member194613]

Before you think about Equal or not equal you must check the index which contains the field vbeln, what other fields are in the index?

If wbsta and pdsta are not in the index then a change into equal will not help.

Actually I think, it will never help, that's why I did not mention it. Changing NE into an EQ with many conditions can help to fill an index gap, i.e. if you have further fields coming later in the index. But for trailing fields it will not help.

Siegfried

[former_member202335]

No, wbsta and pdsta are not in the index. So, I guess changing the condition from NE to EQ will not making any difference.

Thanks for sharing that information.

Cheers,

Divyaman

[ThomasZloch]

We are selecting from VBUP here, which has primary key VBELN and POSNR and no secondary index (at least in 4.7).

With this in mind I do not understand the comments here referring to indexes.

A selection on just 800 sales document numbers (sorted, no dups) by primary key should be fast in both cases.

Thomas

[former_member202335]

Yes, I agree, as pointed out by Seigfried, changing EQs to NEs is not going to make any difference if the fields in question are not part of the index.

Divyaman

[Former Member]

Hi,

There will be a lot of difference if you remove NE in your Query.

comment those conditions and get the data into Internal Table.

now write below code

Delete lt_del_item
  where wbsta EQ gc_goods_mvmt_stat_comp and pdsta EQ gc_pod_complete.

Regards,

Satish

Message was edited by:

Satish Panakala

[Former Member]

I think there was some sort of buffering (not SAP buffering) going on. Quite often when you execute similar queries one after the other, the second one will have much less execution time.

Try running each query a number of times and take the lowest of each. I'd expect them to be similar.

Rob