ODS BOM Extract index question

[Former Member]

Hi,

In our production environment in Sweden, running SAP ME 6.0.4.12, we are doing an hourly ODS transfer.

During this transfer we call amongst other things also the ODSGenealogyETL.bat file.

Since we are in the middle of starting up usage of SAP ME, there can at times be many BOM's
that we add to the WIP server that also needs to be transferred to ODS for reporting. I have noticed,
on those times when many BOM's have been added, that ODS BOM Extract takes several minutes.
The worst I have seen was approximately 20 minutes run-time just form ODS BOM Extract phase.

During this ODS Bom extract we also notice slower response times in SAP ME.

I have checked the highest queries based on CPU usage in the WIP Database (SQL Server 2008 r2, 64-bit).

Almost all of the top queries comes from ODS Bom extract queries with Where clauses on "Modified-date-time" and
"Partition-date-time" from the BOM table.

I have noticed that I have no indexes on "Modified-date-time" and "Partition-date-time". Is there a best-practice
to add additional indexes to WIP BOM Table when experiencing problems?

Best regards,

Johan Nordebrink

[Former Member]

Johan

For ODS, definitely consider adding new indexes to this, and other tables read by the ODS jobs.

However in the case of the BOM table, it may not help. If I recall, the single query references both modified_date_time and partition_date columns with an OR statement - is that correct?

Because the modified_date_time column can be null (according to the table definition DDL as opposed to the actual data content, which never seems to be null) then an index added on this column may not get used due to the null possibility. So, the table may get a full tablescan regardless. So test this before applying it to the production server.

[Former Member]

Hi Stuart,

Yes, it is an OR statement.

I will definitely do it in test environment first. I did a query plan in SQL server and it suggested to add an index on these 2 columns for better performance.

Best regards,

Johan

[sergiy_katerinich]

There was a similar situation with regards to ODS Production Log - see SAP Note 1633673. So, you might check performance improvement after adding the index on PARTITION_DATE versus MODIFIED_DATE_TIME plus PARTITION_DATE.