Long runtime in table HRP1001

[Former Member]

Hi,

We have recently done Unicode Conversion of our ECC 6 System. After Unicode Conversion, we have found that there is one customer program which is taking too much time to Execute. This was working fine when the system was non-unicode.

We have found out through SQL Trace that the customer program is taking too much time in fetching records from table HRP1001. We have checked the program and indexes for the table. These are same as they are in other non-unicode systems where it is working fine.

Kindly Advise.

Regards,

Prasad

[Former Member]

Hi Prasad,

have you checked how the execution plan for the query looks like? You should also check your statistics. May be the statistics are ok, but you get a different plan due to the reorganization effect of the migration. In this case you may copy "good" statistics from an other system into your Unicode system. This is a database depended task. You should ask you database administrator for this.

Sending the plan (of your SQL statement) would may be help too.

Regards

Ralph

[Former Member]

Hi,

In the main program, a module-pool program is called which in turn calls a function module. The query (the one below) used in this function module is what is causing the long run-time.

select single * from hrp1001 where

otype = 'S' and

objid = w_plans and

plvar = '01' and

rsign = 'A' and

relat = '002' and

sclas = 'S' and

endda ge begda and

begda le endda.

[Former Member]

Hi Prasad,

the statement in you reply seems to be fine. It should use part of the primary key. Could you send the execution plan? In DB2 (DB6) it should look like:

0 SELECT STATEMENT ( Estimated Costs = 3,951E+01 [timerons] )

1 RETURN

2 FETCH HRP1001

3 IXSCAN HRP1001~0 #key columns: 6

Important is the #key columns used. It should be 6 like in this example. In Oracle it should like:

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 1 )

2 TABLE ACCESS BY INDEX ROWID HRP1001

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 5.874 Estim. IO-Costs = 1

Filter Predicates

1 INDEX RANGE SCAN HRP1001~0

