Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Tuning - Enhancement ??? Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:05 PM
Points: 3, Visits: 69
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'
Post #1544989
Posted Tuesday, February 25, 2014 9:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:22 PM
Points: 3,814, Visits: 8,587
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1544991
Posted Tuesday, February 25, 2014 4:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:35 PM
Points: 11, Visits: 110
How long does it takes to execute the query?
Post Execution plan.
How many records each table has?
Post #1545160
Posted Tuesday, February 25, 2014 5:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 35,404, Visits: 31,967
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545182
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse