• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Suggestions to improve performance on SQL Server 2005 Select Query with table joins.

I was hoping someone can offer some suggestions on how I can improve the performance on the query below.  I use this query in a C# windows form application.  This query is used to build a hierarchy tree like this

- Root 1 (Hierarchies table)
   -  Level 1 (PIES Level 1 table)
        -  Level 2 (PIES Level 2 table)
            -  Level 3 (PIES Level 3 table)
               -  Level 4 (PIES Level 4 table)
                   -  Level 5(PIES Level 2 table)
- Root 2
   - ......................
This query joins together 6 SQL server tables Hierarchies and PIESLevel1 - 5.  None of these tables has a primary key.  

So my first question is would a primary key help with performance?
Is there a better way to join these tables together to make it less expensive?  

Here is the query:
select  h.HierarchyID, h.Name, h.LinkColumn, h.LinkColumnTable, h.DateUpdated, p1.HierarchyID AS Expr1, p1.ID, p1.Name AS Expr2, p1.Type, p1.LastUpdate, p1.RevisionDate, p2.HierarchyID AS Expr3, p2.ParentID, p2.SubCategoryID, p2.SubCategoryName, p2.LastUpdate AS Expr4, p2.Type AS Expr5
            from Hierarchies as h
               join PIESLevel1 p1 on h.HIERARCHYID=p1.HierarchyID
               join PIESLevel2 p2 on p1.ID=p2.ParentID
               join PIESLevel3 p3 on p3.ParentID=p2.SubcategoryID
               left join PIESLevel4 p4 on p4.ParentID=p3.SubcategoryID
               left join PIESLevel5 p5 on p4.ParentID=p5.SubCategoryID
           
              Group by
h.HierarchyID,h.Name,p1.Name,p2.SubCategoryName,p3.SubCategoryName,p4.SubCategoryName,p5.SubCategoryName,p1.ID,p2.SubcategoryID,p3.SubcategoryID,p4.SubcategoryID,p5.SubcategoryID
order by h.HierarchyID,p1.Name,p2.SubCategoryName,p3.SubCategoryName,p4.SubCategoryName,p5.SubCategoryName
0
Hojoformo
Asked:
Hojoformo
1 Solution
 
wdosanjosCommented:
I believe that having the following columns indexed will improve performance on the SQL in question:

PIESLevel1.HierarchyID
PIESLevel2.ParentID
PIESLevel3.ParentID
PIESLevel4.ParentID
PIESLevel5.ParentID
0
 
anillucky31Commented:
Yes you need to make primary and foreign key relationship. As primary key is one kind of index. It will improve your  query performance.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> So my first question is would a primary key help with performance?

Yes, Creation of Primary keys in all those tables will create a Clustered index by default which helps in major performance increase of queries.

>> Is there a better way to join these tables together to make it less expensive?  

Yes, creation of nonclustered indexes on the following columns involved in joins. ( If you are not able to create Primary keys)
1. h.HIERARCHYID
2. p1.HierarchyID
3. p1.ID
4. p2.ParentID
5. p3.ParentID
6. p2.SubcategoryID
7. p4.ParentID
8. p3.SubcategoryID
9. p5.SubCategoryID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now