Home Dashboard Directory Help
Search

Make NOEXPAND hint usable also with CTE, Views and Subqueries by MauriD


Status: 

Active


54
0
Sign in
to vote
Type: Suggestion
ID: 533766
Opened: 2/15/2010 10:38:16 AM
Access Restriction: Public
1
Workaround(s)
view

Description

It would be very very very very useful to have the possibilty to use the NOEXPAND hint also with CTE, to do something like:

WITH cte OPTION(NOEXPAND) AS
(
cte_body
),
cte2 AS
(
cte2_body
...
SELECT ... FROM cte_xxx WHERE ....
)

In this way we can tell optmizer when it's better to avoid expansion of CTE into main query. In some (rare) cases this lead to very poor plans, or query execution results. Here are the script to reproduce those situations:

---------------------- SAMPLE 1 ----------------------

/*
    Request for NOEXPAND hint in CTE, VIEWS & SUBQUERIES
    
    Platform: SQL Server 2008 SP1

    Problem 1    
    This example shows a query where the predicate of the inner query
    is pushed to the external one and gets avaluated *after* the external query
    predicate, thus generating an error. The usage of the NOEXPAND hint
    would solve the problem.

*/

USE tempdb
go

if object_id('dbo.fn_Nums') is not null drop function dbo.fn_Nums;
go

create function dbo.fn_Nums(@m as bigint) returns table
as
return
with
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select row_number() over (order by n) as n from t5)
select n from result where n <= @m
go

if object_id('dbo.properties') is not null drop table dbo.properties;
go

create table dbo.properties
(
id int not null,
ptype varchar(20) not null,
val varchar(50) not null,
filler binary(200) not null default(0x01)
);

insert into dbo.properties(id, ptype, val)
select n, 'number', cast(n as varchar(11)) from dbo.fn_nums(1000)
union all
select 100000, 'string', 'DONTWORK'
go

create index idx_val on dbo.properties(val);
go

with numbers as
(
select id, val
from dbo.properties
where ptype = 'number'
)
select *
from numbers
where cast(val as int) = 100;

/*

PROPOSED SOLUTION
-----------------


with noexpand numbers as
(
select id, val
from dbo.properties
where ptype = 'number'
)
select *
from numbers
where cast(val as int) = 100;

*/


---------------------- SAMPLE 2 ----------------------


/*
    Request for NOEXPAND hint in CTE, VIEWS & SUBQUERIES
    
    Platform: SQL Server 2008 SP1

    Problem 2
    This example shows a query performance are not optimal since the
    subquery gets evaluated two times instead of one. The usage of
    the NOEXPAND hint would solve the problem.

*/

USE AdventureWorksDW
GO

IF OBJECT_ID('dbo.FactInternetSalesBig', 'U') IS NOT NULL
    DROP TABLE dbo.FactInternetSalesBig
GO    

SELECT * INTO dbo.FactInternetSalesBig FROM dbo.FactInternetSales fis
Go

INSERT INTO dbo.FactInternetSalesBig SELECT * FROM dbo.FactInternetSales fis
GO 10

WITH cte AS
(
    SELECT                                         
        year_orderdate = dt.CalendarYear,
        total_amount = SUM(fisb.SalesAmount)
    FROM    
        dbo.FactInternetSalesBig fisb
    INNER JOIN
        dbo.DimTime dt ON fisb.OrderDateKey = dt.TimeKey
    GROUP BY
        dt.CalendarYear
)
SELECT
    c.year_orderdate,
    diff = c.total_amount - p.total_amount
FROM
    cte AS c
LEFT JOIN
    cte AS p ON c.year_orderdate = p.year_orderdate + 1

/*

PROPOSED SOLUTION
-----------------

WITH NOEXPAND cte AS
(
    SELECT                                         
        year_orderdate = dt.CalendarYear,
        total_amount = SUM(fisb.SalesAmount)
    FROM    
        dbo.FactInternetSalesBig fisb
    INNER JOIN
        dbo.DimTime dt ON fisb.OrderDateKey = dt.TimeKey
    GROUP BY
        dt.CalendarYear
)
SELECT
    c.year_orderdate,
    diff = c.total_amount - p.total_amount
FROM
    cte AS c
LEFT JOIN
    cte AS p ON c.year_orderdate = p.year_orderdate + 1

*/


Details
Sign in to post a comment.
Posted by Microsoft on 2/18/2010 at 10:17 AM
Hi Mauri,
Thanks for your feedback. We will consider it for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.
Posted by Razvan Socol on 2/17/2010 at 9:55 PM
Workaround for the first query:
create index idx_val_number on dbo.properties(val) where ptype='number'

Razvan