THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

NOEXPAND Hint – Give your feedback!

I’ve just filled a feedback on Connect to ask for the possibility to use the NOEXPAND hint with CTEs, Views and Subqueries. This will help to avoid several problems, both from a logical and performance point of view.

https://connect.microsoft.com/SQLServer/feedback/details/533766/make-noexpand-hint-usable-also-with-cte-views-and-subqueries 

Don’t you think is *so* important? Well, let me show you two example that, with Itzik’s help, I’ve set up to show what kind of problem we face right now…problems that can be solved only using a temporary table to put intermediate data. This means that we *must* use stored procedures, which is not always possible. With the ability to use the NOEXPAND hint, all those problems would be solved.

Problem 1 – Query Execution Error

This sample shows that sometimes the query processor may decide to push the predicate in the cte (ptype = ‘number’) to the outer query and process it after the outer query’s predicate (cast(val as int) = 100). Of course this lead to an error when the value “DONTWORK” is cast to int. If the CTE wouldn't have been expanded, its predicate would have been processed always before, thus guaranteeing execution correctness and stability. This error may not happen always, since if the table is small enough (say, 100 rows instead of 1000) the processor won’t “push out” the inner predicate, and thus will avoid the error. Unfortunately this behavior is not easily predictable so the results is that they query may or may not work from time to time. A NOEXPAND hint will solve this problem once and for all.

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;

Problem 2 – Performances

This sample shows that the CTE gets evaluated two times, while it could have been evaluated only once, and thus doubling the performances of the query itself, requiring only the half of the original I/O. The workaround today is the usage of temporary table to store CTE results. Being able to use the NOEXPAND hint here will help avoid putting the result of the CTE into a temp table and the query that temp table to produce the final result with optimum performances.

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

On the Connect page you can find the scripts above and the proposed solution. I think is a feature we *really* need. If you think so, please vote, thanks!

Published Wednesday, February 17, 2010 7:26 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mazhar Karimi said:

Use Table Variable instead of CTE, and compare the results.

March 11, 2010 10:16 AM
 

Davide Mauri said:

Hi Mazhar, the problem with TV is that they are variables and thus for them SQL Server doesn't calculate data distribution statistics. This means that have a very high probability that you'll get sub-optimal execution plans.

March 12, 2010 4:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement