SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Optimize Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oiledor
Starting Member

Philippines
3 Posts

Posted - 07/02/2014 :  22:50:52  Show Profile  Reply with Quote
This one is slow and I know it can be optimized, anybody who have ideas on what should I change? TIA


SELECT table1.REFERENCE,
         table2.Column2,
         table2.Column3,
         table2.Column4
  FROM   table1
         INNER JOIN table2
           ON table1.REFERENCE = table2.REFERENCE
  WHERE  ( table2.LinkedReference = @whereReference)
          OR ( table2.REFERENCE IN ((SELECT table1.table2_REF
                                   FROM   table1
                                          INNER JOIN table3
                                            ON table1.REFERENCE = table3.REFERENCE
                                   WHERE  ( table3.LinkedReference = @whereReference)
                                           OR ( table3.REFERENCE IN (SELECT REFERENCE
                                                                    FROM   table5
                                                                    WHERE  LinkedReference = @whereReference) )
                                           OR ( table3.REFERENCE IN (SELECT table3_REF
                                                                    FROM   table6
                                                                    WHERE  LinkedReference = @whereReference) )
                                           OR ( table3.REFERENCE IN (SELECT REFERENCE
                                                                    FROM   table7
                                                                    WHERE  LinkedReference = @whereReference) )
                                           OR ( table3.REFERENCE IN (SELECT REFERENCE
                                                                    FROM   table8
                                                                    WHERE  LinkedReference = @whereReference) ))) )
          OR ( table2.REFERENCE IN (SELECT REFERENCE
                                  FROM   table9
                                  WHERE  LinkedReference = @whereReference) )
             AND TYPE = 'L'
             AND table2.MarkedForDeletion = 'N'
             AND table1.REFERENCE NOT IN (SELECT REFERENCE
                                         FROM   table1
                                         WHERE  CATEGORY = 'x')

tkizer
Almighty SQL Goddess

USA
36985 Posts

Posted - 07/03/2014 :  12:14:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are the join conditions and where clause columns indexed? Please post the execution plan and the output of SET STATISTICS IO.

I would think you could switch those ORs to LEFT JOINs. If you'd like better help, please check out this link: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000