topic Re: Taking too much time in Application Development and Automation Discussions

Taking too much time

Former Member — Fri, 18 Jul 2008 13:37:54 GMT

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,

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 13:40:40 GMT

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.

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 13:43:18 GMT

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.

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 13:47:15 GMT

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

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 13:51:28 GMT

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.

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 14:00:26 GMT

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

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 14:21:19 GMT

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.

Re: Taking too much time

Former Member — Fri, 18 Jul 2008 14:38:54 GMT

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

Re: Taking too much time

Former Member — Mon, 21 Jul 2008 04:54:14 GMT

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

Re: Taking too much time

Former Member — Mon, 21 Jul 2008 11:42:17 GMT

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