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

Comments

No Comments
New Comments to this post are disabled

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

Privacy Statement