Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share

migration rejected from serverfault.com May 22 '13 at 20:15

This question came from our site for professional system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as not a real question by Martin Smith, Aaron Bertrand, Mark Storey-Smith, RolandoMySQLDBA, Marian May 22 '13 at 20:15

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Query plan please. Table structures would also help. And an idea about the data amounts you talk about. You know, the professional information that people need to give well thought out advice. 12 seconds may be related to you trying to process a lot of data on crappy hardware and be totally acceptable from a performance point of view. And have you considered upgrading the 2 generations behind sql server you have? –  TomTom May 22 '13 at 8:29
1  
Don't know why the question was migrated until the requested info was supplied. –  Martin Smith May 22 '13 at 13:11