THE SQL Server Blog Spot on the Web

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

Alexander Kuznetsov

Inline UDFs do not prevent parallel execution plans

Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our queries are wrapped in inline UDFs or copied and pasted into the main query.

As usual, I will provide a repro script.

Prerequisites

Let us set up a table with 2 million rows - that should be enough to qualify for a parallel plan:


CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers
    
( n )
        
VALUES  ( 1 );
GO
DECLARE @i INT;
    
SET @i=0;
WHILE @i<21  
    
BEGIN
    INSERT INTO
dbo.Numbers
        
( n )
        
SELECT n + POWER(2, @i)
        
FROM dbo.Numbers;
    
SET @i = @i + 1;
    
END;  
GO

SELECT  n ,
        
'asdfasdfas' AS Filler
INTO    dbo.ParallelTest
FROM    dbo.Numbers ;
GO

ALTER TABLE dbo.ParallelTest ADD CONSTRAINT PK_ParallelTest PRIMARY KEY(n) ;
GO
 

Running a query with parallel execution plan

The following query runs with parallel execution plan (number of executions is 12):

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT COUNT(*) FROM dbo.ParallelTest
WHERE Filler LIKE '%nosuchstring%'

Let us wrap it in an inline UDF:

CREATE FUNCTION dbo.MatchCountInParallelTestByPattern ( @Pattern VARCHAR(10) )
RETURNS TABLE
AS RETURN
  
( SELECT  COUNT(*) AS MatchCount
    
FROM    dbo.ParallelTest
    
WHERE   Filler LIKE @Pattern
  
) ;

The following query invokes the inline UDF, yet still runs with exactly the same parallel execution plan (number of executions is the same: 12):


SELECT  MatchCount
FROM    dbo.MatchCountInParallelTestByPattern('%nosuchstring%') ;
 

Conclusion

At the time of this writing, reusing code wrapped in inline UDFs does not prevent parallel execution plans, at least on the following version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

 

Published Wednesday, February 20, 2013 9:23 AM by Alexander Kuznetsov
Filed under:

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

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

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