Report is running long time

[Former Member]

Dear all ,

We have zreport in PROD and report worked fine till last month.

Zreport we are using every month for service tax .

The report will get execute with in 10 to 15 minutes .

We (Abaper) have checked for this month zreport is running long time , its not giving any error .

Kindly suggest

Regards

[Former Member]

Resolved by ABAPER

[Former Member]

SELECT
"KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,
"REBZJ" , "AUGBL" , "BUZEI"
FROM
"BSAD"

Exxxxxxxxxxxxxxxxxxxpensive select statement

Check with your business requirement and change the logic of ABAP program by taking help of ABAP.

And take a look at the size of BSAD table and Indexes you are using on that table, If required rebuild old ones and create new indices with valid fields.

Regards,

Nick Loy

[Former Member]

Hi,

Can note 1349849 be applied. When checking in sm50 the cluster table rfglb is been read. So is there is a solution if the above note is applied. But nowhere in the program there is direct access to the table.

Thanks in advance,

Sathiesh

Edited by: satheesh0812 on Oct 8, 2010 10:14 AM

[Former Member]

Note 1349849 is only valid for 500 to 604 and for tables BSEG/RFBLG and you are having issue with BSAD.

can you please check when last stats executed for table BSAD via db20.

Also you can do analysis using SE30 for your report why it is taking long time.

[Former Member]

Dear Nick,

Let me know how to check the size of BSAD table and Indexes are using on that table ?

kindly suggets

[Former Member]

Dear Sunil,

please check below for last stats executed for table BSAD via db20

Table BSAD

Statistics are current (|Changes| < 50 %)

New Method E

New Sample Size P10

Old Method E Date 25.07.2010

Old Sample Size P10 Time 15:01:53

Old Number 443,010 Deviation Old -> New 23 %

New Number 543,316 Deviation New -> Old 18- %

Inserted Rows 110,614 Percentage Too Old 25 %

Changed Rows 31 Percentage Too Old 0 %

Deleted Rows 10,308 Percentage Too New 2 %

Use A

Active Flag A

Analysis Method

Sample Size

Kindly suggest

[Former Member]

Hi,

The z report contains select queries for bsad and bseg table.

In db20 while checking the table statistics for bseg i get the error

Table BSEG does not exist in the ABAP Dictionary.

But the table(BSEG) is active when checked in se11.

but for the bsad table the statistics is

Table BSAD

Statistics are current (|Changes| < 50 %)

New Method E

New Sample Size P10

Old Method E Date 25.07.2010

Old Sample Size P10 Time 15:01:53

Old Number 443,010 Deviation Old -> New 23 %

New Number 543,316 Deviation New -> Old 18- %

Inserted Rows 110,614 Percentage Too Old 25 %

Changed Rows 31 Percentage Too Old 0 %

Deleted Rows 10,308 Percentage Too New 2 %

Use A

Active Flag A

Analysis Method

Sample Size

Any suggestions.

When trying to trace using se30 we are not getting any results becos the report is not generated at all. But the status is running.

Edited by: satheesh0812 on Oct 8, 2010 10:49 AM

Edited by: satheesh0812 on Oct 8, 2010 10:50 AM

[Former Member]

In DB20 can you please create stats for table BSAD?

BSEG is a Cluster Table hence you can't find in DB20.

[Former Member]

Dear Suil,

let me know how to create stats In DB20 for table BSAD?

Kindly suggest

[Former Member]

DB20 --> enter table name --> BSAD --> click on create (cntrl+F2)

[Former Member]

Dear Sunil ,

i have created Statistics for Table BSAD ,pls check below.

Table BSAD

Statistics are current (|Changes| < 50 %)

New Method E

New Sample Size P10

Old Method E Date 08.10.2010

Old Sample Size P10 Time 14:30:18

Old Number 542,990 Deviation Old -> New 0 %

New Number 542,990 Deviation New -> Old 0 %

Inserted Rows 0 Percentage Too Old 0 %

Changed Rows 0 Percentage Too Old 0 %

Deleted Rows 0 Percentage Too New 0 %

Use A

Active Flag A

Analysis Method

Sample Size

how to proceed further for my issue .

kindly suggest

[Former Member]

can you now try to execute the report again if possible? and check in sm50 or SM66 what are the tables getting called. if possible create the stats for these tables. Also check the ST06 CPU util, IO wait.

[Former Member]

Hi,

While checking in sm50 the tables that are called are BSAD,BSID and RFGLB. RFGLB is the table that is been continuossly called but nowhere in the program we have accessed the RFGLB table.

[Former Member]

The table RFGLB may be called internally by other tables.

When you monitor the job in SM50 did you observed the action on each table? is it sequential read or direct read. also what about ST06 CPU and IO Wait.

[markus_doehr2]

>

>

SELECT
> "KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,
> "REBZJ" , "AUGBL" , "BUZEI"
> FROM
> "BSAD"

>

So this means that you real all data of table BSAD. I think your nested select is just taking so much time.

I suggest, as already advised, to check with the develop to either use a WHERE clause to make the amount of data to be read smaller or use other methods like joins to get just the data you need.

Without knowing the logic of the program and what its doing it's pointless to start the analysis from a database approach.

Markus

