Home Dashboard Directory Help
Search

Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n) by MichaelSmith


Status: 

Active


6
0
Sign in
to vote
Type: Suggestion
ID: 483181
Opened: 8/15/2009 9:43:53 PM
Access Restriction: Public
Primary Feedback Item: 218968
0
Workaround(s)
view

Description

Sometimes queries would perform faster if one or more table expressions were evaluated individually & "materialized", rather than the optimizer/algebrizer transforming the query.

Providing a query hint which enumerates those CTEs we want materialized would provide a more elegant alternative to temp tables/variables and manually breaking-out the query into steps. Of course, there will be times when a temp table, and appropriate interim indexes, will still be needed. But materializing as a worktable, could improve performance, while keeping the query SQL-esqe. (as opposed to procedural)
Details
Sign in to post a comment.
Posted by crokusek3 on 4/4/2013 at 4:48 PM
Please consider the ability to specify the materialized table's PK with the materialization hint else I'm still out of luck for some cases. I am constantly using temp tables to work around the lack of materialization issue. Thanks!
Posted by Microsoft on 8/27/2009 at 3:30 PM
Hello Mike,

Thank you for the feedback and sorry for the delay in responding to you! I'm going to resolve this item as duplicate of the feedback item 218968 (see Adam's comment). You can also vote on that item to increase its visibility internally.

Regards,
Boris Baryshnikov.
SQL Server Engine
Posted by Adam Machanic on 8/16/2009 at 6:38 PM
Duplicate:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=218968
Sign in to post a workaround.