SQL Tuning - Enhancement ???

• DKG2014S

  SSC Veteran

  Points: 267

  More actions

  February 25, 2014 at 9:04 am

  #286180

  Is there any other way of writing this query ?

  SQL Server 2008 R2

  LEFT OUTER LOOP JOIN PART_LIST p1 ON e.COLUMN3 = p1.CODE

  AND p1.LIST_TYPE = 'PART_TEST_TYPE'

  AND p1.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p2 ON e.SOURCE1 = p2.CODE

  AND p2.LIST_TYPE = 'SOURCE_TYPE'

  AND p2.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p3 ON e.SOURCE2 = p3.CODE

  AND p3.LIST_TYPE = 'SOURCE_TYPE'

  AND p3.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p4 ON e.STATUS = p4.CODE

  AND p4.LIST_TYPE = 'PART_STATUS'

  AND p4.LANGUAGE = 'en-US'

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 25, 2014 at 9:08 am

  #1691943

  Why do you have LOOP hints in your JOINS?

  A better option is to normalize the data 🙂

  I'm not sure what can you do better. An EAV design won't be great for performance.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• saber_talas

  Old Hand

  Points: 365

  More actions

  February 25, 2014 at 4:17 pm

  #1692087

  How long does it takes to execute the query?

  Post Execution plan.

  How many records each table has?

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  February 25, 2014 at 5:30 pm

  #1692107

  DKG2014S (2/25/2014)

  ---

  Is there any other way of writing this query ?

  SQL Server 2008 R2

  LEFT OUTER LOOP JOIN PART_LIST p1 ON e.COLUMN3 = p1.CODE

  AND p1.LIST_TYPE = 'PART_TEST_TYPE'

  AND p1.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p2 ON e.SOURCE1 = p2.CODE

  AND p2.LIST_TYPE = 'SOURCE_TYPE'

  AND p2.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p3 ON e.SOURCE2 = p3.CODE

  AND p3.LIST_TYPE = 'SOURCE_TYPE'

  AND p3.LANGUAGE = 'en-US'

  LEFT OUTER LOOP JOIN PART_LIST p4 ON e.STATUS = p4.CODE

  AND p4.LIST_TYPE = 'PART_STATUS'

  AND p4.LANGUAGE = 'en-US'

  Yes. And EAV's aren't necessarily the "horrible problem" that many make them out to be. I just can't advise you because I don't know what the rest of the query does because you've left it out.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)