I am facing performance issue for a Dimension table having multiple relation to other table. For one of my dimension it took almost 8 min to complete that is having no records that was inserted or updated in the previous DWH run. Sometimes a stat update helps other times I tried rebuild the index of some of the tables used in that query and it ran in seconds. I also altered the sub query to use that base table only once and that also helped. Could some one help me this? Is there something that can be done in compose that would improve the performance?
I'm using Compose Version 2021.5.0.69 and Sql Server 2017 CU22.
