Performance issue

[vinod_vemuru2]

Hi Guys,

I am trying to find out whether any sales order has been created for particular customer in past 6 months. I am writing below query. But it is taking toomuch time for input of around 900 customers. Is there any other way to improve the performance/to know whether sales order is created in past 6 months for a customer.

SORT i_tab BY kunnr.

DELETE ADJACENT DUPLICATES FROM i_tab COMPARING kunnr.

CHECK NOT i_tab[] IS INITIAL.

SELECT vbeln erdat kunnr

INTO TABLE i_vbak

FROM vbak

FOR ALL ENTRIES IN i_tab

WHERE kunnr EQ i_tab-kunnr

AND erdat GE l_date.

l_date contains 6 months past date.

Thanks,

Vinod.

[Former Member]

probably use the primary key fields of the table to filter the data first in the select statement....thereafter delete the unwanted records from the internal table.

give it a try...

Just to add, you say you are trying to find out WHETHER ANY sales order has been created for a customer. If you are just looking for a yes/no answer for this query, then there is no need to fetch all the data, just a select single will do the trick for you. however if you are looking for a list of all such sales orders then your query is correct and you will have to look at alternative ways of improving its performance.

Edited by: Priyank Jain on May 12, 2008 5:58 AM

[vinod_vemuru2]

Hi Guys,

Thanks for the quick responce.

Yes. First i also thought of select single. But if we use select single then we have to do it in side loop of customers. i.e around 900 hits on data base which may not be advisable.

Also i can input only customer number and date as input. So i can't go ahead with the BAPI.

Even i tried interchanging where clause fields as they appear in data base.

SELECT vbeln erdat kunnr

INTO TABLE i_vbak

FROM vbak

FOR ALL ENTRIES IN i_tab

WHERE erdat GE l_date

AND kunnr EQ i_tab-kunnr.

Is there any work around.

Thanks,

Vinod.

Edited by: Vinod Kumar Vemuru on May 12, 2008 3:42 PM

[Former Member]

Hello Vindoh-

Instead of the select staement try this BAPI_SALESORDER_GETLIST .

This may improve the perfomance.

Cheers,

~Srini....

[Former Member]

Hi Vinod,

I found there is an secondary index(ERD) to the table VBAK, which has only one field(Created Date - ERDAT). So write select with the where class having only created date and then check whether that sales order has the customer which is in your internal table as shown below.

CHECK NOT i_tab[] IS INITIAL.

SELECT vbeln erdat kunnr

INTO TABLE i_vbak

FROM vbak

WHERE erdat GE l_date.

sort i_tab by kunnr.

data v_tabix like sy-tabix.

loop at i_vbak.

v_tabix = sy-tabix.

read table i_tab with key kunnr = i_vbak-kunnr binary search.

if sy-subrc <> 0.

delete i_vbak index v_tabix.

endif.

endloop.

Note that as there is index on created date, this select will take less time.

Regards,

Satya

[former_member188827]

sort i_tab by erdat.

loop at i_tab.

if i_tab-erdat ge l_date.

delete i_tab index sy-tabix.

end if.

endloop.

den use

ur code

SORT i_tab BY kunnr.

DELETE ADJACENT DUPLICATES FROM i_tab COMPARING kunnr.

CHECK NOT i_tab[] IS INITIAL.

SELECT vbeln erdat kunnr

INTO TABLE i_vbak

FROM vbak

FOR ALL ENTRIES IN i_tab

WHERE kunnr EQ i_tab-kunnr

AND erdat GE l_date.

[vinod_vemuru2]

Problem solved by first getting data from vakpa and then from vbak.