[Former Member]

Hi,

In the program the select query for bsad is as follows:

SELECT kunnr belnr budat dmbtr augbl rebzg gjahr XBLNR HKONT BUDAT AUGDT UMSKZ SHKZG AUGGJ REBZJ PSWSL

FROM BSAD

INTO CORRESPONDING FIELDS OF TABLE I_BSAD

WHERE kunnr IN s_kunnr

AND bukrs EQ p_bukrs

AND gjahr EQ p_gjahr

AND budat IN s_budat

• AND shkzg EQ 'H'

AND blart EQ 'DZ'

AND ( UMSKZ EQ 'X'

OR UMSKZ EQ ' ' )

AND HKONT NE '0011404501'

AND HKONT NE '0011404502'.

When checking in the debugging mode the program is stuck after the statement

IF I_BSAD[] IS NOT INITIAL.

SELECT DMBTR BELNR REBZG BUZEI FROM BSEG

APPENDING TABLE I_BSAD_ST

FOR ALL ENTRIES IN I_BSAD

WHERE BELNR EQ I_BSAD-REBZG

AND BUKRS EQ P_BUKRS

AND GJAHR EQ I_BSAD-REBZJ " ( GJAHR EQ I_BSAD-GJAHR OR

AND HKONT EQ '0011404501'

OR HKONT EQ '0011404502'.

ENDIF.

Kindly suggest.

[former_member204746]

run the job

while it runs, check inSM50

double-click on background process running your job.

check SQL request being run.

adapt index or DB stats on that table.

[Former Member]

Dear Eric,

Now job is running and i have checked SM50 and I have double clicked the dialog process running our job .

No Ty. PID Status Reasn Start Err Sem CPU Time Cl. User

1 DIA 1331222 Running Yes 147:44 102 320 S2K_BASIS

Report / Spool action

ZFI_SERVICE_TAX

Main Program

ZFI_SERVICE_TAX

Action Table

Sequential Read BSAD

Waiting f. since

Last SQL statement

SELECT

"KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,

"REBZJ" , "AUGBL" , "BUZEI"

FROM

"BSAD"

WHERE

"MANDT" = :A0 AND "BUKRS" = :A1 AND "AUGGJ" = :A2 AND "AUGBL" = :A3 AND "PSWSL" = :A4 AND "SHKZG" = :A5

AND ( "UMSKZ" = :A6 OR "UMSKZ" = :A7 ) OR "MANDT" = :A8 AND "BUKRS" = :A9 AND "AUGGJ" = :A10 AND

"AUGBL" = :A11 AND "PSWSL" = :A12 AND "SHKZG" = :A13 AND ( "UMSKZ" = :A14 OR "UMSKZ" = :A15 ) OR

"MANDT" = :A16 AND "BUKRS" = :A17 AND "AUGGJ" = :A18 AND "AUGBL" = :A19 AND "PSWSL" = :A20 AND "SHKZG" =

:A21 AND ( "UMSKZ" = :A22 OR "UMSKZ" = :A23 ) OR "MANDT" = :A24 AND "BUKRS" = :A25 AND "AUGGJ" = :A26

AND "AUGBL" = :A27 AND "PSWSL" = :A28 AND "SHKZG" = :A29 AND ( "UMSKZ" = :A30 OR "UMSKZ" = :A31 ) OR

"MANDT" = :A32 AND "BUKRS" = :A33 AND "AUGGJ" = :A34 AND "AUGBL" = :A35 AND "PSWSL" = :A36 AND "SHKZG" =

:A37 AND ( "UMSKZ" = :A38 OR "UMSKZ" = :A39 )#

Database Number Time (usec) Recs.

Direct Read 19 21,509 6

Sequential Read 47 101906536 632

Insert 0 0 0

Update 0 0 0

Delete 0 0 0

Sources 960,273 (Bytes)

RSQL 132,880 (Bytes)

Commit 0

DB Procedure Calls 0 0

Roles / Memory Number Time (usec)

Roll In 1 2,159

Roll Out 0 0

Roll 253,952 (Bytes)

Page 73,728 (Bytes)

Memory (Total) 12,581,164 (Bytes)

Memory(Total Private) 0 (Bytes)

Memory (used) 4,189,856 (Bytes)

Memory (Max. Transaction) 25,140,032 (Bytes)

Memory (Max. Dialog Step) 12,569,568 (Bytes)

Development Environ. Number Time (usec)

Generate 0

Load Report 86,508

Load Screen 960,273 (Bytes) 0

Load CUA 50

Read Dictionary 0

RFC 0 Calls 0.000000000000000E+00

Queue API 0 (Bytes) 0

Ad Opcodes 0 0

Kindly suggest

[Former Member]

We (Abaper) have checked for this month zreport is running long time

Do the same in debugging mode, then you can find out where it is taking time.

Regards,

Nick Loy

[markus_doehr2]

- check your selection criterias

- check your database statistics

- run the report through transaction SE30 and see where the time is being consumed

Markus

[Former Member]

Dear Marcus ,

I have executed only one day ,still there is no output its keep on running .

- I have checked our database statistics from DB13 , its successfully completed .

- i have run the report through transaction SE30 and enen though its not completed .

Kindly suggest .