Why does the presence of a UDF (that is not referenced) in a view cause the execution plan to change

3 Nov 2005 6:59 PM

smaully

I am running the TPC-H benchmark query 1 and I noticed the execution
plan changes drastically if I create a view that projects all of the
columns of the table and calls a UDF on a column that will not be
referenced in query 1.

--
-- Query and plan for query using the table LINEITEM_NEW
--
select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    LINEITEM_NEW
where
    l_shipdate <= DATEADD(day, -90, '1998-12-01')
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus

(1 row(s) affected)

|--Compute Scalar(DEFINE:([Expr1002]=If ([globalagg1011]=0) then NULL

else [globalagg1013], [Expr1003]=If ([globalagg1015]=0) then NULL else
[globalagg1017], [Expr1004]=If ([globalagg1019]=0) then NULL else
[globalagg1021], [Expr1005]=If ([globalagg1023]

|--Stream Aggregate(GROUP BY:([LINEITEM_NEW].[L_RETURNFLAG],
[LINEITEM_NEW].[L_LINESTATUS])

DEFINE:([globalagg1011]=SUM([partialagg1010]),
[globalagg1013]=SUM([partialagg1012]),
[globalagg1015]=SUM([partialagg1014]), [globalagg1017]=SUM([partialagg1

|--Sort(ORDER BY:([LINEITEM_NEW].[L_RETURNFLAG] ASC,

[LINEITEM_NEW].[L_LINESTATUS] ASC))

|--Parallelism(Gather Streams)
|--Compute

Scalar(DEFINE:([partialagg1010]=[partialagg1010],
[partialagg1014]=[partialagg1014]))

|--Hash Match(Partial Aggregate,

HASH:([LINEITEM_NEW].[L_RETURNFLAG], [LINEITEM_NEW].[L_LINESTATUS]),
RESIDUAL:([LINEITEM_NEW].[L_RETURNFLAG]=[LINEITEM_NEW].[L_RETURNFLAG]
AND [LINEITEM_NEW].[L_LINESTATUS]=[LINEITEM_NEW].[L_LINES

|--Table

Scan(OBJECT:([tpch_rsa].[dbo].[LINEITEM_NEW]),
WHERE:([LINEITEM_NEW].[L_SHIPDATE]<='Sep 2 1998 12:00AM'))

--
-- Query and plan for query using the LINEITEM_UDF_V
--
select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    LINEITEM_UDF_V
where
    l_shipdate <= DATEADD(day, -90, '1998-12-01')
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus

|--Sort(ORDER BY:([LINEITEM_NEW].[L_RETURNFLAG] ASC,

[LINEITEM_NEW].[L_LINESTATUS] ASC))

|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1053]=0) then NULL

else [Expr1054], [Expr1003]=If ([Expr1055]=0) then NULL else
[Expr1056], [Expr1004]=If ([Expr1057]=0) then NULL else [Expr1058],
[Expr1005]=If ([Expr1059]=0) then NULL else [Expr1060],

|--Hash Match(Aggregate,

HASH:([LINEITEM_NEW].[L_RETURNFLAG], [LINEITEM_NEW].[L_LINESTATUS]),
RESIDUAL:([LINEITEM_NEW].[L_RETURNFLAG]=[LINEITEM_NEW].[L_RETURNFLAG]
AND [LINEITEM_NEW].[L_LINESTATUS]=[LINEITEM_NEW].[L_LINESTATUS])
DEFINE:([Expr10

|--Table

Scan(OBJECT:([tpch_rsa].[dbo].[LINEITEM_NEW]),
WHERE:([LINEITEM_NEW].[L_SHIPDATE]<='Sep 2 1998 12:00AM'))

-- UDF definition
CREATE FUNCTION dbo.simple_udf
     (@pi_Data VARBINARY(8000))
RETURNS VARCHAR(8000) AS
BEGIN

     IF @pi_Data IS NULL
          RETURN NULL

    RETURN 'HELLO'
END

-- The view definition
CREATE VIEW [dbo].[LINEITEM_UDF_V] AS
SELECT [L_ORDERKEY] ,[L_PARTKEY] ,
[L_SUPPKEY] ,[L_LINENUMBER] ,
[L_QUANTITY] ,[L_EXTENDEDPRICE] ,
[L_DISCOUNT] ,[L_TAX] ,[L_RETURNFLAG] ,
[L_LINESTATUS] ,[L_SHIPDATE] ,
[L_COMMITDATE] ,[L_RECEIPTDATE] ,
[L_SHIPINSTRUCT] ,[L_SHIPMODE] ,
[dbo].simple_udf([L_COMMIT_NEW]) [L_COMMIT] ,
[ING_ROW_ID]
FROM [tpch_rsa].[dbo].[LINEITEM_NEW]

I have read SQL Server cannot parallelize queries with UDFs but in my
example the UDF is not being referenece.

I am using SQL Server 2000 service pack 3a on Window 2000 (dual
processors)

4 Nov 2005 11:05 PM

Erland Sommarskog

smaully (smaul***@ingrian.com) writes:

> I am running the TPC-H benchmark query 1 and I noticed the execution
> plan changes drastically if I create a view that projects all of the
> columns of the table and calls a UDF on a column that will not be
> referenced in query 1.
>...
> I have read SQL Server cannot parallelize queries with UDFs but in my
> example the UDF is not being referenece.

I don't know, but I would guess that it is a simple that SQL Server
does not check whether the UDF is used, but the mere presence of it
makes the query tainted.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp