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

Status : 

 


57
0
Sign in
to vote
ID 533766 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 2/15/2010 10:38:16 AM
Access Restriction Public

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

*/ 


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