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

Posted on 2011-05-10
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

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

    LVL 9

    Expert Comment

    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

    >> 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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now