none
Using OPTIMIZE FOR in Procedure with IF () statement

    Question

  • This works:

    CREATE PROCEDURE [test]
    @t INT
    AS
    BEGIN
     SELECT * FROM t_Table WHERE ID = @t
     OPTION (OPTIMIZE FOR (@t UNKNOWN))
    END

    This does not:

    CREATE PROCEDURE [test]
    @t INT
    AS
    BEGIN
     IF EXISTS (
     SELECT * FROM t_Address WHERE AddressID = @t
     OPTION (OPTIMIZE FOR (@t UNKNOWN))
     )
     SELECT 1
    END

    Seems like if I add the "IF" clause, I can't use OPTIMIZE FOR.  Anyone know how to make this work?

    Thanks


    Matt

    Saturday, September 07, 2013 10:52 PM

Answers

  • My guess is 1) you're not going to be able to use a query hint inside a IF EXISTS(), and 2) you almost certainly don't need the query hint.

    1)  You're not going to be able to use a query hint inside a IF EXISTS().  The definition of the EXISTS keyword is IF EXISTS(<subquery>).  Hints cannot be used on subqueries, they have to be applied to the outer query.  But when you do IF EXISTS(), you don't have an outer query, so the syntax doesn't allow a hint to be used.

    2) You almost certainly don't need the query hint.  Optimize For hints are useful if the optimal plan depends on the value of a parameter.  If you have a table like

    Create Table MyTable(MyID int primary key,
      MyDataA int,
      MyDataB varchar(10));
    Create Index MyTableIdx On MyTable(MyDataA);

    and that table has 1,000,000 rows and 999,999 of those rows has MyDataA = 0 and 1 row has MyDataA = 1 and you run a query like

    Select MyID, MyDataA, MyDataB From MyTable Where MyDataA = @MyParameter

    Now if @MyParameter has the value 1, you want SQL to use the MyTableIdx because it would go directly to the one row in the index, then grab that row from the clustered index andd it's done.  But if it doesn't use the MyTableIdx, it has to scan the whole clustered index and that will be slower.

    But if @MyParameter has the value 0, you do not want to use the MyTableIdx.  The reason is that you will have to get an entry from the MyTableIdx, look it up in the clustered index, get the next entry in MyTableIdx, look that entry up in the clustered index, repeat 999,999 times.  So it will need to read every page in MyTableIdx plus every page in the clustered index (and each page in the clustered index may be read many, many times since each page has multiple rows).  In this case it is far better to just scan the clustered index.

    And if @MyParameter has some value like 2 that doesn't exist in the table, SQL tries to find that value in the MyTableIdx, doesn't find it and returns a result set with 0 rows.

    So for different values of the parameter, you get drastically different query plans.  The OPTIMIZE FOR UNKNOWN is a hint to SQL to pick a plan that works best in the "typical" case.

    But consider the case

    If Exists(Select * From MyTable Where MyDataA = @MyParameter)

    In this case if @MyParameter has the value 1, SQL looks up the first row in MyTableIdx with MyDataA = 1, if it finds one, it stops immediately and returns true.  Same thing if @MyParameter has the value 0, SQL looks up the first row in MyTableIdx with MyDataA = 0, if it finds one, it stops immediately and returns true.  The fact that there are 999,998 other rows with MyDataA = 0 makes no difference.  And if @MyParameter has some value like 2 that doesn't exist in the table, SQL looks up that value in the MyTableIdx, doesn't find it and returns false.  So no matter what the value of @MyParameter is, you end up with the same plan.  So it makes no sense to try to tell SQL which value of @MyParameter to optimze for.

    Tom

    Sunday, September 08, 2013 3:01 AM

All replies

  • Is this a real issue? Theoritical? Likelihood is high that HINT is not necessary in a IF EXISTS statement.

    Is there index on AddressID?

    The message is loud & clear, you cannot do it.

    CREATE PROCEDURE [test]
     @t INT
     AS
     BEGIN
      IF EXISTS (
      SELECT * FROM t_Address WHERE AddressID = @t
      OPTION (OPTIMIZE FOR (@t UNKNOWN))
      )
      SELECT 1
     END
    
    /*
    Msg 156, Level 15, State 1, Procedure test, Line 7
    Incorrect syntax near the keyword 'OPTION'.
    */

    Optimization bible:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012




    Sunday, September 08, 2013 1:53 AM
    Moderator
  • Seems like if I add the "IF" clause, I can't use OPTIMIZE FOR.  Anyone know how to make this work?

    Instead of IF, consider specifying the condition in a WHERE clause:

    CREATE PROCEDURE [test]
    	@t INT
    AS
    BEGIN
    	SELECT 1
    	WHERE EXISTS (
    		SELECT * 
    		FROM t_Address 
    		WHERE AddressID = @t
    		)
    	OPTION (OPTIMIZE FOR (@t UNKNOWN));
    END;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 08, 2013 2:19 AM
  • My guess is 1) you're not going to be able to use a query hint inside a IF EXISTS(), and 2) you almost certainly don't need the query hint.

    1)  You're not going to be able to use a query hint inside a IF EXISTS().  The definition of the EXISTS keyword is IF EXISTS(<subquery>).  Hints cannot be used on subqueries, they have to be applied to the outer query.  But when you do IF EXISTS(), you don't have an outer query, so the syntax doesn't allow a hint to be used.

    2) You almost certainly don't need the query hint.  Optimize For hints are useful if the optimal plan depends on the value of a parameter.  If you have a table like

    Create Table MyTable(MyID int primary key,
      MyDataA int,
      MyDataB varchar(10));
    Create Index MyTableIdx On MyTable(MyDataA);

    and that table has 1,000,000 rows and 999,999 of those rows has MyDataA = 0 and 1 row has MyDataA = 1 and you run a query like

    Select MyID, MyDataA, MyDataB From MyTable Where MyDataA = @MyParameter

    Now if @MyParameter has the value 1, you want SQL to use the MyTableIdx because it would go directly to the one row in the index, then grab that row from the clustered index andd it's done.  But if it doesn't use the MyTableIdx, it has to scan the whole clustered index and that will be slower.

    But if @MyParameter has the value 0, you do not want to use the MyTableIdx.  The reason is that you will have to get an entry from the MyTableIdx, look it up in the clustered index, get the next entry in MyTableIdx, look that entry up in the clustered index, repeat 999,999 times.  So it will need to read every page in MyTableIdx plus every page in the clustered index (and each page in the clustered index may be read many, many times since each page has multiple rows).  In this case it is far better to just scan the clustered index.

    And if @MyParameter has some value like 2 that doesn't exist in the table, SQL tries to find that value in the MyTableIdx, doesn't find it and returns a result set with 0 rows.

    So for different values of the parameter, you get drastically different query plans.  The OPTIMIZE FOR UNKNOWN is a hint to SQL to pick a plan that works best in the "typical" case.

    But consider the case

    If Exists(Select * From MyTable Where MyDataA = @MyParameter)

    In this case if @MyParameter has the value 1, SQL looks up the first row in MyTableIdx with MyDataA = 1, if it finds one, it stops immediately and returns true.  Same thing if @MyParameter has the value 0, SQL looks up the first row in MyTableIdx with MyDataA = 0, if it finds one, it stops immediately and returns true.  The fact that there are 999,998 other rows with MyDataA = 0 makes no difference.  And if @MyParameter has some value like 2 that doesn't exist in the table, SQL looks up that value in the MyTableIdx, doesn't find it and returns false.  So no matter what the value of @MyParameter is, you end up with the same plan.  So it makes no sense to try to tell SQL which value of @MyParameter to optimze for.

    Tom

    Sunday, September 08, 2013 3:01 AM
  • I agree with Tom. This query dose not need optimization hint!

    sqldevelop.wordpress.com

    Sunday, September 08, 2013 4:15 AM