( Estim. Costs = 1 , Estim. #Rows = 1 )

Search Columns: 6

Estim. CPU-Costs = 4.323 Estim. IO-Costs = 1

Access Predicates Filter Predicates

You see again that 6 columns of the index are used. The Costs could be different in your system.

In other databases it looks different, again.

If you see a different plan, you should check the statistics of the table and all indexes.

Regards

Ralph

[Former Member]

Hi,

Please find the below ExecutionPlan...My Database is ORACLE.

`````````````````````````````

SQL Statement

SELECT

/*+

FIRST_ROWS (1)

*/

*

FROM

"HRP1001"

WHERE

"MANDT" = :A0 AND "OTYPE" = :A1 AND "OBJID" = :A2 AND "PLVAR" = :A3 AND "RSIGN" = :A4 AND "RELAT"

= :A5 AND "SCLAS" = :A6 AND "ENDDA" >= :A7 AND "BEGDA" <= :A8 AND ROWNUM <= :A9

Execution Plan

SELECT STATEMENT ( Estimated Costs = 4 , Estimated #Rows = 1 )

3 COUNT STOPKEY

Filter Predicates

2 TABLE ACCESS BY INDEX ROWID HRP1001

( Estim. Costs = 4 , Estim. #Rows = 1 )

Estim. CPU-Costs = 29,328 Estim. IO-Costs = 4

Filter Predicates

1 INDEX RANGE SCAN HRP1001~7

( Estim. Costs = 3 , Estim. #Rows = 1 )

Search Columns: 5

Estim. CPU-Costs = 21,564 Estim. IO-Costs = 3

Access Predicates

[Former Member]

Hi Prasad,

your database seems to favor index ~7 instead of the wright one ~0. Could you check if your primary key is valid in your database? You need command line access to your database server. Open sqlplus "/ as sysdba" as ora<sid> user on Unix or <sid>adm on Windows. then you could check by executing the following statement:

SQL> select index_name, status from dba_indexes where owner = '<Schema Owner>' and table_name = 'HRP1001';

You should get something like:

INDEX_NAME STATUS

-

---

-

---

HRP1001~6 VALID

HRP1001~0 VALID

HRP1001~1 VALID

HRP1001~2 VALID

HRP1001~3 VALID

HRP1001~4 VALID

HRP1001~5 VALID

HRP1001~7 VALID

8 rows selected.

If ~0 is not valid as an example, you should rebuild it by executing:

alter index <Schema Owner>."HRP1001~0" rebuild;

(replace <Schema Owner> by the content (upper case) of the environment variable dbs_ora_schema.

If all indexes are valid, you should run update statistics on the indexes with DB20.

Regards

Ralph

[Former Member]

Hi Ralph,

I have got the following output from my database:

SQL> select index_name, status from dba_indexes where owner = 'SAPR3' and table_name = 'HRP1001';

INDEX_NAME STATUS

-

---

-

---

HRP1001~4 VALID

HRP1001~5 VALID

HRP1001~6 VALID

HRP1001~7 VALID

HRP1001~1 VALID

HRP1001~3 VALID

HRP1001~0 VALID

HRP1001~2 VALID

8 rows selected.

I have checked in DB20 also for table statistics. For table HRP1001, it is showing green symbol to represent as statistics are current.

Please advise what else could be the problem.

[Former Member]

Hi Prasad,

you may run an Optimizer trace. Please open transaction ST05 and press the button "Enter SQL Statement". Then copy & paste your statement:

SELECT

/*+

FIRST_ROWS (1)

*/

*

FROM

"HRP1001"

WHERE

"MANDT" = :A0 AND "OTYPE" = :A1 AND "OBJID" = :A2 AND "PLVAR" = :A3 AND "RSIGN" = :A4 AND "RELAT"

= :A5 AND "SCLAS" = :A6 AND "ENDDA" >= :A7 AND "BEGDA" <= :A8 AND ROWNUM <= :A9

and press Explain. In the next view press the Optimizer trace button. You should then get an output which contains some lines like:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: HRP1001 Alias: HRP1001

#Rows: 1553900 #Blks: 164435 AvgRowLen: 130.00

Index Stats::

Index: HRP1001~0 Col#: 1 2 3 4 5 6 7 8 9 10 11 12

LVLS: 2 #LB: 16586 #DK: 1522236 LB/K: 1.00 DB/K: 1.00 CLUF: 48990.00

Index: HRP1001~1 Col#: 14 4 10

LVLS: 2 #LB: 10476 #DK: 123720 LB/K: 1.00 DB/K: 6.00 CLUF: 779028.00

Index: HRP1001~2 Col#: 14 15 4 10

LVLS: 2 #LB: 11605 #DK: 119527 LB/K: 1.00 DB/K: 6.00 CLUF: 800565.00

Index: HRP1001~3 Col#: 22 21 15 4 10

LVLS: 2 #LB: 12595 #DK: 129258 LB/K: 1.00 DB/K: 7.00 CLUF: 908948.00

Index: HRP1001~4 Col#: 14 15 21 4 10

LVLS: 2 #LB: 12750 #DK: 128359 LB/K: 1.00 DB/K: 6.00 CLUF: 823327.00

Index: HRP1001~5 Col#: 1 24

LVLS: 2 #LB: 5390 #DK: 68957 LB/K: 1.00 DB/K: 1.00 CLUF: 130526.00

Index: HRP1001~6 Col#: 14 15 11 4 10

LVLS: 2 #LB: 15301 #DK: 271727 LB/K: 1.00 DB/K: 5.00 CLUF: 1467702.00

Index: HRP1001~7 Col#: 6 21 2 4 10

LVLS: 2 #LB: 8798 #DK: 228 LB/K: 38.00 DB/K: 329.00 CLUF: 75071.00

***************************************

...

Access Path: index (RangeScan)

Index: HRP1001~0

resc_io: 4.00 resc_cpu: 29296

ix_sel: 3.0163e-10 ix_sel_with_filters: 2.4432e-14

Cost: 0.80 Resp: 0.80 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~1

resc_io: 10946.00 resc_cpu: 387165434

ix_sel: 1 ix_sel_with_filters: 6.0000e-04

Cost: 2247.85 Resp: 2247.85 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~2

resc_io: 12088.00 resc_cpu: 382302983

ix_sel: 1 ix_sel_with_filters: 6.0000e-04

Cost: 2475.64 Resp: 2475.64 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~3

resc_io: 12626.00 resc_cpu: 404505885

ix_sel: 1 ix_sel_with_filters: 3.1579e-05

Cost: 2586.05 Resp: 2586.05 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~4

resc_io: 12778.00 resc_cpu: 401795514

ix_sel: 1 ix_sel_with_filters: 3.1579e-05

Cost: 2616.11 Resp: 2616.11 Degree: 1

Access Path: index (RangeScan)

Index: HRP1001~5

resc_io: 10458.00 resc_cpu: 181255547

ix_sel: 0.076923 ix_sel_with_filters: 0.076923

Cost: 2114.22 Resp: 2114.22 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~6

resc_io: 16184.00 resc_cpu: 421285571

ix_sel: 1 ix_sel_with_filters: 6.0000e-04

Cost: 3299.71 Resp: 3299.71 Degree: 1

Access Path: index (RangeScan)

Index: HRP1001~7

resc_io: 4.00 resc_cpu: 29309

ix_sel: 1.7544e-07 ix_sel_with_filters: 1.7544e-07

Cost: 0.80 Resp: 0.80 Degree: 1

Best:: AccessPath: IndexRange Index: HRP1001~0

Cost: 0.80 Degree: 1 Resp: 0.80 Card: 0.00 Bytes: 0

May be you could find the reason there. If necessary you could fake the statistc records to improve the statement. This has to be done with care because this is probably not the only statement on this table.

Regards

Ralph

[Former Member]

Hi Ralph,

We have run the steps mentioned by you and we got the following output:

PARAMETERS IN OPT_PARAM HINT

****************************

***************************************

Column Usage Monitoring is ON: tracking level = 1

***************************************

****************

QUERY BLOCK TEXT

****************

SELECT /*+ FIRST_ROWS (1) */ * FROM "HRP1001" WHERE "MANDT" = :A0 AND "OTYPE" = :A1 AND "OBJID" = :A2 AND "PLVAR" = :A3 AND "RSIGN" = :A4 AND "RELAT" = :A5 AND "SCLAS" = :A6 AND "ENDDA" >= :A7 AND "BEGDA" <= :A8 AND ROWNUM <= :A9

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=0 objn=137297 hint_alias="HRP1001"@"SEL$1"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 717 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: HRP1001 Alias: HRP1001

#Rows: 1695667 #Blks: 32298 AvgRowLen: 131.00

Index Stats::

Index: HRP1001~0 Col#: 1 2 3 4 5 6 7 8 9 10 11 12

LVLS: 2 #LB: 16708 #DK: 1684054 LB/K: 1.00 DB/K: 1.00 CLUF: 47048.00

Index: HRP1001~1 Col#: 14 4 10

LVLS: 2 #LB: 8038 #DK: 283225 LB/K: 1.00 DB/K: 3.00 CLUF: 939675.00

Index: HRP1001~2 Col#: 14 15 4 10

LVLS: 2 #LB: 8996 #DK: 341085 LB/K: 1.00 DB/K: 3.00 CLUF: 1092436.00

Index: HRP1001~3 Col#: 22 21 15 4 10

LVLS: 2 #LB: 9367 #DK: 367178 LB/K: 1.00 DB/K: 3.00 CLUF: 1461595.00

Index: HRP1001~4 Col#: 14 15 21 4 10

LVLS: 2 #LB: 9673 #DK: 372661 LB/K: 1.00 DB/K: 3.00 CLUF: 1145516.00

Index: HRP1001~5 Col#: 1 24

LVLS: 2 #LB: 4848 #DK: 160625 LB/K: 1.00 DB/K: 1.00 CLUF: 146227.00

Index: HRP1001~6 Col#: 14 15 11 4 10

LVLS: 2 #LB: 11317 #DK: 472447 LB/K: 1.00 DB/K: 3.00 CLUF: 1577892.00

Index: HRP1001~7 Col#: 6 21 2 4 10

LVLS: 2 #LB: 7280 #DK: 1927 LB/K: 3.00 DB/K: 273.00 CLUF: 526441.00

***************************************

SINGLE TABLE ACCESS PATH

Column (#1): MANDT(VARCHAR2)

AvgLen: 4.00 NDV: 7 Nulls: 0 Density: 0.14286

Column (#2): OTYPE(VARCHAR2)

AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619

Column (#3): OBJID(VARCHAR2)

AvgLen: 9.00 NDV: 35290 Nulls: 0 Density: 2.8337e-05

Column (#4): PLVAR(VARCHAR2)

AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 0.5

Column (#5): RSIGN(VARCHAR2)

AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5

Column (#6): RELAT(VARCHAR2)

AvgLen: 4.00 NDV: 26 Nulls: 0 Density: 0.038462

Column (#21): SCLAS(VARCHAR2)

AvgLen: 3.00 NDV: 22 Nulls: 0 Density: 0.045455

Column (#10): ENDDA(VARCHAR2)

AvgLen: 9.00 NDV: 1006 Nulls: 0 Density: 9.9404e-04

Column (#9): BEGDA(VARCHAR2)

AvgLen: 9.00 NDV: 1801 Nulls: 0 Density: 5.5525e-04

Table: HRP1001 Alias: HRP1001

Card: Original: 1695667 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00

Access Path: TableScan

Cost: 8893.95 Resp: 8893.95 Degree: 0

Cost_io: 8749.00 Cost_cpu: 1247411028

Resp_io: 8749.00 Resp_cpu: 1247411028

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

kkofmx: index filter:"HRP1001"."BEGDA"<=:B1 AND ROWNUM<=TO_NUMBER(:B2)

kkofmx: index filter:"HRP1001"."PLVAR"=:B1 AND "HRP1001"."RSIGN"=:B2 AND "HRP1001"."RELAT"=:B3 AND "HRP1001"."SCLAS"=:B4 AND "HRP1001"."ENDDA">=:B5 AND "HRP1001"."BEGDA"<=:B6 AND ROWNUM<=TO_NUMBER(:B7)

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

kkofmx: index filter:"HRP1001"."PLVAR"=:B1 AND "HRP1001"."RSIGN"=:B2 AND "HRP1001"."RELAT"=:B3 AND "HRP1001"."SCLAS"=:B4 AND "HRP1001"."ENDDA">=:B5 AND "HRP1001"."BEGDA"<=:B6 AND ROWNUM<=TO_NUMBER(:B7)

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

kkofmx: index filter:"HRP1001"."PLVAR"=:B1 AND "HRP1001"."RSIGN"=:B2 AND "HRP1001"."RELAT"=:B3 AND "HRP1001"."SCLAS"=:B4 AND "HRP1001"."ENDDA">=:B5 AND "HRP1001"."BEGDA"<=:B6 AND ROWNUM<=TO_NUMBER(:B7)

kkofmx: index filter:"HRP1001"."SCLAS"=:B1 AND "HRP1001"."ENDDA">=:B2 AND "HRP1001"."BEGDA"<=:B3 AND ROWNUM<=TO_NUMBER(:B4)

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

kkofmx: index filter:"HRP1001"."PLVAR"=:B1 AND "HRP1001"."RSIGN"=:B2 AND "HRP1001"."RELAT"=:B3 AND "HRP1001"."SCLAS"=:B4 AND "HRP1001"."ENDDA">=:B5 AND "HRP1001"."BEGDA"<=:B6 AND ROWNUM<=TO_NUMBER(:B7)

kkofmx: index filter:"HRP1001"."SCLAS"=:B1 AND "HRP1001"."ENDDA">=:B2 AND "HRP1001"."BEGDA"<=:B3 AND ROWNUM<=TO_NUMBER(:B4)

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

kkofmx: index filter:"HRP1001"."PLVAR"=:B1 AND "HRP1001"."RSIGN"=:B2 AND "HRP1001"."RELAT"=:B3 AND "HRP1001"."SCLAS"=:B4 AND "HRP1001"."ENDDA">=:B5 AND "HRP1001"."BEGDA"<=:B6 AND ROWNUM<=TO_NUMBER(:B7)

kkofmx: index filter:"HRP1001"."ENDDA">=:B1 AND "HRP1001"."BEGDA"<=:B2 AND ROWNUM<=TO_NUMBER(:B3)

Access Path: index (RangeScan)

Index: HRP1001~0

resc_io: 4.00 resc_cpu: 29296

ix_sel: 1.8535e-09 ix_sel_with_filters: 1.5014e-13

Cost: 4.00 Resp: 4.00 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~1

resc_io: 12269.00 resc_cpu: 431619098

ix_sel: 1 ix_sel_with_filters: 0.0045

Cost: 12333.93 Resp: 12333.93 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~2

resc_io: 13914.00 resc_cpu: 435050244

ix_sel: 1 ix_sel_with_filters: 0.0045

Cost: 13979.33 Resp: 13979.33 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~3

resc_io: 9668.00 resc_cpu: 413521626

ix_sel: 1 ix_sel_with_filters: 2.0455e-04

Cost: 9731.05 Resp: 9731.05 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~4

resc_io: 9910.00 resc_cpu: 409240647

ix_sel: 1 ix_sel_with_filters: 2.0455e-04

Cost: 9972.56 Resp: 9972.56 Degree: 1

Access Path: index (skip-scan)

SS sel: 0.14286 ANDV (#skips): 22946

SS io: 22946.43 vs. index scan io: 693.00

Skip Scan rejected

Access Path: index (RangeScan)

Index: HRP1001~5

resc_io: 21585.00 resc_cpu: 367016399

ix_sel: 0.14286 ix_sel_with_filters: 0.14286

Cost: 21627.65 Resp: 21627.65 Degree: 1

Access Path: index (FullScan)

Index: HRP1001~6

resc_io: 18420.00 resc_cpu: 459570582

ix_sel: 1 ix_sel_with_filters: 0.0045

Cost: 18488.18 Resp: 18488.18 Degree: 1

Access Path: index (RangeScan)

Index: HRP1001~7

resc_io: 4.00 resc_cpu: 29327

ix_sel: 3.7463e-07 ix_sel_with_filters: 3.7463e-07

Cost: 4.00 Resp: 4.00 Degree: 1

• trying bitmap/domain indexes ******

Access Path: index (IndexOnly)

Index: HRP1001~7

resc_io: 3.00 resc_cpu: 21564

ix_sel: 3.7463e-07 ix_sel_with_filters: 3.7463e-07

Cost: 3.00 Resp: 3.00 Degree: 0

SORT resource Sort statistics

Sort width: 993 Area size: 1048576 Max Area size: 173936640

Degree: 1

Blocks to Sort: 1 Row size: 21 Total Rows: 4

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 8605997

Total Temp space used: 0

Access Path: index (IndexOnly)

Index: HRP1001~0

resc_io: 2389.00 resc_cpu: 65129120

ix_sel: 0.14286 ix_sel_with_filters: 0.14286

Cost: 2396.57 Resp: 2396.57 Degree: 0

SORT resource Sort statistics

Sort width: 993 Area size: 1048576 Max Area size: 173936640

Degree: 1

Blocks to Sort: 623 Row size: 21 Total Rows: 242238

Initial runs: 2 Merge passes: 1 IO Cost / pass: 338

Total IO sort cost: 961 Total CPU sort cost: 219154870

Total Temp space used: 7791000

Best:: AccessPath: IndexRange Index: HRP1001~7

Cost: 4.00 Degree: 1 Resp: 4.00 Card: 0.00 Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table

***********************

Join order[1]: HRP1001[HRP1001]#0

***********************

Best so far: Table#: 0 cost: 4.0034 card: 0.0000 bytes: 131

*********************************

Number of join permutations tried: 1

*********************************

Final - All Rows Plan: Best join order: 1

Cost: 4.0034 Degree: 1 Card: 1.0000 Bytes: 131

Resc: 4.0034 Resc_io: 4.0000 Resc_cpu: 29327

Resp: 4.0034 Resp_io: 4.0000 Resc_cpu: 29327

kkoipt: Query block SEL$1 (#0)

• UNPARSED QUERY IS *******

SELECT /*+ NO_STAR_TRANSFORMATION NO_EXPAND FIRST_ROWS (0) */ "HRP1001"."MANDT" "MANDT","HRP1001"."OTYPE" "OTYPE","HRP1001"."OBJID" "OBJID","HRP1001"."PLVAR" "PLVAR","HRP1001"."RSIGN" "RSIGN","HRP1001"."RELAT" "RELAT","HRP1001"."ISTAT" "ISTAT","HRP1001"."PRIOX" "PRIOX","HRP1001"."BEGDA" "BEGDA","HRP1001"."ENDDA" "ENDDA","HRP1001"."VARYF" "VARYF","HRP1001"."SEQNR" "SEQNR","HRP1001"."INFTY" "INFTY","HRP1001"."OTJID" "OTJID","HRP1001"."SUBTY" "SUBTY","HRP1001"."AEDTM" "AEDTM","HRP1001"."UNAME" "UNAME","HRP1001

kkoqbc-end

: call(in-use=111688, alloc=114552), compile(in-use=46888, alloc=48584)

First K Rows: Setup end

***********************

kkoqbc-start

: call(in-use=109488, alloc=114552), compile(in-use=47088, alloc=48584)

****************

QUERY BLOCK TEXT

****************

SELECT /*+ FIRST_ROWS (1) */ * FROM "HRP1001" WHERE "MANDT" = :A0 AND "OTYPE" = :A1 AND "OBJID" = :A2 AND "PLVAR" = :A3 AND "RSIGN" = :A4 AND "RELAT" = :A5 AND "SCLAS" = :A6 AND "ENDDA" >= :A7 AND "BEGDA" <= :A8 AND ROWNUM <= :A9

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=0 objn=137297 hint_alias="HRP1001"@"SEL$1"

*****************************

Please advise how we can optimise the program with this output.

[Former Member]

Hi Prasad,

after all I found an inconsistency in my understanding of your code. In your ABAP statement:

select single * from hrp1001 where

otype = 'S' and

objid = w_plans and

plvar = '01' and

rsign = 'A' and

relat = '002' and

sclas = 'S' and

endda ge begda and

begda le endda.

I missed that begda and endda are filled by the module. Is this your intention? I thought that you mean this statement?:

select single *

into wa

from hrp1001 as h

where h~otype = 'S'

and h~objid = w_plans

and h~plvar = '01'

and h~rsign = 'A'

and h~relat = '002'

and h~sclas = 'S'

and h<sub>endda ge h</sub>begda

and h<sub>begda le h</sub>endda.

Is this standard SAP coding or customer code. I thought originally that you're using a SAP module, but now I think different.

The difference is that your original code uses a table with header line and the SQL statement uses the content of the header line. This is very dangerous because I'm not sure if the result is what you want in any case. If my statement is what you originally want, then this probably will solve your problem too.

Regards

Ralph

[former_member194613]

Hi,

you are going down into details very fast.

This does not really look as if there is a problem.

> SELECT STATEMENT ( Estimated Costs = 4 , Estimated #Rows = 1 )

And I am missing the information of the Summary by SQL Statements:

What are

executions:

duration:

records:

min time per record:.

Please better run it several times and check whether these number are always the same.

Siegfried

[Former Member]

Hi Siegfried,

The program runs for an awfully long time (6-7 hrs). Hence getting an average is not possible. However, here's a sample output..

Execution:408

Duration:106,916,911

Records:38

Min Time per Record:443

One point to note is that in the sql trace, I notice its the 'FETCH' operation taking a long time and not the 'REOPEN'.

Regards,

Prasad.

[former_member194613]

Execution:408

Duration:106,916,911

Records:38

Min Time per Record:443

o.k. 100sec is slow!

But minimal time per record of 443 is o.k., so there are fast executions

38 records with that speed would need 16ms, no problem, 400 executions maybe 160ms not a real problem.

Are there identical executions?

The REOPEN is only the opening of the database connection that is always fast, it is the FETCH.

Check the extended list, I guess that there are executions where no record is found and I would guess that these are the slower ones.

Check the variables for the no records found accesses (see details in extended list), sometimes the variables are filled with values which just not exist,

sometimes there is initial values which are of course not there and which cause slow accesses.

Siegfried

[former_member181927]

The simple solution this problem is to delete INDEX~7 from table HRP1001. Even if we put all primary key fields like OTYPE, OBJID, PLVAR, ISTAT, RSIGN, RELAT ENDDA, BEDGA, ETC.

The Optimizer selects INDEX~7 because INDEX~7 matches all fields from the primary key. The major issue with INDEX~7 is that it is BAD because the fields from this index don't form a key to find a unique record in the table. So, this make the select very hard to retrieve data when you are running a big report.

So, delete this INDEX from table and the Optimizer automatically uses Primary Index and the performance will get much better.

ALL THE BEST AND ENJOY SAP!!