topic Re: Primary Index issue on table ECC/SER01 in Application Development and Automation Discussions

Primary Index issue on table ECC/SER01

former_member587905 — Tue, 17 Jul 2018 07:36:29 GMT

Hi,

I am working on ecc program performance improvement, when I execute my program found that the issue with the SER01 table. using ST05, SAT to measure the changes.

It seems like the Primary index of table SER01 is not working - In SELECT statement WHERE clause used the primary key + non-index field(SERNR+VBTYP) it takes 100 seconds. but when I use same query with only primary key(SERNR) it is taking 140 seconds. At runtime, I have seen the data in both cases output is same.

ST05- I can see INDEX UNIQUE SCAN SER01~0 is used.

Question:

1. In st05 detailed information of index it shows Last statistics date is 11/11/2015 is this something wrong and index table is not updated till date..?

2. In se16, total number of records 23,628,275 but in st05 shows 23,598,400. Do both numbers should match approximately..? now the diff is around 30K.

Thanks,

Vishwa

Re: Primary Index issue on table ECC/SER01

RaymondGiuseppi — Tue, 17 Jul 2018 08:04:40 GMT

1. No statistics since 2015 is bad -> Ask basis to run those on a periodic base

2. Did you check for client MANDT in the primary key, implicit use by SELECT statement

Re: Primary Index issue on table ECC/SER01

former_member587905 — Tue, 17 Jul 2018 15:14:13 GMT

I tried with MANDT it did not help.

Re: Primary Index issue on table ECC/SER01

RaymondGiuseppi — Wed, 18 Jul 2018 06:39:46 GMT

First step is actualisation of statistics...

Re: Primary Index issue on table ECC/SER01

former_member587905 — Wed, 18 Jul 2018 17:12:41 GMT

Hi Raymond,

My basis team has run statistics I have attached the latest screenshot. now in the st05 primary key entries are more than the total number of table entries.

but after the index update also the performance is same, am passing only primary key field which is taking more time than when I pass the primary key + non-indexed field.

Thanks,

Vishwa

2ser010.jpg

3ser010.jpg

Re: Primary Index issue on table ECC/SER01

RaymondGiuseppi — Thu, 19 Jul 2018 06:31:14 GMT

Don't forget that the execution time is time to find records + time to transfer data from database server to application server. If you remove the non key criteria, more data will be copied.

What is your database, and which version?

(The statistics are statistics, the index itself is updated in nearly real time, only the stats were refreshed, and ony a sample of data were actually analyzed)