topic Re: Still not solved problem in Application Development and Automation Discussions

Still not solved problem

Former Member — Thu, 28 Jun 2007 04:51:14 GMT

Hello Friends,

I am using 6 tables in my report.i want to display recores by AT NEW and Related stuff.

Can i use following code for query and internal tables ?

or Sujjest me Better code.

to use loop within loop is not good idea.

I want to disply Records

total Document type wise ->year wise -> Month wise -> Sales Order Number wise->Item Numberwise -> Production order numberwise

This is interactive report of 8-9 screens !!

Main problem is to make AT new with different loop !!

Sujjest me with code !!

Points will be rewarded soon !!

Regards,

Nimesh Master

TABLE DECLARATION ************************

TABLES : VBAK,AFPO,AUFM,MAKT,KNA1,VBAP.

INTERNAL TABLE DECLARATION *******************

DATA : BEGIN OF T1 OCCURS 0,

AUART TYPE VBAK-AUART,

NETWR TYPE VBAK-NETWR,

KUNNR TYPE VBAK-KUNNR,

VBELN TYPE VBAK-VBELN,

AUDAT TYPE VBAK-AUDAT,

END OF T1.

DATA : BEGIN OF T2 OCCURS 0,

NETWR TYPE VBAP-NETWR,

POSNR TYPE VBAP-POSNR,

END OF T2.

DATA : BEGIN OF T3 OCCURS 0,

AUFNR TYPE AFPO-AUFNR,

KDPOS TYPE AFPO-KDPOS,

KDAUF TYPE AFPO-KDAUF,

PSMNG TYPE AFPO-PSMNG,

WEMNG TYPE AFPO-WEMNG,

END OF T3.

DATA : BEGIN OF T4 OCCURS 0,

DMBTR TYPE AUFM-DMBTR,

MATNR TYPE AUFM-MATNR,

ELIKZ TYPE AUFM-ELIKZ,

END OF T4.

DATA : BEGIN OF T5 OCCURS 0,

NAME1 TYPE KNA1-NAME1,

END OF T5.

DATA : BEGIN OF T6 OCCURS 0,

MAKTX TYPE MAKT-MAKTX,

END OF T6.

FREE T1.

SELECT VBELN NETWR AUART AUDAT KUNNR FROM VBAK INTO CORRESPONDING FIELDS

OF TABLE T1 WHERE AUART IN AUART AND VBELN IN VBELN.

FREE T2.

SELECT NETWR POSNR FROM VBAP INTO CORRESPONDING FIELDS OF TABLE T2

FOR ALL ENTRIES IN T1 WHERE VBELN = T1-VBELN .

FREE T3.

SELECT AUFNR PSMNG WEMNG KDPOS FROM AFPO INTO CORRESPONDING FIELDS OF

TABLE T3 FOR ALL ENTRIES IN T1 WHERE KDAUF = T1-VBELN .

FREE T4.

SELECT DMBTR MATNR ELIKZ FROM AUFM INTO CORRESPONDING FIELDS OF

TABLE T4 FOR ALL ENTRIES IN T3 WHERE AUFNR = T3-AUFNR.

FREE T6.

SELECT MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF TABLE T6 WHERE

MATNR = T4-MATNR.

FREE T5.

SELECT NAME1 FROM KNA1 INTO CORRESPONDING FIELDS OF TABLE T5 WHERE

KUNNR = T1-KUNNR.

LOOP AT T1.

AT NEW VBELN.

WRITE : / T1-VBELN .

ENDAT.

AT NEW AUFNR.

WRITE : / T1-AUFNR.

ENDAT.

AT END OF AUFNR.

SUM.

IF T1-PSMNG = T1-WEMNG.

T1-CLOSE_ORD1 = T1-CLOSE_ORD1 + T1-DMBTR.

ELSE.

T1-OPEN_ORD1 = T1-OPEN_ORD1 + T1-DMBTR.

ENDIF.

TOT_DMBTR_C = TOT_DMBTR_C + T1-CLOSE_ORD1.

TOT_DMBTR_O = TOT_DMBTR_O + T1-OPEN_ORD1 .

ENDAT.

AT END OF VBELN.

SUM.

