why is the swich procedure so slow ?

[Former Member]

Hi all,

we run an XI 3.0 sp 21. we do high volume (we think..)

from sxmb_moni processing statistics :

- XML Messages/Day 9.704

- Minimum 1

- Maximum 295.940

- Today 172.008

retention is 3 days.

the switch procedure takes from 01:00 to 17:00 for the copy of about 750000 entries.

this job runs each day.

Questions :

is this duration normal ?

do we need some index or suchlike ?

if you run a comparable load, what are your runtimes ?

I would very much like my switch procedure to finish before start of business...

Regards,

Ronald van Aalst

Edited by: TNTPost SAP CCC on Nov 19, 2008 3:43 PM

[former_member334189]

Dear Ronald,

let me start with some words on the idea behind switch procedure: assuming a constant flow of messages in an high-volume scenario means to continuously delete as many messages as new messages are received. Now deletion is almost as expensive as insertion which made us think about different approches to achive the same aim (steady state) with lower effort. The idea is to not delete messages physically by SQL delete statements, but to mark them as 'logically' deleted. Then the message which are still in use are copied to a shadow set of tables whereas the logically deleted messages remain in the original set of tables. After that the original set is destroyed and re-created immediately. This way physical deletion of a huge amount of messages is done at a single blow.

Consequence: The switch procedure is beneficial if only few messages have to be copied whereas many messages are deleted logically.

Regarding your questions:

Q1,Q3: Pretty hard to answer. This is because this is dependent on various factors, such as

+ average message size

+ number of versions (number of messages x number of versions x average message size = datavolume to be copied)

+ hardware

+ load situation at the time the switch takes place

+...

Q2: No, definitely no additional index. However, dependent on the database system in use there are performance related notes available in OSS regarding the table switch procedure. For example there is a recommendation for some releases of Oracle DB to exclude almost all tables belonging to XI persistency layer from statistic updates. Instead of that there are preset statistics to be used.

Regarding your quantity structure: you stated that 750.000 messages are copied and that the retention period (for asynchronous messages I suppose) is set to 3 days. I.e. the throughput is ~250K per day. Thus, there are ~1mio messages in XI persistency layer right before the switch report starts. Most likely the threshold (XI config parameter DELETION-DROP_MAX_TABLE_LOAD) is exceeded each time the switch report starts, because the number of expected messages (see online documentation on that parameter) evaluates to 200.000 or 850.000 (depending on platform AND support package level). Consequently the report decides to actually perform the switch to the shadow data container. This way 250.000 messages are deleted whereas 750.000 are copied. This contradicts the Consequence that I have outlined before.

Options:

1) Reduce the retention period, but of course this is highly dependent on business requirements!

2) Turn off switch procedure

3) Adjust threshold such that the switch is done every 5-7 days. However, this will not change the execution time once the switch to a shadow set of tables has started, because the number of messages to be copied (750.000) stays the same.

Best Regards,

Harald Keimer

XI Development Support

SAP AG, Walldorf

Edited by: Harald Keimer on Nov 19, 2008 11:17 PM

[SudhirT]

Hi,

i hv also gone through this activity. and as per my exp. it is the normal speed of table switch. actually during this process, it works on many tables simultaneously like SXMSPMAST,SXMSCLUP,SXMSCLUR and the secondary tables for these tables with same name with suffix 2 eg SXMSPMART2 etc.

the whole process is as below

1. copy all the primary containers to secondary.

2.delete the unused space and reorganize the database.

in this way unused space for the archived and deleted messages is releasf.

Hope it helps.

Thanks.