Performance issue in select query for table CE1ZCSC

[Former Member]

Hi

How to resolve performance issue in following select query-

SELECT *

INTO CORRESPONDING FIELDS OF TABLE it_final

FROM ce1zcsc

WHERE paledger EQ c_10 "Currency Type

AND vrgar IN s_vrgar "Record Type

AND versi EQ space "Plan Version

AND perio IN r_perio "Period

AND bukrs IN s_bukrs. "Company Code

TABLE CE1ZCSC has around 173 fields,but it_final has around 105 fields.

The indexes are created for the following fields:

paledger

vrgar

versi

perio

bukrs and

prctr.

I doubt whether we should look for Estim. CPU-Costs in index range scan or table access by index rowid in Execution plan for SQL statement.

If anybody can provide me with informative documents on performance issue.

[Former Member]

Hi

How to resolve performance issue in following select query-

SELECT *

INTO CORRESPONDING FIELDS OF TABLE it_final

FROM ce1zcsc

WHERE paledger EQ c_10 "Currency Type

AND vrgar IN s_vrgar "Record Type

AND versi EQ space "Plan Version

AND perio IN r_perio "Period

AND bukrs IN s_bukrs. "Company Code

TABLE CE1ZCSC has around 173 fields,but it_final has around 105 fields.

The indexes are created for the following fields:

paledger

vrgar

versi

perio

bukrs and

prctr.

I doubt whether we should look for Estim. CPU-Costs in index range scan or table access by index rowid in Execution plan for SQL statement.

If anybody can provide me with informative documents on performance issue.

[Former Member]

where ist your performance issue ????

Check SQL trace and goto Trace List' 'summarize Trace by SQL Statement'

Look for your statement, and the duration, how many microseconds does it need?

How many records come back? What are the mimimal and average costs per record.

EXECUTE it several time before you run the trace.

For details

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Siegfried

[Former Member]

Hi

Thanks for your reply.The details of SQL trace are as follows:

Duration- 21,462,875

Records- 53

Time/Execution- 21,462,875

Record/Execution-53

Avg Time-404,960

min time/rec-404,960

Length-1338

And the Execution plan for the sql statement is -

SELECT STATEMENT ( Estimated Costs = 1,212 , Estimated #Rows = 2,589 )

3 FILTER

2 TABLE ACCESS BY INDEX ROWID CE1ZCSC

( Estim. Costs = 1,211 , Estim. #Rows = 2,589 )

Estim. CPU-Costs = 46,043,418 Estim. IO-Costs = 1,207

1 INDEX RANGE SCAN CE1ZCSC~Z06

( Estim. Costs = 643 , Estim. #Rows = 12,944 )

Search Columns: 5

Estim. CPU-Costs = 32,831,533 Estim. IO-Costs = 639

[Former Member]

I think Move corresponding takes a bit more time .. as it needs to

compare each and every field and insert data ..

Instead declare it_final like table CE1ZCSC and use into table ..

Also check if the fields in where condition are in the same order

as in table CE1ZCSC.

[Former Member]

Hi,

if the index in the execution plan is of different color then you can double-click on it and you will get index details

What is the date of the statistics?

What are the different values of the index fields?

You can also double click on the table and get table information, what is the total number of rows of the table?

And please run the test 3-times and check whether the behavior is similar.

In principle your index is o.k., but your trace result is slow.

Siegfried