CODEWITHSUNDEEP
sql-serverjoin
DoIt
DoIt

Reputation: 3428

Optimizing a nested left join query

I am working on a SQL query which contains several joins in it which is as follows.

   SELECT DISTINCT GL2.Number1, GL.DEPARTMENT,GL.CLASS,GL.SUBCLASS,GL.LINE, PR.*
    FROM DETAIL A

    LEFT JOIN CATEGORIES CAT ON   A.WEB_ID=CAT.CATEGORY_ID  
    LEFT JOIN GROUP_LINES GL ON 
                                            A.BRAND=GL.MANUFACTURER 
                                            OR  A.A1=GL.Number1 
                                            OR  CAT.A2 =  GL.DEPARTMENT
                                            OR  CAT.A3=  GL.CLASS
                                            OR  CAT.A4= GL.SUBCLASS
                                            OR  CAT.A5= GL.LINE


    LEFT JOIN  RULE_GROUPS RG ON GL.GROUP_ID=RG.GROUP_ID
    LEFT JOIN  RULE_GROUPS RG2 ON RG.RULE_ID=RG2.RULE_ID                                            
    LEFT JOIN  GROUP_LINES GL2 ON RG2.GROUP_ID=GL2.GROUP_ID
    LEFT JOIN  PRICING_RULES PR ON RG.RULE_ID=PR.RULE_ID
    LEFT JOIN  PRICING_ATTRIBUTES PA ON PR.RULE_ID=PA.RULE_ID

WHERE A.INVEN_ID='123456'

For some reasons, I do not feel that the above query is written in a best possible way. Is there a way I could re-write the above query in a better way?

Upvotes: 0

Views: 251

Answers (1)

LDMJoe
LDMJoe

Reputation: 1589

I initially intended to leave this as a comment, but it got too long.

PR.* might be returning more fields than you actually need in your result set. Depending on the size of the table, listing the fields you need explicitly may just be theoretical improvement.

Anyway, if this is returning exactly what you want, I wouldn't try and mess with the query itself. Even if you do manage to get something that returns the exact same result written in a different way, I would not be surprised at all if when you look at the execution plan it ends up being exactly the same.

I would recommend instead taking your working, known good query, and feeding it to the Tuning Advisor, which will figure out how to make the query run faster by creating indexes and statistics and such. It's a great tool and sometimes the results are almost magical.

Upvotes: 2

Related Questions