P = TOT_DMBTR_C.

Q = TOT_DMBTR_O.

TMP_OC_SALES = NET_PRICE - P.

TMP_PER_SALES = ( TMP_OC_SALES * 100 ) / NET_PRICE .

TMP_TOT_MAT = P + Q.

TMP_PER_MAT = ( TMP_TOT_MAT * 100 ) / NET_PRICE.

TOT_NET_SALES_A = TOT_NET_SALES_A + NET_PRICE.

TOT_P_A = TOT_P_A + P.

TOT_Q_A = TOT_Q_A + Q.

ENDIF.

FORMAT COLOR 2 ON.

WRITE : 20 NET_PRICE,

50 P,

65 Q,

83 TMP_OC_SALES,

100 TMP_PER_SALES,'%',

120 TMP_TOT_MAT ,

139 TMP_PER_MAT,'%'.

FORMAT COLOR 2 OFF.

ENDAT.

ENDLOOP.

1. Tell me is it appropriate to take same number of INTERNAL TABLE as DDIC

TABLE ??

2. Wat should be the queries for them ?

3. How i use AT NEW with Different Loop for Internal tables ??

Re: Still not solved problem

Former Member — Thu, 28 Jun 2007 04:56:33 GMT

Hi Nimesh,

1. Tell me is it appropriate to take same number of INTERNAL TABLE as DDIC

TABLE ??

- Not necessary. You can use join so that the number of table can be reduced. To use join or not depends on the tables and the number of entries expected.

2. Wat should be the queries for them ?

Answered in 1.

3. How i use AT NEW with Different Loop for Internal tables ??

_ if you have diffrent internal tables and you want use them in the final output, don't use AT NEW use READ statement. It is easy to use and performance will be very good.

Regards,

Atish

Re: Still not solved problem

Former Member — Thu, 28 Jun 2007 05:06:49 GMT

I am new bie in this performance tunning so can u sujjest me queries and read table code.?

So, after that i will follow same format for further projects.

Regards,

n.Master

Re: Still not solved problem

Former Member — Thu, 28 Jun 2007 06:21:25 GMT

Performance Tuning

1) Dont use nested select statements

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234

ABAP performance depends upon various factors and in devicded in three parts:

1. Database

2. ABAP

3. System

Run Any program using SE30 (performance analys) to improve performance refer to tips and trics section of SE30, Always remember that ABAP perfirmance is improved when there is least load on Database.

u can get an interactive grap in SE30 regarding this with a file.

also if u find runtime of parts of codes then use :

Switch on RTA Dynamically within ABAP Code

*To turn runtim analysis on within ABAP code insert the following code

SET RUN TIME ANALYZER ON.

*To turn runtim analysis off within ABAP code insert the following code

SET RUN TIME ANALYZER OFF.

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

Check the following Links

http://www.sapgenie.com/abap/performance.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

check the below link

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

See the following link if it's any help:

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Check also http://service.sap.com/performance

and

books like

http://www.sap-press.com/product.cfm?account=&product=H951

http://www.sap-press.com/product.cfm?account=&product=H973

http://www.sap-img.com/abap/more-than-100-abap-interview-faqs.htm

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Performance tuning for Data Selection Statement

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Debugger

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617ca9e68c11d2b2ab080009b43351/content.htm

http://www.cba.nau.edu/haney-j/CIS497/Assignments/Debugging.doc

http://help.sap.com/saphelp_erp2005/helpdata/en/b3/d322540c3beb4ba53795784eebb680/frameset.htm

Run Time Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c6/617cafe68c11d2b2ab080009b43351/content.htm

SQL trace

http://help.sap.com/saphelp_47x200/helpdata/en/d1/801f7c454211d189710000e8322d00/content.htm

CATT - Computer Aided Testing Too

http://help.sap.com/saphelp_47x200/helpdata/en/b3/410b37233f7c6fe10000009b38f936/frameset.htm

Test Workbench

http://help.sap.com/saphelp_47x200/helpdata/en/a8/157235d0fa8742e10000009b38f889/frameset.htm

Coverage Analyser

http://help.sap.com/saphelp_47x200/helpdata/en/c7/af9a79061a11d4b3d4080009b43351/content.htm

