topic Re: Problem with select queries in Application Development and Automation Discussions

Problem with select queries

Former Member — Wed, 06 Aug 2008 07:42:49 GMT

Dear Friends,

I have written the below select queries to get the shipment details for a given date range.

select vbeln lddat

into table itab

from likp

where lddat in l_r_date2 .

if not it_ldt[] is initial.

select fknum fkpos rebel

into table jtab

from vfkn

for all entries in itab

where

rebel = itab-vbeln.

endif.

First query is giving 82000 entries and second query is giving approx 60000 from the table VFKN( total entries in this table are 8 lakhs).

Here the problem is second select query(for all entries select query) is taking a time of 3 to 4 hours.

So please suggest me how can i reduce the time.

Thanks and Regards

Re: Problem with select queries

Former Member — Wed, 06 Aug 2008 07:44:59 GMT

For dates IN operator shouldn't be used.

use less than and greater than

Re: Problem with select queries

JozsefSzikszai — Wed, 06 Aug 2008 07:50:22 GMT

hi Chandra,

what about creating a secondary index on table VFKN, with fields MANDT and REBEL?

"total entries in this table are 8 lakhs" ==> I am not sure if the meaning of unit of measure "lakhs" is clear for everyone...

hope this helps

Re: Problem with select queries

Former Member — Wed, 06 Aug 2008 07:50:37 GMT

Hi Chandra,

Plz read the below link:

http://it.toolbox.com/blogs/sap-on-db2/for-all-entries-vs-db2-join-8912

With luck,

Pritam.

Re: Problem with select queries

Former Member — Wed, 06 Aug 2008 07:51:32 GMT

Hi Chandra,

Before 2nd query

sort it_ldt by vbeln.

If this does not improve much on performance create secondary index on VFKN for fields MANDT and REBEL.

Regards,

Mohaiyuddin

Re: Problem with select queries

former_member585060 — Wed, 06 Aug 2008 07:53:16 GMT

Hi,

Use FREE or PACKAGE size.

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the driver table Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Regards

Krishna

Re: Problem with select queries

Former Member — Wed, 06 Aug 2008 08:02:52 GMT

hi check the if condition here ..

if not itab[] is initial.

select fknum

fkpos

LFNKN

rebel

into table jtab

from vfkn

for all entries in itab

where rebel = itab-vbeln.

endif.

use all the key fields available to fetch the data

Re: Problem with select queries

Former Member — Wed, 06 Aug 2008 08:47:20 GMT

Hi Chandra,

you should use FREE or PACKAGE size that will help you to get fetch in complete data. also check the following link http://it.toolbox.com/blogs/sap-on-db2/for-all-entries-vs-db2-join-8912.

filter the duplicates .

I hope this will help to solve your issue.

All the best

Priyanka