Runtime of "Start Data Selection for Header Tables" for X_JEST

[Former Member]

Hello Everyone,

I was just curious about how long this sub activity is running for other companies doing TDMS refreshes? For us it is taking roughly 30 hours to complete this one step. Our sender system has roughly 81 million rows in JEST and 24 million rows in JSTO and we feel that this step is taking way too long to complete. Our sender system is a fairly powerful system with 20 logical processors and 32GB of ram. The entire database is only 432 GB in size.

The max_blocking_factor parameter for this specific job was manually set to 50 by SAP (the default is 5) so the job is taking the following select statement:

SELECT *

FROM JEST

INTO TABLE LT_S_JEST

FOR ALL ENTRIES IN LT_S_CNVTDMS_05_JEST

WHERE OBJNR = LT_S_CNVTDMS_05_JEST-OBJNR

%_HINTS ORACLE '&max_in_blocking_factor 50&'

and converting it into a few million select * statements with a where clause of 50 OBJNR records at a time. This loop is what is causing the job to run for many hours. In looking at the data that this job is gathering, wouldn't a join of CNVTDMS_05_JEST and JEST be faster in this case? For a test, we ran the following join against the sender system to see the runtime and it only took 11 minutes. We understand that there would be additional overhead in regard to actually inserting data vs just a count(*) but it seems that this would be a faster process.

SYS @ EP2 > sho timing

timing ON

SYS @ EP2 > select count(*) cnt

from sapep2.JEST a, sapep2.CNVTDMS_05_JEST b

where a.mandt = b.mandt and

a.objnr = b.objnr

CNT

-

---

61059208

Elapsed: 00:11:08.03

Anyway, I was hoping we could get a listing of other sites runtimes, record counts and database sizes to see if this is an issue for all companies or just our specific scenario.

Thanks

-Stephen

[former_member182566]

Hi Stephen. I would say your times are not normal. What SP are you on? Have you search for OSS notes?

My data:

DB 900 Gb

JEST 20 million records

JSTO 10 million records

it will also depend, of course, on how much of the sender system you are copying; in my case my receiver system ended up with:

JEST 10 million records

JSTO 7 million records

the times were:

Access plan for X_JEST is being calculated (ID 00001, 16.777.216 blocks)

Loading of cluster data in sender system started on 07.08.2009 at 22:27:05

Load of cluster data in the sender system completed on 07.08.2009 at 23:07:53

Access plan for X_JEST_REST is being calculated (ID 00001, 16.777.216 blocks)

Loading of cluster data in sender system started on 08.08.2009 at 01:36:01

Load of cluster data in the sender system completed on 08.08.2009 at 02:41:32

[Former Member]

Hi Rui,

Thanks for the reply. We are currently on SP10 but I am installing SP11 and SP12 as I write this. I had put in an SAP note and they had said there were performance updates in SP11 and SP12 so our current plan is to apply the latest support packs we can for TDMS and run another refresh to see if the timing improves.

We chose a start date of 9/1/2009 and the TDMS package started 10/27/2009 so we had roughly 2 months of data to transfer. We ended up with about 61 million rows in JEST and almost 20 million in JSTO for the receiver client.

Our runtime for X_JEST_REST ran very fast: (4 minutes)

Access plan for X_JEST_REST is being calculated (ID 00005, 16,777,216 blocks)

Loading of cluster data in sender system started on 10/28/2009 at 19:04:00

Load of cluster data in the sender system completed on 10/28/2009 at 19:08:52

The runtime for X_JEST ran very slow: (almost 29 hours)

Access plan for X_JEST is being calculated (ID 00005, 16.777.216 blocks)

Loading of cluster data in sender system started on 03.11.2009 at 23:41:19

Load of cluster data in the sender system completed on 05.11.2009 at 04:26:57

Thanks again for looking and providing your data. I am curious to see what other companies are seeing for runtimes as well.

Thanks

-Stephen