The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index but i was getting the same time. How can I avoid this issue?.
SELECT T.Name AS name, T.Id AS id, COUNT(DISTINCT( DD.dynamictableid )) AS counts, ROUND(SUM(D.[employees]), 0) AS measure1 FROM dbo.TreeHierarchy T LEFT JOIN dbo.DynamicDataTableId DD ON T.Id = DD.HierarchyId AND T.DataViewId = DD.DataViewId LEFT JOIN dbo.Demo1 D ON D.[Demo1Id] = DD.DynamicTableId WHERE T.DataViewId = 2 AND T.ParentId = 0 GROUP BY T.Id, T.Name