Optimize Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Optimize Stored Procedure

Author

Topic

oiledor
Starting Member

3 Posts

Posted - 2014-07-02 : 22:50:52

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

38200 Posts

Posted - 2014-07-03 : 12:14:45

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources