Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How will you optimize this Query (having many SELF (LEFT) Joins) ? RE: How will you optimize this Query (having many SELF (LEFT) Joins) ?

  • April 2, 2013 at 4:31 am

    #1602736

    theashishtrivedi (4/2/2013)

    Thanks for the reply Chris,

    I have already posted some sample data in my earlier post. Due to some legal restrictions, I can't post more data.

    Can you please read the article I mentioned? It will show you how to set up your sample data as a set of INSERTs, so folks don't have to spend half an hour or more prepping data before they can begin to investigate the problem.

    Many of us deal with sensitive data every day so I understand your comment, however to fully test any solution, we will need sample data which touches every part of the query. If necessary you could mask real values with dummy values. Numeric keys are fine, the 'name' values might require masking.

    Did the query I posted return the expected result set? I'm not concerned yet with performance, I'm toying with it to construct something which is easier to work with.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden