We have BPC 10.1 for NetWeaver on SQL Server 2017. We are experiencing poor performance with some BPC processes, and I noticed that none of the E fact tables have a clustered index. Every other table does, including the corresponding F fact tables. There is, however, a non-clustered unique index.
Because of this, the table is also not getting rebuilt with regular index maintenance jobs, and is heavily fragmented. That can be remedied separately though.
The E table is only ever updated via a SQL Merge statement. Although a clustered index will slow down an insert, it will generally speed up a select and update. I have not be able to find anything about this online, but it seems like it would help more than hurt.
One reason I found for implementing a primary key via a non-clustered index (as is done here) is a composite primary key (as would be required here) would greatly increase the storage space of all the other indexes. I need to see which of the other indexes are even used. The current unique index is ordered in a way that is useless from a business perspective; I need to see if it's even ideal from the Merge perspective.
Anyway, I guess this is a lot more complicated than I initially thought, so any input would be appreciated.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.