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

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

Surely this sounds like common knowledge, yet I decided to post a simple benchmark demonstrating how slow they are - in this case almost 100 times slower than a foreign key. Recently Pure Krome asked a question on stackoverflow about implementing subtypes in the database and the discussion lead to such benchmarking.

The DDL for parent and child tables

Here is the parent table:

 CREATE TABLE dbo.Animals
(AnimalId INT NOT NULL IDENTITY PRIMARY KEY,
AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile, etc..
Name VARCHAR(30))

 The child table and the check constraint that guarantees that all rows in the child table refer to Mammals:

CREATE FUNCTION dbo.GetAnimalType(@AnimalId INT)
RETURNS TINYINT
AS
BEGIN
DECLARE 
@ret TINYINT;
SELECT @ret AnimalType FROM dbo.Animals
  
WHERE AnimalId @AnimalId;
RETURN @ret;
END
GO
CREATE TABLE dbo.Mammals
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_AnimalType_Mammal CHECK(dbo.GetAnimalType(AnimalId)=1)
); 

 Another approach, copying AnimalType column in the child table, and using a foreign key

 CREATE TABLE dbo.Mammals2
(AnimalId INT NOT NULL PRIMARY KEY,
AnimalType TINYINT NOT NULL,
SomeOtherStuff VARCHAR(10),
CONSTRAINT Chk_Mammals2_AnimalType_Mammal CHECK(AnimalType=1),
CONSTRAINT FK_Mammals_Animals FOREIGN KEY(AnimalIdAnimalType)
  
REFERENCES dbo.Animals(AnimalIdAnimalType)
);

And finally the child table without enforcing any logic with regards to AnimalType:

 

CREATE TABLE dbo.Mammals3
(AnimalId INT NOT NULL PRIMARY KEY,
SomeOtherStuff VARCHAR(10)
);
 

Setting up the helper table:


CREATE TABLE dbo.Numbers(INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i 1;
INSERT INTO dbo.Numbers(nSELECT 1;
WHILE @i<128000 BEGIN
  INSERT INTO 
dbo.Numbers(n)
    
SELECT @i FROM dbo.Numbers;
  
SET @i @i 2;
END

 

Benchmarking results

The overhead of a FK was 78 ms

The overhead of a UDF wrapped in CHECK constraint was 7453 ms

 Inserting 128K rows with a UDF:

INSERT INTO dbo.Animals
  
(AnimalTypeName)
SELECT 1'some name' FROM dbo.Numbers;
GO
SET STATISTICS IO ON
SET STATISTICS 
TIME ON
GO
INSERT INTO dbo.Mammals
(AnimalId,SomeOtherStuff)
SELECT n'some info' FROM dbo.Numbers;
  

SQL Server parse and compile time: 
CPU time
= 0 ms, elapsed time = 2 ms.
Table 'Mammals'. Scan count 0, logical reads 272135,
    physical reads
0, read-ahead reads 0, lob logical reads 0,
    lob physical reads
0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
    read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
    lob read
-ahead reads 0.

SQL
Server Execution Times:
    CPU time
= 7750 ms,  elapsed time = 7830 ms.


(131072 row(s) affected)
 

  Inserting 128K rows with a FK:

 INSERT INTO dbo.Mammals2
(AnimalId,AnimalType,SomeOtherStuff)
SELECT n1'some info' FROM dbo.Numbers;
 

SQL Server parse and compile time: 
   CPU time
= 93 ms, elapsed time = 100 ms.
Table 'Animals'. Scan count 1, logical reads 132, physical reads 0,
    read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
    lob read
-ahead reads 0.
Table 'Mammals2'. Scan count 0, logical reads 275381, physical reads 0,
   read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
   lob read
-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
   read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
   lob read
-ahead reads 0.

SQL
Server Execution Times:
   CPU time
= 375 ms,  elapsed time = 383 ms.

 

The baseline: inserting 128K rows into a table without CHECK or FK:

 INSERT INTO dbo.Mammals3
(AnimalId,AnimalType,SomeOtherStuff)
SELECT n1'some info' FROM dbo.Numbers;

 

SQL Server Execution Times:
   CPU time
= 0 ms,  elapsed time = 66 ms.
Table 'Mammals3'. Scan count 0, logical reads 272135, physical reads 0,
    read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
    lob read
-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 441, physical reads 0,
    read
-ahead reads 0, lob logical reads 0, lob physical reads 0,
    lob read
-ahead reads 0.

SQL
Server Execution Times:
   CPU time
= 297 ms,  elapsed time = 303 ms.


(131072 row(s) affected)
 
The results of benchmarking are very clear, but the story does not end here, there is more to it: 
A UDF wrapped in a CHECK constraint may not work correctly for multi row updates:
Read the following post by Tony Rogerson:
Using a UDF in a CHECK constraint to check validity of History Windows
 

 

Published Thursday, June 25, 2009 10:36 PM by Alexander Kuznetsov

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 at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

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