Performance problem in accessing custom table

[Former Member]

Hi,

I am accessing a Z table with around 50 fields including two primary key fields Document and line item number. Number of entries are 400000. I have the following select in program.

SELECT *

FROM YORDER

INTO TABLE it_customer

FOR ALL ENTRIES IN it_cust_head

WHERE document = it_cust_head-doc_num.

In ST05, the above trace took a lot of time and it appears in red. Also the index picked up by the program appears to be the one created and not the default one since the key document passed is a key field.

The execution plan of the statment is as follows :-

SELECT STATEMENT ( Estimated Costs = 83 , Estimated #Rows = 65,836 )

TABLE ACCESS BY INDEX ROWID YORDER

INDEX RANGE SCAN YORDER~Z01 .

There are two issues here:-

1) Performance is very slow.

2) Wrong index is picked up when one of the primary key fields is the basis on which the selection is done.

Anyone has nay ideas on performance improvement here ?

Thanks...

[Former Member]

Hi RK,

1. Specify the required field names instead of * in the select query.

2. Check it_cust_head[] is not inital before using for all entries.

3. if still there is a problem, create a secondary index.

<b>Reward for helpful answers.</b>

-Satish

[former_member194669]

Hi,

Have you using

if not it_cust_head[] is initial.

before calling select all entries ?

You can force the sql statement to use a particular index by using %_HINTS parameter.

aRs

[Former Member]

Please provide the fields of the pprimary key and the fields of the index Z01,

which one contains field 'document', at which position?

[Former Member]

Hi,

1) I need all the fields. Select * is essential.

2) it_cust_head[] is being checked for contents before the for all entries.

3) Primary index should have fired since field docuument is part of primary keys but the secondary index is picked up automatically even though I am not mentioning HINTS anywhere.

Helllllp....

Rgds.

[RaymondGiuseppi]

If you don't have the FULL primary key, SQL won't choose this index automatically, Try to put the full key, even if you have to use a :

WHERE FIELD IN SO-FIELD where SO-FIELD is an empty range, or a dummy range (like 000000-999999)

Better if you can fill the range with the true values (maybe Society, Status, Year?)

If the first keys have too many values, the SQL optimization will not give any miracle, maybe if you transaction is often called, could you build an index, or build a new table with your data available, filled by a periodic job ?

Regards

[RaymondGiuseppi]

Hi,

(1) first of all : Always check if table is empty before any SELECT FOR ALL ENTRIES.

(2) Only read data wanted: Please remove that *

(3) If the index you want is not the primary one, check under SE11 if it is actually active in the database.

(4) If nothing works try to give hints to SQL (Look at OSS <a href="https://service.sap.com/sap/support/notes/129385">Note 129385 - Database hints in Open SQL</a>)

(5) Upgrade you hardware?

Regards

[Former Member]

so that case ...

"Replace your  select statment with  my statements  ... 

DATA  YORDER_tab TYPE TABLE OF YORDER. 
FIELD-SYMBOLS <YORDER> TYPE YORDER. 

SELECT *FROM YORDER INTO correspnding fields  of table   TABLE    YORDER_itab .

loop at  it_cust_head.

read table   YORDER_itab with    key  document =  it_cust_head-doc_num  ASSIGNING  <YORDER> .
if  sy-subr = 0 .
move   <YORDER>  to   it_customer .
endif.
Append  it_customer .
endloop .

"now see  what  was the  performance  of  your program .

Reward points if it is usefull ..

Girish

[Former Member]

> Primary index should have fired since field docuument is part of primary keys b

the question was not whether it is a part of the primary key, but whether the primary key starts with the document field. I guess it does not.Then the primary key will not be used.

> WHERE FIELD IN SO-FIELD where SO-FIELD is an empty

> range, or a dummy range (like 000000-999999)

this is not recommended and actually quite useless.

> SELECT *FROM YORDER INTO correspnding fields of

> table TABLE YORDER_itab .

this is also not recommended! I don't know your data distrubution, but you have a where condition, so you read only a fraction of the full table. Here you would read the full table.

=> please check whether you know other conditions, and add them to the where condition

=> if not, then check selectivity of field 'document' how many different values of document appear in the table?

=> if less thant 10 different values, then nothing will help you, if more than 10 (the more the better) then you should create a new index, with field 'client document'

Siegfried

[Former Member]

Two ideas:

1) replace your query with a join, something like:

SELECT * FROM YORDER inner join it_cust_head on YORDER~document = it_cust_head~doc_num
  INTO corresponding fields of TABLE it_customer.

2) better yet, use a range (doesn' t work if you need too many entries for the range, 512 on my system):

ranges: rord for YORDER-document.
free rord.
rord = 'IEQ'.
select document from it_cust_head into rord-low.
  append rord.
endselect.

SELECT * FROM YORDER
INTO TABLE it_customer
WHERE document in rord.

[Former Member]

Thanks to all for the solutions.. Including the primary key as well helped a lot..