Runtime Monitor

http://help.sap.com/saphelp_47x200/helpdata/en/b5/fa121cc15911d5993d00508b6b8b11/content.htm

Memory Inspector

http://help.sap.com/saphelp_47x200/helpdata/en/a2/e5fc84cc87964cb2c29f584152d74e/content.htm

ECATT - Extended Computer Aided testing tool.

http://help.sap.com/saphelp_47x200/helpdata/en/20/e81c3b84e65e7be10000000a11402f/frameset.htm

Just refer to these links...

You can go to the transaction SE30 to have the runtime analysis of your program.Also try the transaction SCI , which is SAP Code Inspector.

Reward points for useful Answers

Regards

Anji

Re: Still not solved problem

Former Member — Thu, 28 Jun 2007 06:23:27 GMT

Hi,

I want to suggest few points according to your code.

1). In the select query you have written.. you are using into corresponding fields of itb..which reduces the performance. instead create the internal table with the fields u want from the table on which u r writing a select query.. and according to that order create the internal table.

2).Before using control break statements, the best approach is to sort the internal table..

Answers to your question.

1).Tell me is it appropriate to take same number of INTERNAL TABLE as DDIC

TABLE ??

A). Not necessarily as suggested by one of our friend. You can use joins ( though its not best method when it comes to performance)

Re: Still not solved problem

Former Member — Thu, 28 Jun 2007 13:19:31 GMT

Before you do the first SELECT, make sure that select-option or range table vbeln is not empty. For the subsequent SELECTs, make sure that the FOR ALL ENTRIES tables are not empty; otherwise, it will select all and not be able to use the index.

Rob

Re: Still not solved problem

Former Member — Fri, 29 Jun 2007 04:34:49 GMT

Can any one put standard code format for this example which i can use in further reports also?

It will be helpful in future also.

Give me the code for this problem .ie. example of creating internal table , creating such 5 tables query, loop with internal table.

CAUTION : Performance should be fast.

Re: Still not solved problem

Former Member — Fri, 29 Jun 2007 04:43:12 GMT

Hi Nimesh,

Just can you give yourself a try based on all the suggestions and come up with code so that we can do the required changes.

Regards,

Atish

Re: Still not solved problem

Former Member — Fri, 29 Jun 2007 05:03:13 GMT

i tried !! but some people say USE INNER JOIN , some say NOT USE INNER JOIN.and such thing for other stuff

so i m comfused related to it.

so i get proper format with mant tables query stuff. then i will use it further as per performance !!

Regards,

N.Master

Re: Still not solved problem

Former Member — Fri, 29 Jun 2007 05:19:56 GMT

i am gving u the silmilar kind of code where i used 4-5 tables.

1> used for all entries tat will increase the performance

2> use read instead of at

3> use work area

REPORT ZBH_FI_ALV1 No Standard Page Heading.

TABLES:T001,SKA1,SKB1,BKPF,BSEG.

TYPE-POOLS:slis.

----

TYPES DECLARTIONS

----

TYPES:begin of str_bkpf,

blart type blart,

bukrs type bukrs,

belnr type bseg-belnr,

gjahr type gjahr,

budat type budat,

end of str_bkpf.

TYPES: begin of str_bseg,

belnr type belnr_d,

gjahr type gjahr,

buzei type buzei,

bschl type bschl,

hkont type hkont,

bukrs type bukrs,

blart type blart,

budat type bkpf-budat,

saknr type saknr,

fstag type fstag,

ktopl type ktopl,

bilkt type bilkt,

end of str_bseg.

TYPES:begin of str_t001,

bukrs type bukrs,

ktopl type ktopl,

end of str_t001.

TYPES:begin of str_ska1,

bilkt type bilkt,

ktopl type ktopl,

saknr type saknr,

end of str_ska1.

TYPES:begin of str_skb1,

fstag type fstag,

bukrs type bukrs,

saknr type saknr,

end of str_skb1.

----

DATA DECLARATIONS

----

DATA:it_bkpf type standard table of str_bkpf ,

it_bseg type standard table of str_bseg ,

it_t001 type standard table of str_t001 ,

it_ska1 type standard table of str_ska1 ,

it_skb1 type standard table of str_skb1 ,

