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

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-05-11
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
order by h.HierarchyID,p1.Name,p2.SubCategoryName,p3.SubCategoryName,p4.SubCategoryName,p5.SubCategoryName
Question by:Hojoformo
LVL 23

Expert Comment

ID: 35734850
I believe that having the following columns indexed will improve performance on the SQL in question:


Expert Comment

ID: 35734867
Yes you need to make primary and foreign key relationship. As primary key is one kind of index. It will improve your  query performance.
LVL 57

Accepted Solution

Raja Jegan R earned 2000 total points
ID: 35736324
>> 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)
2. p1.HierarchyID
3. p1.ID
4. p2.ParentID
5. p3.ParentID
6. p2.SubcategoryID
7. p4.ParentID
8. p3.SubcategoryID
9. p5.SubCategoryID

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question