Taking too much time

[Former Member]

Hi All,

I am using this select statement in my report, this is taking too much time to pick the data. BSAK table contains huge data.

please modify this statement.

SELECT BUKRS LIFNR ZUONR BELNR BUDAT BLDAT

XBLNR SHKZG DMBTR HKONT

INTO TABLE I_BSAK FROM BSAK

WHERE BUKRS = CO_CODE-LOW

AND UMSKS = 'A' AND UMSKZ IN SPEC_GL AND

AUGDT > ASOFDATE AND BUDAT <= ASOFDATE.

Thanks,

[Former Member]

Ss,

i can see in BSAK Accounting: Secondary Index for Vendors (Cleared Items) which have 10 primary keys and in your select query you are using only 3.please use as much as you can.

Amit.

[Former Member]

Create a secondary index which matches your select statement; first find out which of the selection criteria narrows down the selection the most, put this field as the first in the secondary index, then the second.... and so on. Finally use the same order of fields in the select statement.

Regards,

John.

[Former Member]

John - it's a bad idea to create a secondary index on an SAP table to speed up a single SELECT statement - particulary when there's an easy programming solution.

Rob

[Former Member]

Rob - I'm very interested in the easy programming solution?

I agree that you shouldn't create a secondary index just for one report; but if the performance of the report is a big issue than I don't have a problem with that.

John.

[Former Member]

I've posted the answer a number of times and even written a blog that includes it. If the original poster had searched the forum, he would have found it.

But even if there were no solution, it would still be a bad idea. There is overhead when creating indexes on heavily used SAP tables. This program could probably be run in the background.

Rob

[Former Member]

Is there any way to modify code to increase performance of statement instead of create index.

Please let me know the process how to create indexes.

[Former Member]

Hi,

To Create Index -Open the table in SE11.

Now in Application Toolbar there is a tab Called INDEXES.

Click that And give the name of Secondary index.

Mention the Fields in the input box on which you want to create the index.

Now save and Activate that.

Check this link

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

Regards,

Sujit

[Former Member]

hi

LIFNR

BUKRS

AUGDT

AUGBL

<b>GJAHR</b>

BELNR

BUZEI fields are Secondary index for logical database so use maximum number of thses frelds in where condition

u can use range of GJAHR

GJAHR (Fiscal Year) = year from these fields v_GJAHR

(

AUGDT > ASOFDATE AND BUDAT <= ASOFDATE.)

use this help

<b> range for date (year) </b>

if not s_date[] is initial.

wa_r_year-sign = c_i.

wa_r_year-option = c_option.

wa_r_year-low = s_date-low+0(4).

wa_r_year-high = s_date-high+0(4).

if wa_r_year-high is initial.

wa_r_year-high = s_date-low+0(4).

endif.

append wa_r_year to r_year.

clear: wa_r_year.

endif.

that 'll sure help u

Amit

Edited by: Amit Kumar on Jul 21, 2008 6:54 AM

Edited by: Amit Kumar on Jul 21, 2008 6:54 AM

[Former Member]

Hi Amit,

Sorry to inform you ,this is not clear for me.

You mean, do i need to declare ranges ?

wa_r_year-sign = c_i.

wa_r_year-option = c_option.

wa_r_year-low = s_date-low+0(4).

wa_r_year-high = s_date-high+0(4).

if wa_r_year-high is initial.

wa_r_year-high = s_date-low+0(4).

how to creat and i dont want to use "GJAHR" in my select statement. If you dont mind please give the solution clearly

Thanks,

Subbu