Extendend Notifications: Performance on SWWLOGHIST

[Florin Wach]

Hi dear wuggers,

I'm currently running the intial (first) run of SWN_SELSEN in the production system. There's a filter active, restricting the notifications down to a handful of tasks-ID's. There are relatively few users using the notifications (as receivers).

The run takes now 3 hours, where I can trace it down to a large and time-consuming number of sequential reads on table SWWLOGHIST, which I could track down to ABAP class CL_SWF_RUN_GET_WI_DLT_REQUEST.

Probably it's the method GET_DELTA_ENTRIES that ... when you run it for the very first time, obviously gets a huge number of work items... so to say.

Here is a statements that is sticking out:

SELECT hist~wi_id hist~method hist~timestamp
             FROM swwloghist AS hist
             INNER JOIN swwwihead AS head ON hist~client = head~client 
               AND hist~wi_id = head~wi_id
             INTO CORRESPONDING FIELDS OF TABLE lt_swwloghist
             FOR ALL ENTRIES IN lr_method
             WHERE hist~timestamp GE me->im_timestamp
             AND   hist~method EQ lr_method-low
             AND   head~wi_type EQ swfco_wi_normal
             AND   head~wi_rh_task IN lr_task.

There's a secondary database index existing on SWWLOGHIST~002 for
[ CLIENT , TIMESTAMP , METHOD ]

The join seems to be okay, since they match on the primary key of WI_ID. Mayhaps the wi_id needs to be included in the database index, making it the database easier to access the joined table w/o reading the record set, yet at that stage.
Furthermore, the swwwihead-table needs to be read fully for each entry found at the swwloghist table.
Hmm... Since the statement FOR ALL ENTRIES was used, a combined where clause is generated, grouping about 5 to 10 select-clauses into one statement. To the number of SELECT's is the number work items in the system (since they're all within the requested me->im_timestamp (which is zeroe at that time)).

** Well, did anybody else came across that performance issue?
** Maybe someone knows the trick to manually set a timestamp, so I can reduce the number of selects for the initial load?

We have 3,559,095 work items currently in the system.

Any ideas, even the most absurd one, are highly appreciated!

With the very best wishes,
Florin