StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I ran a small benchmark - in this case the approach with a UDF runs almost 100 times slower.</p> <h3>The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms</h3> <h3>The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms</h3> <p>Here's the Sql code...</p> <p>-- set up an auxiliary table Numbers with 128K rows:</p> <pre><code>CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY) GO DECLARE @i INT; SET @i = 1; INSERT INTO dbo.Numbers(n) SELECT 1; WHILE @i<128000 BEGIN INSERT INTO dbo.Numbers(n) SELECT n + @i FROM dbo.Numbers; SET @i = @i * 2; END; GO </code></pre> <p>-- the tables</p> <pre><code>CREATE TABLE dbo.Animals (AnimalId INT NOT NULL IDENTITY PRIMARY KEY, AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile, etc.. Name VARCHAR(30)) GO ALTER TABLE dbo.Animals ADD CONSTRAINT UNQ_Animals UNIQUE(AnimalId, AnimalType) GO 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) ); GO </code></pre> <p>--- populating with UDF:</p> <pre><code>INSERT INTO dbo.Animals (AnimalType, Name) 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; </code></pre> <p>results are:</p> <pre><code>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) </code></pre> <p>--- populating with FK:</p> <pre><code>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(AnimalId, AnimalType) REFERENCES dbo.Animals(AnimalId, AnimalType) ); INSERT INTO dbo.Mammals2 (AnimalId,AnimalType,SomeOtherStuff) SELECT n, 1, 'some info' FROM dbo.Numbers; </code></pre> <p>results are:</p> <pre><code>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. </code></pre> <p>-- populating without any integrity:</p> <pre><code>CREATE TABLE dbo.Mammals3 (AnimalId INT NOT NULL PRIMARY KEY, SomeOtherStuff VARCHAR(10) ); INSERT INTO dbo.Mammals3 (AnimalId,SomeOtherStuff) SELECT n, 'some info' FROM dbo.Numbers; </code></pre> <p>results are:<br> SQL Server parse and compile time: CPU time = 1 ms, elapsed time = 1 ms.</p> <pre><code>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) </code></pre> <p>The overhead of an FK in CPU time = 375 ms - 297 ms = 78 ms<br> The overhead of an UDF in CPU time = 7750 ms - 297 ms = 7453 ms</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload