topic Re: regardinf faster select statement in Application Development and Automation Discussions

regardinf faster select statement

Former Member — Thu, 04 Oct 2007 10:51:10 GMT

hi friends i m using this select statements............

in production system thaere are lots of records thats why taking so much time ......

and giving dump..................

pls,tell some changes thats require for better performance............

types:BEGIN OF T_BSEG,

BELNR LIKE BSEG-BELNR,

BUDAT LIKE BKPF-BUDAT,

WERKS LIKE BSEG-WERKS,

LIFNR LIKE BSEG-LIFNR,

MATNR LIKE BSEG-MATNR,

MAKTX LIKE MAKT-MAKTX,

FWBAS LIKE BSEG-FWBAS,

MWSKZ LIKE BSEG-MWSKZ,

WRBTR LIKE BSEG-WRBTR,

SGTXT LIKE BSEG-SGTXT,

SHKZG LIKE BSEG-SHKZG,

PRCTR LIKE BSEG-PRCTR,

END OF T_BSEG,

IT_BSEG2 LIKE IT_BSEG OCCURS 0 WITH HEADER LINE,

BEGIN OF T_BKPF ,

BELNR LIKE BKPF-BELNR,

BUDAT LIKE BKPF-BUDAT,

TCODE LIKE BKPF-TCODE,

AWKEY LIKE BKPF-AWKEY,

END OF T_BKPF.

DATA:

IT_BSEG TYPE STANDARD TABLE OF T_BSEG INITIAL SIZE 0 WITH HEADER LINE,

IT_BKPF TYPE STANDARD TABLE OF T_BKPF INITIAL SIZE 0 WITH HEADER LINE.

SELECT BELNR WERKS LIFNR FWBAS MWSKZ WRBTR SGTXT MATNR SHKZG PRCTR

FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG

WHERE BUKRS IN S_BUKRS

AND HKONT IN S_HKONT.

IF IT_BSEG[] IS NOT INITIAL..

SELECT BELNR BUDAT TCODE AWKEY

FROM BKPF

INTO CORRESPONDING FIELDS OF TABLE IT_BKPF

FOR ALL ENTRIES IN IT_BSEG

WHERE BELNR = IT_BSEG-BELNR

AND BUDAT IN S_DATE.

ENDIF.

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 10:55:20 GMT

HI,

Do not use into corresponding fiedls define the fiedls in the same order in the TYPES declaration and then use the same order in the select query.

Thanks CSR.

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 10:55:35 GMT

HI,

gud query.

try to mension all the key fileds in WHERE clause of first select statement.

gavas.

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 10:59:13 GMT

Hi,

Always check the driver internal tables is not empty, while using FOR ALL ENTRIES

Avoid for all entries in JOINS

Try to avoid joins and use FOR ALL ENTRIES.

Try to restrict the joins to 1 level only ie only for tables

Avoid using Select *.

Avoid having multiple Selects from the same table in the same object.

Try to minimize the number of variables to save memory.

The sequence of fields in 'where clause' must be as per primary/secondary index ( if any)

Avoid creation of index as far as possible

Avoid operators like <>, > , < & like % in where clause conditions

Avoid select/select single statements in loops.

Try to use 'binary search' in READ internal table. Ensure table is sorted before using BINARY SEARCH.

Avoid using aggregate functions (SUM, MAX etc) in selects ( GROUP BY , HAVING,)

Avoid using ORDER BY in selects

Avoid Nested Selects

Avoid Nested Loops of Internal Tables

Try to use FIELD SYMBOLS.

Try to avoid into Corresponding Fields of

Avoid using Select Distinct, Use DELETE ADJACENT

Also by going to transaction SE30->tips and tricks you can get the idea

Reward if helpful.

Regards,

Harini.S

Re: regardinf faster select statement

varma_narayana — Thu, 04 Oct 2007 11:00:11 GMT

hI ..

Avoid the Option

INTO CORRESPONDING FIELDS

In all ur queries.

One more thing: BSEG is a Cluster table . So it gives poor performance.

Better to fetch from BSIK BSAK BSID BSAD tables which gives the same data as BSEG.

REWARD IF HELPFUL.

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 11:05:28 GMT

DON'T USE CORRESPONDING FIELDS OPTION

JUST KEEP THE ORDER OF THE INTERNAL TABLE AS THE ORDER OF THE SELECT QUERY

THEN IT WILL BE SOME FASTR THAN THE BEFORE

IF STILL IT HAS THE SAME PROBLEM THEN ASK UR BASIS PEOPLE TO REDUCE THE PARAMETER LOAD THEN IT WILL EXECUTE VERY FASTLY

REWARD IF USEFULL

Re: regardinf faster select statement

suredarreddy_pulimamidi — Thu, 04 Oct 2007 11:09:57 GMT

try to avoid using cluster tables in this case BSEG and also include key fields in the where condition and use FOR ALL ENTRIES to avoid joins.

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 11:10:12 GMT

but my all require fields are from BSEG........only..........

i hv tried all things but still giving me dump...........

pls,do some help

Re: regardinf faster select statement

Clemenss — Thu, 04 Oct 2007 11:20:32 GMT

Hi CS,

don't confuse the user. Please proove that INTO CORRESPONDING FIELDS has any impact on performance.

INTO CORRESPONDING FIELDS is a SAFE option with liitle or no maintenance impact if anything changes.

Regards,

Clemens

Re: regardinf faster select statement

Clemenss — Thu, 04 Oct 2007 12:59:56 GMT

Hi SAP Abap ,

I didn't have the time before lunch, now let me tell you that it is not advisable to select directly from BSEG due to the amount of entries.

You may select the headers within a date range first - this will reduce the number of records drastically. Or, what is the best way, select from a secondary index first:

BSAD : Accounting: Secondary Index for Customers (Cleared Items)

BSAK : Accounting: Secondary Index for Vendors (Cleared Items)

BSAS : Accounting: Secondary Index for G/L Accounts (Cleared Item

BSEC : One-Time Account Data Document Segment

BSEG : Accounting Document Segment

BSID : Accounting: Secondary Index for Customers

BSIK : Accounting: Secondary Index for Vendors

BSIS : Accounting: Secondary Index for G/L Accounts

I think in your case, BSIS, with BUKRS and HKOnT as key fields, will be the best, maybe BSAS for completed items will be even better. Check it out: First fetch GJAHR BELNR BUZEI from BSIS/BSAS, then the rest from BSEG using for all entries.

Regards,

Clemens

Re: regardinf faster select statement

Former Member — Thu, 04 Oct 2007 14:00:06 GMT

I wonder if therre's an error in your logic. You are trying to SELECT from BSEG both HKONT and LIFNR from the same row. These are fields that are usually on offsetting entries, not the same entries. Can you tell us what your requirements are?

Rob