We have a OLTP system and we have got a grid, which is containing close to 20 columns, coming from multiple tables. The grid loaded based on search parameters involving around 6 columns. The data is huge with 100M rows coming from background tables.
To improve the performance of the grid loading, we have created indexed view with single unique clustered index. We are currently want to see how we can improve the performance of search parameters: a,b,c,d,e,f
The search can be based on any of the combination: (a), (a,c),(d,e), (a,b,c) ... (a,b,c,d,e,f)
We are thinking of either going for one of the below options:
- Multiple composite indexes on indexed view with specific access patterns like (a,b), (b,d), (a,b,c) etc.
- Single non-clustered columnstore index on indexed view, which will be helpful to satisfy all different access patterns like (a,b), (b,d), (a,b,c) etc. with included column of 20 columns
Can you please suggest, which is better approach ?
UPDATE: Just read that, non-clustered column store index does not support include columns. Will try further and update the answer, either by comments, if question is closed or by answering it.
question from:
https://stackoverflow.com/questions/65913023/multiple-composite-index-vs-single-non-clustered-column-store-index 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…