Re: SQL SERVER 2005: User-defined function overhead

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


There is overhead with the function calling, which doesn't exist for system functions and expressions, quite simple. That is why it is a best practice to be very careful using UDF's which operates over many rows (and it is even worse if the UDF does data access!!!).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"AA2e72E" <AA2e72E@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1CC16E71-616F-4E93-8E1F-EF72BC992FD8@xxxxxxxxxxxxxxxx

I have this function:

CREATE FUNCTION [dbo].[fnInRange]
--
-- This function takes 3 arguments
-- It returns true when arg[1] is between arg[2] and arg[3]
-- arg[2] may be less than/greater than arg[3]
--
-- unlike BETWEEN MinValue AND MaxValue, this function copes with
(MinValue,MaxValue) or (MaxValue,MinValue)
--
(@thisValue as Decimal(18,15), @firstValue As decimal(18,15),@secondValue As
decimal(18,15))

RETURNS int

AS
BEGIN
return case
abs(sign(@firstValue-@thisValue) + sign(@secondValue-@thisValue))
when 2 then 0 else 1 end
END

This query, using the udf:

select top 100000 *
from lrftest
where dbo.fnInRange(latitude,78.15,78.97)=1

takes over 10 times longer than

select top 100000 *
from lrftest -- tblaisliverefregionzoneedges
where 1= case abs(sign(78.15-latitude) + sign(78.97-latitude)) when 2 then 0
else 1 end

Any one know why this overhead exists?

.

Relevant Pages

  • Re: Math headache
    ... Kind regards, ... | In the Module where your Overhead function resides, ... I wrote the UDF below to use in my ... ||> | Dim LowResult As Double, HighResult As Double, NowResult As Double ...
    (microsoft.public.excel.programming)
  • Re: Replacing IIF with a UDF (not CASE)
    ... > It would be fine to simply encapsulate a CASE statement in a UDF. ... > Would even doing that cause a big overhead? ... Regardless of the ...
    (microsoft.public.sqlserver.programming)
  • Re: Math headache
    ... determine the overhead from the subtotal rather than the estimate. ... Here is UDF: ... | Optional MaxDiffPerc) As Double ... | Dim LowResult As Double, HighResult As Double, NowResult As Double ...
    (microsoft.public.excel.programming)
  • Re: Replacing IIF with a UDF (not CASE)
    ... It would be fine to simply encapsulate a CASE statement in a UDF. ... Regardless of the overhead issue, ...
    (microsoft.public.sqlserver.programming)
  • Re: Math headache
    ... In the Module where your Overhead function resides, ... Kind regards, ... I wrote the UDF below to use in my ... |> | Dim LowResult As Double, HighResult As Double, NowResult As Double ...
    (microsoft.public.excel.programming)