fieldcat type slis_t_fieldcat_alv WITH HEADER LINE,

events type slis_t_event WITH HEADER LINE,

layout type slis_layout_alv,

layout type slis_layout_alv1 ,
ALV type SY-REPID.

data:w_index type sy-tabix.

data: wa_bkpf like line of it_bkpf,

wa_bseg like line of it_bseg,

wa_t001 like line of it_t001,

wa_ska1 like line of it_ska1,

wa_skb1 like line of it_skb1.

----

INPUT VARIABLES

----

SELECT-OPTIONS:s_BUKRS FOR BKPF-BUKRS obligatory,

s_BLART FOR BKPF-BLART obligatory,

s_BUDAT FOR BKPF-BUDAT,

s_BSCHL FOR BSEG-BSCHL,

s_HKONT FOR BSEG-HKONT.

----

SELECT STATEMENTS

----

START-OF-SELECTION.

select blart "Document type

bukrs "Company Code

belnr "Accounting Document Number

gjahr "Fiscal Year

budat "Posting Date in the Document

from bkpf

into

table it_bkpf

where bukrs in s_bukrs

and blart in s_blart

and budat in s_BUDAT.

check sy-subrc = 0.

if not it_bkpf[] is initial.

select belnr "Accounting Document Number

gjahr "Fiscal Year

buzei "Number of Line Item Within Accounting Document

bschl "Posting Key

hkont "General Ledger Account

bukrs "Company Code

from bseg

into table it_bseg

for all entries in it_bkpf

where belnr = it_bkpf-belnr

and gjahr = it_bkpf-gjahr

and bukrs = it_bkpf-bukrs.

endif.

if not s_BSCHL[] is initial.

delete it_bseg where not bschl in s_bschl.

endif.

if not s_HKONT[] is initial.

delete it_bseg where not hkont in s_hkont.

endif.

if it_bseg[] is not initial.

sort it_bseg by bukrs gjahr belnr.

refresh it_t001.

SELECT bukrs "Company Code

ktopl "Chart of Accounts

from T001

into table it_t001

where bukrs in s_bukrs.

if not it_t001[] is initial.

sort it_t001 by bukrs.

refresh it_ska1.

SELECT bilkt "Group Account Number

ktopl "Chart of Accounts

saknr "G/L account number

from SKA1

into table it_ska1

for all entries in it_t001

where ktopl = it_t001-ktopl.

if not it_ska1[] is initial.

sort it_ska1.

refresh it_skb1.

SELECT fstag "Field status group

bukrs "Company code

saknr "G/L account number

from skb1

into table it_skb1

for all entries in it_ska1

where bukrs in s_bukrs and

SAKNR = it_ska1-saknr.

endif.

loop at it_bseg into wa_bseg.

w_index = sy-tabix.

read table it_bkpf into wa_bkpf with key BUKRS = wa_bseg-bukrs.

if sy-subrc = 0.

wa_bseg-blart = wa_bkpf-blart.

Read Table it_t001 into wa_t001 with key bukrs = wa_bseg-bukrs.

if sy-subrc = 0.

wa_bseg-ktopl = wa_t001-ktopl.

if sy-subrc = 0.

Read table it_ska1 into wa_ska1 with key ktopl = wa_bseg-ktopl.

wa_bseg-saknr = wa_ska1-saknr.

wa_bseg-bilkt = wa_ska1-bilkt.

if sy-subrc = 0.

Read Table it_skb1 into wa_skb1 with key bukrs = wa_bseg-bukrs saknr = wa_bseg-hkont.

wa_bseg-fstag = wa_skb1-fstag.

modify it_bseg from wa_bseg index w_index transporting ktopl bilkt saknr fstag blart.

endif.

endloop.

regards

bhanu

all the best

Re: Still not solved problem

Former Member — Sun, 01 Jul 2007 00:02:20 GMT

First thing is look at the selections and the scope.

I understand you may not have much data now but why is BUDAT not mandatory? If I remember well (It's been a few years) this is one index of BKPF....

Even as a range it is still way better than allowing the selection for the whole timeframe present in the DB...

So yes you really need to take a critical view at what you are trying to accomplish and how you are going about it...