Simple scalar function slows down query

Question

Post reply

Simple scalar function slows down query

tomas-460706

Old Hand

Points: 305
More actions
May 27, 2010 at 9:27 am

#235704

This is my function (actually, any function gives the slow-down effect):
CREATE FUNCTION [dbo].[fn_sh_fixdate] (@pInputDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN CAST(CONVERT (VARCHAR, DATEADD(hh,3,@pInputDate), 112) AS DATETIME)
END
This is my DB: http://www.sendspace.com/file/zd9xnd (4,6MB zipped backup)
These are my selects (i've bolded the parts that are different. Other parts of selects are identical):
Slow select (takes ~12mins to execute):
SELECT dbo.fn_sh_fixdate(t.datetime1), CAST(kt.sql_variant1 AS NVARCHAR(255)),
CAST(kt.sql_variant1 AS NVARCHAR(255)),
kt.nvarchar9, f.nvarchar3, p.nvarchar3, ep.nvarchar4, t.float1
FROM imp.sh_alluserdata t
INNER JOIN imp.sh_alluserdata kt ON kt.tp_dirname = 'Lists/Katilai' AND t.int6 = kt.tp_id AND t.tp_iscurrent = kt.tp_iscurrent AND t.tp_deletetransactionid = kt.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata f ON f.tp_dirname = 'Lists/Kuras' AND t.int3 = f.tp_id AND t.tp_iscurrent = f.tp_iscurrent AND t.tp_deletetransactionid = f.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata p ON p.tp_dirname = 'Lists/Teral_tipai' AND t.int4 = p.tp_id AND t.tp_iscurrent = p.tp_iscurrent AND t.tp_deletetransactionid = p.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata ep ON ep.tp_dirname = 'Lists/Emisij parametrai' AND t.int7 = ep.tp_id AND t.tp_iscurrent = ep.tp_iscurrent AND t.tp_deletetransactionid = ep.tp_deletetransactionid
WHERE t.tp_dirname = 'Lists/APLEmisij skaiiavimaspastovs parametrai'
AND t.tp_iscurrent = 1 --naujausia versija
AND t.tp_deletetransactionid = 0; --neistrintas
Fast select (takes ~3 seconds):
SELECT CAST(CONVERT (VARCHAR, DATEADD(hh,3,t.datetime1), 112) AS DATETIME), CAST(kt.sql_variant1 AS NVARCHAR(255)),
kt.nvarchar9, f.nvarchar3, p.nvarchar3, ep.nvarchar4, t.float1
FROM imp.sh_alluserdata t
INNER JOIN imp.sh_alluserdata kt ON kt.tp_dirname = 'Lists/Katilai' AND t.int6 = kt.tp_id AND t.tp_iscurrent = kt.tp_iscurrent AND t.tp_deletetransactionid = kt.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata f ON f.tp_dirname = 'Lists/Kuras' AND t.int3 = f.tp_id AND t.tp_iscurrent = f.tp_iscurrent AND t.tp_deletetransactionid = f.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata p ON p.tp_dirname = 'Lists/Teral_tipai' AND t.int4 = p.tp_id AND t.tp_iscurrent = p.tp_iscurrent AND t.tp_deletetransactionid = p.tp_deletetransactionid
INNER JOIN imp.sh_alluserdata ep ON ep.tp_dirname = 'Lists/Emisij parametrai' AND t.int7 = ep.tp_id AND t.tp_iscurrent = ep.tp_iscurrent AND t.tp_deletetransactionid = ep.tp_deletetransactionid
WHERE t.tp_dirname = 'Lists/APLEmisij skaiiavimaspastovs parametrai'
AND t.tp_iscurrent = 1 --naujausia versija
AND t.tp_deletetransactionid = 0; --neistrintas
Could you help me to find out why scalar function slows down this select?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

ok tomas, a couple of things;

scalar functions can slow things down, as they introduce a hidden cursor/REBAR...million rows=million function calls.

I've changed your function froma scalar(called once per row) to a inline table value function...it should perform much better.

there must be a reason for this:

select CAST(CONVERT (VARCHAR, DATEADD(hh,3,getdate()), 112) AS DATETIME);

so anything from 9pm to midnight gets moved to the next day...that's desired, right?

you are adding three hours, then truncating the time off of the value...to speed that up, we want to avoid converting to varchar and back, so we stay with the same datatype.

CREATE FUNCTION [dbo].[fn_sh_fixdate] (@pInputDate DATETIME)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN (

select DATEADD(dd, DATEDIFF(dd,0,DATEADD(hh,3,@pInputDate)), 0) AS TheDate

)

and the way you call it is slightly different:

SELECT myAlias.TheDate , CAST(kt.sql_variant1 AS NVARCHAR(255)),

CAST(kt.sql_variant1 AS NVARCHAR(255)),

kt.nvarchar9, f.nvarchar3, p.nvarchar3, ep.nvarchar4, t.float1

FROM imp.sh_alluserdata t

INNER JOIN imp.sh_alluserdata kt ON kt.tp_dirname = 'Lists/Katilai' AND t.int6 = kt.tp_id AND t.tp_iscurrent = kt.tp_iscurrent AND t.tp_deletetransactionid = kt.tp_deletetransactionid

INNER JOIN imp.sh_alluserdata f ON f.tp_dirname = 'Lists/Kuras' AND t.int3 = f.tp_id AND t.tp_iscurrent = f.tp_iscurrent AND t.tp_deletetransactionid = f.tp_deletetransactionid

INNER JOIN imp.sh_alluserdata p ON p.tp_dirname = 'Lists/Teral_tipai' AND t.int4 = p.tp_id AND t.tp_iscurrent = p.tp_iscurrent AND t.tp_deletetransactionid = p.tp_deletetransactionid

INNER JOIN imp.sh_alluserdata ep ON ep.tp_dirname = 'Lists/Emisij parametrai' AND t.int7 = ep.tp_id AND t.tp_iscurrent = ep.tp_iscurrent AND t.tp_deletetransactionid = ep.tp_deletetransactionid

CROSS APPLY dbo.fn_sh_fixdate(t.datetime1) myAlias

WHERE t.tp_dirname = 'Lists/APLEmisij skaiiavimaspastovs parametrai'

AND t.tp_iscurrent = 1 --naujausia versija

AND t.tp_deletetransactionid = 0; --neistrintas

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tomas-460706 Old Hand Points: 305 More actions · Answer 2

Hi,

thank you for a fast reply. Yes, adding 3 hours and moving to next day (then truncating time) is intended result.

I've changed to inline table and everything works fast now. I was never aware that scalar functions have such performance issues, so strange...

Thanks again for helping me out.

Regards,

Tomas

Lowell SSC Guru Points: 323547 More actions · Answer 3

excelent tomas glad it's working for you;

performance wise, it it the same as the inline calculation you tested against?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Nice job, Lowell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is a good blog post (if I do say so myself) regarding performance between inline code, inline TVF's, and scalar functions:

Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions

WayneS SSC Guru Points: 95461 More actions · Answer 6

Lynn Pettis (5/28/2010)
Here is a good blog post (if I do say so myself) regarding performance between inline code, inline TVF's, and scalar functions:
Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions

Very informative Lynn... thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tomas-460706 Old Hand Points: 305 More actions · Answer 7

Lowell (5/28/2010)
excelent tomas glad it's working for you;
performance wise, it it the same as the inline calculation you tested against?

Hi,

yes, performance is the same (or very close - given my small amounts of data, i did not noticed any difference).

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

I am submitting a presentation for PASS this fall titled "Death by UDF". 😎 I will be VERY disappointed if it isn't accepted! I have a TON of examples of the myriad of ways UDFs can cause problems - and it goes well beyond crushing your server from a performance perspective!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service