June 17, 2009 at 3:03 pm
I'm trying to tidy up a SPROC by wrapping a CASE statement up in a function and I'm geting a huge performance hit. If I leave the CASE statement in the base script it runs in a second, I move it to a function and it takes over a minute. Below is how I'm handling the case portion inline and using the function as well as the function itself. What am I missing?
TIA
INLINE SNIPPET:
"StaffType"= CASE
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'V' AND REG_TEMP = 'T' THEN 'Vendor'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'R' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'I' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Employee'
ELSE 'ERROR'
END
FUNCTION CALL VERSION SNIPPET:
, "StaffType" = (SELECT dbo.fn_StaffTypeMap(p.EMPLID))
FUNCTION:
USE [HRData]
GO
/****** Object: UserDefinedFunction [dbo].[fn_StaffTypeMap] Script Date: 06/17/2009 13:00:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
AUTHOR: Bill King
CREATE DATE: 6/16/2009
DESCRIPTION: Used to convert the PER_ORG/Empl_Class/Reg_Temp combos to
a more user friendly format.
COMMENTS:
SAMPLE EXECUTION: SELECT dbo.fn_StaffTypeMap ('16011')
*/
CREATE FUNCTION [dbo].[fn_StaffTypeMap]
(
@EmplID CHAR(5)
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @MappedValue AS VARCHAR(20)
SELECT @MappedValue = (
SELECT CASE
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Contractor'
WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'V' AND REG_TEMP = 'T' THEN 'Vendor'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'R' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'I' AND REG_TEMP = 'T' THEN 'Employee'
WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Employee'
ELSE 'ERROR'
END
FROM PSPerson
WHERE EmplID = @EmplID
)
RETURN (@MappedValue)
END
June 17, 2009 at 4:13 pm
Problem with scalar functions used this way - where you pass a column as a parameter - is that they are not inlined by the query compiler. This means they are executed for each row and this is the main performance issue.
There is a trick you can do - instead of scalar function you can write a table valued function that returns 1 row. Query optimizer is then wise enough to choose different execution plan which allows to avoid the performance hit.
So the function would look like this:
CREATE FUNCTION [dbo].[fn_StaffTypeMap]
(
@EmplID CHAR(5)
)
RETURNS TABLE
AS
RETURN SELECT convert(varchar(20), [your CASE statement]) as res;
GO
HTH
Piotr
...and your only reply is slàinte mhath
June 17, 2009 at 7:22 pm
Piotr is quite correct that the function is executed for each row and the start-up and shutdown cost of each function execution is very expensive including such processing as memory allocation and deallocation.
If the objective is not to hardcode the mapping in all of the SQL, a better solution is to have an auxiliary table:
CREATE TABLE StaffTypeMap
( PER_ORG
, EMPL_CLASS
, REG_TEMP
, StaffType
, PRIMARY KEY ( PER_ORG , EMPL_CLASS, REG_TEMP )
)
THEN your SQL is on the pattern of:
SELECT COALESCE(StaffTypeMap.StaffType, 'UNKNOWN')
FROM X
LEFT OUTER JOIN
StaffTypeMap
ON StaffTypeMap.PER_ORG= X.PER_ORG
AND StaffTypeMap.EMPL_CLASS = X.EMPL_CLASS
AND StaffTypeMap.REG_TEMP= X.REG_TEMP
SQL = Scarcely Qualifies as a Language
June 17, 2009 at 10:36 pm
Yet other options include:
1. Creating a view (possibly indexed) over PSPerson and adding a column which contains the CASE.
2. Adding a computed column for the CASE to the PSPerson table, and preferably persisting it.
If you wish, you can encapsulate the CASE (without the data access) in a function and use that in the persisted computed column definition. An example of this idea follows:
USE tempdb
GO
CREATE FUNCTION dbo.f (@p1 INT, @p2 INT)
RETURNS INT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN RETURN CASE WHEN @p1 = 1 AND @p2 = 1 THEN 0 WHEN @p1 = 1 AND @p2 = 2 THEN 1 WHEN @p1 = 2 AND @p2 = 1 THEN 2 ELSE 3 END END
GO
CREATE TABLE dbo.Demo (A INT NOT NULL, B INT NOT NULL, C AS dbo.f (A, B) PERSISTED);
GO
INSERTdbo.Demo (A, B)
SELECTTOP (25000)
CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1),
CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1)
FROMmaster.sys.columns c1
CROSS
JOINmaster.sys.columns c2
GO
SELECT*
FROMdbo.Demo;
GO
DROP TABLE dbo.Demo;
DROP FUNCTION dbo.f;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 18, 2009 at 10:47 am
Thanks all. Much appreciated!
June 20, 2009 at 1:57 pm
Paul,
I've kind of forgotten what the advantage to SCHEMABINGING is for a scalar UDF. I think I read it somewhere, but I can't find it in my SQL saved SQL docs. What does this do exactly?
Todd Fifield
June 20, 2009 at 8:28 pm
tfifield (6/20/2009)
Paul,I've kind of forgotten what the advantage to SCHEMABINGING is for a scalar UDF. I think I read it somewhere, but I can't find it in my SQL saved SQL docs. What does this do exactly?
Todd Fifield
Take a look at this: http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 20, 2009 at 9:12 pm
Nice article and great explanation in the article... too bad they didn't actually test it for performance... 😉 Both of the following UDF's render identical execution plans and they both take turns winning on my desktop. That also means that compared to the inline code, they both make some nasty sucking sounds. 😛 I got the function code below from the article.
[font="Courier New"]
CREATE FUNCTION dbo.ComputeNumSB(@i INT)
RETURNS INT
WITH SCHEMABINDING
BEGIN
RETURN @i * 2 + 50
END
GO
--===== Create a scalar function without SchemaBinding
CREATE FUNCTION dbo.ComputeNum(@i INT)
RETURNS INT
BEGIN
RETURN @i * 2 + 50
END
GO
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
GO
--===== Test more than once... the functions take turns winning.
-- The inline code ALWAYS blows the functions away.
DECLARE @BitBucket INT
DECLARE @TestSize INT
SELECT @TestSize = 100000
SET STATISTICS TIME ON
SELECT @BitBucket = dbo.ComputeNumSB(t.N)
FROM dbo.Tally t
WHERE t.N <= @TestSize
SELECT @BitBucket = dbo.ComputeNum(t.N)
FROM dbo.Tally t
WHERE t.N <= @TestSize
SELECT @BitBucket = t.N * 2 + 50
FROM dbo.Tally t
WHERE t.N <= @TestSize
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
GO 6
For those that don't know what a Tally table is, now would be a good time to learn. Please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
I agree with most of the other's said here... a UDF is probably not the way to go here. An aux table or a bit of inline code is the way to go. Here's the run results from the code above...
[font="Courier New"]Beginning execution loop
SQL Server Execution Times:
CPU time = 8625 ms, elapsed time = 16175 ms.
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 16226 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 78 ms.
================================================================================
SQL Server Execution Times:
CPU time = 8704 ms, elapsed time = 16103 ms.
SQL Server Execution Times:
CPU time = 8875 ms, elapsed time = 16358 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 76 ms.
================================================================================
SQL Server Execution Times:
CPU time = 8203 ms, elapsed time = 16051 ms.
SQL Server Execution Times:
CPU time = 8406 ms, elapsed time = 16075 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 94 ms.
================================================================================
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 15994 ms.
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 19858 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 81 ms.
================================================================================
SQL Server Execution Times:
CPU time = 8203 ms, elapsed time = 17882 ms.
SQL Server Execution Times:
CPU time = 8844 ms, elapsed time = 19245 ms.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 86 ms.
================================================================================
SQL Server Execution Times:
CPU time = 8297 ms, elapsed time = 16888 ms.
SQL Server Execution Times:
CPU time = 8656 ms, elapsed time = 18983 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 79 ms.
================================================================================
Batch execution completed 6 times.
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2009 at 9:34 pm
Jeff Moden (6/20/2009)
Nice article and great explanation in the article... too bad they didn't actually test it for performance... 😉 Both of the following UDF's render identical execution plans and they both take turns winning on my desktop. That also means that compared to the inline code, they both make some nasty sucking sounds. 😛 I got the function code below from the article.[/font]
Hey Jeff,
Short answer: Try it with an UPDATE statement as the article does 😉 :w00t:
Long answer in progress 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 20, 2009 at 10:30 pm
Long answer:
SCHEMABINDING performs no magic with simple integer math - so we don't expect a difference with the SELECTs in your example code.
Where it does make a huge difference, is when the query optimizer (QO) must consider whether the function is deterministic or not. The QO has many fewer options when it encounters a non-deterministic function.
When marked as schema-bound, the engine analyzes the content of the function to decide on whether it is deterministic or not.
A scalar function which is not marked with SCHEMABINDING it is marked as doing data access - without analyzing the content of the function at all. This not only prevents it from be evaluated with deterministic optimizations, it also means that things like halloween protection become necessary in UPDATE statements, where they would not be otherwise. (This is the specific example in the article I linked to.)
The script below illustrates the dramatic differences that often appear.
[font="Courier New"]USE tempdb;
GO
-- Conditional drops
IFOBJECT_ID(N'dbo.ComputeNum', N'FN') IS NOT NULL DROP FUNCTION dbo.ComputeNum;
IFOBJECT_ID(N'dbo.ComputeNumSB', N'FN') IS NOT NULL DROP FUNCTION dbo.ComputeNumSB;
IFOBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL DROP TABLE dbo.TestTable;
GO
-- Schema-bound (will be evaluated for determinism)
CREATE FUNCTION dbo.ComputeNumSB (@i INT) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN @i * 2 + 50 END;
GO
-- Not schema-bound, will be marked as doing data access
CREATE FUNCTION dbo.ComputeNum (@i INT) RETURNS INT AS BEGIN RETURN @i * 2 + 50 END;
GO
-- Create a test table which we can update
CREATE TABLE dbo.TestTable (A INT IDENTITY PRIMARY KEY, B INT NOT NULL)
GO
INSERTdbo.TestTable (B)
SELECTTOP (100000)
CONVERT(INT, 123456 * RAND(CHECKSUM(NEWID())))
FROMmaster.sys.columns C1
CROSS
JOINmaster.sys.columns C2
GO
-- Check the properties of the functions
/*
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNum', N'FN'), 'IsDeterministic')-- 0 = No
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNum', N'FN'), 'SystemDataAccess')-- 1 = Yes
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNum', N'FN'), 'UserDataAccess')-- 1 = Yes
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNumSB', N'FN'), 'IsDeterministic')-- 1 = Yes
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNumSB', N'FN'), 'SystemDataAccess')-- 0 = No
SELECTOBJECTPROPERTYEX(OBJECT_ID(N'dbo.ComputeNumSB', N'FN'), 'UserDataAccess')-- 0 = No
*/
GO
--
-- TESTS
--
DECLARE @TestSize INT;
SELECT @TestSize = 100000;
SET STATISTICS IO ON;
-- Non-schema-bound version (compare the plans!)
BEGIN TRANSACTION
UPDATEdbo.TestTable
SETB = dbo.ComputeNum(B)
WHEREA BETWEEN 1 AND @TestSize
ROLLBACK
--Table 'TestTable'. Scan count 1, logical reads 200213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 1, logical reads 201856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Schema-bound
BEGIN TRANSACTION
UPDATEdbo.TestTable
SETB = dbo.ComputeNumSB(B)
WHEREA BETWEEN 1 AND @TestSize
ROLLBACK
--Table 'TestTable'. Scan count 1, logical reads 213, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SET STATISTICS IO OFF;
GO
IFOBJECT_ID(N'dbo.ComputeNum', N'FN') IS NOT NULL DROP FUNCTION dbo.ComputeNum;
IFOBJECT_ID(N'dbo.ComputeNumSB', N'FN') IS NOT NULL DROP FUNCTION dbo.ComputeNumSB;
IFOBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL DROP TABLE dbo.TestTable;
[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 20, 2009 at 10:58 pm
Just to add: I don't want to give the impression the SCHEMABINDING only produces benefits in UPDATE plans - it doesn't.
The key point is that the QO can apply many more optimizations to deterministic functions - that may be important in more complex SELECT statements, where the value can be evaluated once and a table-spool used to 'remember' the result of the function for later executions with the same parameters.
The caveat there is that the QO doesn't make any attempt to estimate the true cost of a scalar function - it uses a fixed cost. This may mean that it fails to apply worthwhile optimizations for relatively expensive functions.
Another advantage of SCHEMABINDING is the ability to persist and index computed columns:
ALTER TABLE dbo.TestTable ADD C AS dbo.ComputeNum(B) PERSISTED -- Fails
ALTER TABLE dbo.TestTable ADD C AS dbo.ComputeNumSB(B) PERSISTED -- Succeeds
The final 'win' is that even a non-persisted computed column based on a deterministic function can have statistics created on it:
ALTER TABLE dbo.TestTable ADD D AS dbo.ComputeNum(B) -- Not persisted, so succeeds
CREATE STATISTICS s on dbo.TestTable (C) -- Succeeds
CREATE STATISTICS s on dbo.TestTable (D) -- Fails
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 1:10 am
Heh... well done, Paul. You should have written the article. Thanks for taking the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2009 at 3:32 am
Jeff Moden (6/21/2009)
Heh... well done, Paul. You should have written the article. Thanks for taking the time.
Wow- coming from you that's awesome!
And you're welcome, of course - I enjoyed doing it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 10:28 am
Paul,
Thanks for the full explanation. It makes complete sense now.
Jeff,
Thanks for your time and full test. You do a fine job of myth busting. I've used your reference on SELECT INTO #Temp not actually locking sysobjects in TempDB several times to resolve arguments with DBA's
Todd Fifield
June 21, 2009 at 12:00 pm
Paul White (6/21/2009)
Jeff Moden (6/21/2009)
Heh... well done, Paul. You should have written the article. Thanks for taking the time.Wow- coming from you that's awesome!
And you're welcome, of course - I enjoyed doing it.
Paul
What I really enjoy is the fact that you laid it out all so nicely. Not my intent to pull a code review on you but I have to say something when I see some quality code especially when it proves such a valuable point. The code is nicely formatted (even though the forum slammed all your stuff to the left), well documented, and follows most best practices like using 2 part names for objects and using a consistent format. Adding to that, you actually tested for something more than a small handful of rows, included the results in your post, and added a complete additional explanation... this is some awsome stuff. I'd recommend that you actually DO turn it into an article for SSC especially since it trashes one side of a myth and proves the other.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply