none
How to use SQL Hints within CTE/subquery

    Question

  • Hi,

    I am trying to use the SQL Server Hints in CTE or in subquery but it is giving me the syntax error. Below is the sample code.

     

    WITH
     Page AS
    
    (
      SELECT
     *
    	FROM
     Table_1
    		INNER
     JOIN
     Table_2 ON
     Table_1.c1=Table_2.c1		 
    	WHERE
     Table_1.c2='sample'
    
    	OPTION
     (FORCE ORDER
    );
    )
    

     


    Regards, Jigs
    Wednesday, September 15, 2010 6:22 AM

Answers

  • Erland Sommarskog, You are right. I didn't know about that hint and I used it first time as it looked me that it may help me and it has.

    Let me describe my scenario more to you. I have implemented custom paging in my proj. by passing the SortFieldName and SortDirection from my application. I also pass the Page details that are required. Now in my SP I first join only required tables and sort the given field in given direction. I am storing result a temporary table with UniqId and RowNumber. In other select query I have filter on RowNumber for particular page and other conditions. In this select query I joined the temporary table with other table. At this point the performance is hitting and joining is not working. Now I used the FORCE ORDER hint and it is working for me.

    Currently time is not permitting me to elaborate more in this thread but tell me if any better approach is there to implement efficient paging.

    Regards, Jigs
    • Marked as answer by jdp12383 Thursday, May 31, 2012 9:13 AM
    Thursday, September 16, 2010 4:54 AM

All replies

  • OPTION with query hints is not allowed in CTE definition.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 15, 2010 6:38 AM
  • I hope below link will help

    http://msdn.microsoft.com/en-US/library/ms181714(v=SQL.90).aspx

     

    USE AdventureWorks;
    GO
    --Creates an infinite loop
    WITH cte (EmployeeID, ManagerID, Title) as
    (
        SELECT EmployeeID, ManagerID, Title
        FROM HumanResources.Employee
        WHERE ManagerID IS NOT NULL
      UNION ALL
        SELECT cte.EmployeeID, cte.ManagerID, cte.Title
        FROM cte
        JOIN  HumanResources.Employee AS e
            ON cte.ManagerID = e.EmployeeID
    )
    --Uses MAXRECURSION to limit the recursive levels to 2
    SELECT EmployeeID, ManagerID, Title
    FROM cte
    OPTION (MAXRECURSION 2);
    GO

    Wednesday, September 15, 2010 6:41 AM
  • Hints are 3 types.. Query Hint,Table Hint, Join Hint.

    "FORCE ORDER"  is a "QUERY HINT" 

    a query hint will apply to entire query.

    As forceorder is a query hint, and it will be applied to entire query, the syntax is written in such a way that, you can apply that hint only at the end of the query.

    Even when you add it at the end of the query, still it will apply with in that CTE also

    WITH Page AS
    (
     SELECT *	FROM Table_1
     INNER JOIN Table_2 ON Table_1.c1=Table_2.c1		 
     WHERE Table_1.c2='sample'
    )
    select * from Page 
    OPTION (FORCE ORDER)
    
    
    
    Wednesday, September 15, 2010 6:43 AM
  • Thanks all for your responses.

    ramireddy, Actually I wanted to apply hint within CTE which seems not possible. I tried your suggested way but it is taking too much time and seems optimizer is taking its own path.

     


    Regards, Jigs
    Wednesday, September 15, 2010 7:07 AM
  • see the Note in below URL

    http://msdn.microsoft.com/en-US/library/ms181714(v=SQL.90).aspx

     
    Using FORCE ORDER does not affect possible role reversal behavior of the query optimizer. For more information, see Understanding Hash Joins.

     

    In your case, there is only one join. What is the use of forcing the join order??  do u have any indexes on c1 or c2??

    Wednesday, September 15, 2010 7:20 AM
  • > ramireddy, Actually I wanted to apply hint within CTE which seems not possible. I tried your suggested way but it is taking too much time and seems optimizer is taking its own path.

    FORCE ORDER is quite a hefty hint, and incorrectly applied it can cause consdierable damage. Any particular reason you feel compelled to use this hint?

    You could insert the result of the CTE into a temp table, in which case you could use FORCE ORDER. But I strongly suspect that your real problem is elsewhere and you should stay away from FORCE ORDER.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, September 15, 2010 7:27 AM
  • ramireddy, The code I written is just for your understanding and my actual query is totally different having 9 tables in join. It is not chosing the best path which it should and I wanted to use the FORCE ORDER hint here.

    I have created necessary indexes on the needy columns.


    Regards, Jigs
    Wednesday, September 15, 2010 7:52 AM
  • Erland Sommarskog, The reason behind using this hint is to force the join order. Actually I have 50 record page table with required Uniq Id filled. I want to extract whole 50 records from other 1 Million record table. For that I need to use that hint. The optimizer doesn't seem to use it.

    Regards, Jigs
    Wednesday, September 15, 2010 10:27 AM
  • > Erland Sommarskog, The reason behind using this hint is to force the join order. Actually I have 50 record page table with required Uniq Id filled. I want to extract whole 50 records from other 1 Million record table. For that I need to use that hint. The optimizer doesn't seem to use it.

    Of course, since I don't know the tables, indexes and the query, I cannot comment. However, my general experience is that FORCE ORDER is a hint you use very very rarely. Also, it is my experience that queries where the optimizer does not do the best job, often have a problem of some sort. Maybe some good index is missing. Maybe some important condition is missing. Maybe some condition is written in a way that good use of an index is precluded.

    In a second post you say that you're real query has 9 tables. It is very unlikely that you can say beforehand which is the best join order for these nine tables. I certainly would like to make such bets - because I know that I would probably be wrong.

    Or to be perfectly blunt: if you don't even know there the FORCE ORDER hint goes in the syntax, you do absolutely not have the knowledge to use it correctly.

    You could post your table and index definition here, as well as you query. But obviously a nine-table query is a bit more than a mouthful for a forum discussion.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, September 15, 2010 9:57 PM
  • Erland Sommarskog, You are right. I didn't know about that hint and I used it first time as it looked me that it may help me and it has.

    Let me describe my scenario more to you. I have implemented custom paging in my proj. by passing the SortFieldName and SortDirection from my application. I also pass the Page details that are required. Now in my SP I first join only required tables and sort the given field in given direction. I am storing result a temporary table with UniqId and RowNumber. In other select query I have filter on RowNumber for particular page and other conditions. In this select query I joined the temporary table with other table. At this point the performance is hitting and joining is not working. Now I used the FORCE ORDER hint and it is working for me.

    Currently time is not permitting me to elaborate more in this thread but tell me if any better approach is there to implement efficient paging.

    Regards, Jigs
    • Marked as answer by jdp12383 Thursday, May 31, 2012 9:13 AM
    Thursday, September 16, 2010 4:54 AM