KB438609: The 'Insert into IS_REP_FILT' query is very slow on a MySQL database during the Enterprise Manager 10.x data load process

SYMPTOM

When running a data load in Strategy Enterprise Manager 10.x on a MySQL database, the data load process may take a very long time and appear to be hanging. When reviewing the MigrationSQL log, it can be seen that the data load is taking a long time at the 'INSERT INTO IS_REP_FILT' statement. During the data load 'hang', the last statement in the log will be as follows:

SELECT DISTINCT * FROM STG_IS_REP_FILT ORDER BY IS_REP_GUID

The data load may appear to be hanging at this time, but in fact, it is not a hang. On the MySQL database end, a database administrator should see that the IS_REP_FILT statement is continuing to run on the database but is taking a very long time to complete.
After the data load is completed, the MigrationSQL log will show a large time gap between the following statements:

SELECT DISTINCT * FROM STG_IS_REP_FILT

INSERT INTO IS_REP_FILT ( IS_REP_ID, IS_FILT_ID,  IS_PROJ_ID) select   L1.IS_REP_ID,  L2.IS_FILT_ID,  P0.IS_PROJ_ID FROM  IS_REP L1, IS_FILT L2,  STG_IS_REP_FILT R0, EM_MD M0, IS_PROJ P0 WHERE R0.IS_REP_GUID =  L1.IS_REP_GUID AND  L1.IS_PROJ_ID = P0.IS_PROJ_ID AND R0.IS_FILT_GUID =  L2.IS_FILT_GUID AND  L2.IS_PROJ_ID = P0.IS_PROJ_ID AND  R0.IS_REPOSITORY_GUID = M0.REP_GUID AND R0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND P0.IS_REPOSITORY_ID = M0.REP_ID AND NOT EXISTS (SELECT 1 FROM  IS_REP_FILT W0 WHERE W0.IS_REP_ID =  L1.IS_REP_ID AND W0.IS_FILT_ID =  L2.IS_FILT_ID AND  W0.IS_PROJ_ID = P0.IS_PROJ_ID)

CAUSE
This performance issue has been identified as a defect in Enterprise Manager 10.x.
ACTION
This issue is currently being investigated by Strategy Development team. Contact Strategy Technical Support for an update on this issue. 
WORKAROUND
It is possible to create a composite index on three of the columns in the IS_REP_FILT table which can increase the performance of the data load. This change can be made by running the following query on the Enterprise Manager MySQL database. 
 

alter table IS_REP_FILT
add index em_load (IS_REP_ID, IS_FILT_ID,IS_PROJ_ID);

Warning
It is strongly suggested to do a full backup of the Statistics and Enterprise Manager database before modifying the database structure or altering data by running data loads. 
  KB438609