Calling a scalar-valued function two times in one query takes longer than its invidual execution time

# Calling a scalar-valued function two times in one query takes longer than its invidual execution time

• Tuesday, November 20, 2012 12:56 AM

HI,

when I run

SET @YTDSick = dbo.MFN_GetYTDTime(@tsUID,GUID1) it takes about 1 second, when I run

SET @YTDVacation = dbo.MFN_GetYTDTime(@tsUID,GUID2) it takes also about 1 second,

however it I call both scalar-value function right after another in one query the entire execution time is about 30 seconds.

What can be the cause? What can I do to troubleshoot this? I cannot change the code, but can I can for example create a plan guide.

John

John

• Edited by Tuesday, November 20, 2012 9:26 AM
•

### All Replies

• Tuesday, November 20, 2012 1:23 AM
Moderator

Can you post the definition of the function? Perhaps you're dealing with the parameter's sniffing problem.

Also, is it possible to return both values at once by calling a modification of this function? This will be the best solution.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, November 20, 2012 1:29 AM

Hi,

yes, of course. Here it is:

ALTER FUNCTION [dbo].[MFN_GetYTDTime](
@tsUID        UID,
@classUID     UID
) RETURNS WORK
AS
BEGIN
DECLARE @YTDValue WORK
DECLARE @FractionValue WORK
DECLARE @year int
DECLARE @startDate DATETIME

SELECT @year = DATEPART (yyyy,P.WPRD_START_DATE), @startDate = P.WPRD_START_DATE
FROM MSP_TIMESHEETS T INNER JOIN MSP_WEB_TIME_PERIODS P ON (T.WPRD_UID = P.WPRD_UID)
WHERE T.TS_UID = @tsUID

SELECT @YTDValue = ISNULL (SUM(L.TS_LINE_ACT_SUM_VALUE),0)
FROM MSP_TIMESHEETS T1
INNER JOIN MSP_WEB_TIME_PERIODS P2 ON (P2.WPRD_START_DATE<=@startDate AND @year = DATEPART (yyyy, P2.WPRD_START_DATE) AND @year = DATEPART (yyyy, P2.WPRD_FINISH_DATE))
INNER JOIN MSP_TIMESHEETS T2 ON (T2.RES_UID = T1.RES_UID AND T2.WPRD_UID = P2.WPRD_UID)
INNER JOIN MSP_TIMESHEET_LINES L ON (L.TS_UID = T2.TS_UID AND L.TS_LINE_CLASS_UID=@classUID)
WHERE T1.TS_UID = @tsUID

SELECT @FractionValue = ISNULL (SUM(A.TS_ACT_VALUE),0)
FROM MSP_TIMESHEETS T1
INNER JOIN MSP_WEB_TIME_PERIODS P2 ON (P2.WPRD_START_DATE<=@startDate AND ((DATEPART (yyyy, P2.WPRD_START_DATE)<@year AND DATEPART (yyyy, P2.WPRD_FINISH_DATE) = @year) OR ((DATEPART (yyyy, P2.WPRD_START_DATE)=@year AND DATEPART (yyyy, P2.WPRD_FINISH_DATE) >@year))))
INNER JOIN MSP_TIMESHEETS T2 ON (T2.RES_UID = T1.RES_UID AND T2.WPRD_UID = P2.WPRD_UID)
INNER JOIN MSP_TIMESHEET_LINES L ON (L.TS_UID = T2.TS_UID AND L.TS_LINE_CLASS_UID=@classUID)
INNER JOIN MSP_TIMESHEET_ACTUALS A ON (L.TS_LINE_UID = A.TS_LINE_UID AND @year = DATEPART (yyyy,A.TS_ACT_START_DATE))
WHERE T1.TS_UID = @tsUID

RETURN @YTDValue + @FractionValue
END

I cannot change the code since its from Microsoft.

Thanks again

John

John

• Tuesday, November 20, 2012 2:06 AM
Moderator

This does not look like a MS code? Where exactly does it come from?

Can you add OPTION (RECOMPILE) to all queries used in this function and see if it helps?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, November 20, 2012 2:09 AM

Hi,

the code is from Project Server 2010. For that reason I cannot change it. Is there another way to influence this? With a plan guide, etc?

Thanks,

John

John

• Tuesday, November 20, 2012 2:20 AM
Moderator

I am not sure. I ran a quick search to see if we can add something akin WITH (RECOMPILE) for a function and I don't think we can.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, November 20, 2012 2:33 AM

Hi,

any other thought how to tackle this?

Thanks,

John

John

• Tuesday, November 20, 2012 4:11 AM

Damn, this is a mess! But then some of the worst code I ever had to repair was from MS Consulting Services. First of all, good SQL programmer do not use UDFs, user defined data types and other proprietary crap that ruins maintenance and portability. UIDs are also a non-relational way of creating fake pointer chains in T-SQL dialect. Or to do Kabbalah magic with the number God assigns to his creations:)

One of the characteristics of good functional programming is that there are no local variables. An SQL programmer would have used CTEs at worst in place of those fake COBOL declarations.

The usual idiom is a table of report periods and not all of that string handling to build dates on the fly; this code is exactly like what we did in COBOL! Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL and there is some talk of it becoming part of the standards.

Without DDL, I cannot give you much more than that.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

• Tuesday, November 20, 2012 9:29 AM

Hi,

I got this.

The UDF is working fine if called with two different parameters one after each other in separate queries.

But both called in another SP right after another results in bad performance.

So is there a chance to improve this without modifying the code?

Thanks,

John

John

• Tuesday, November 20, 2012 9:35 AM

If the Wrapper SP is your sp, you may try RECOMPILE option for the SP and try.

• Tuesday, November 20, 2012 10:25 AM

Hi,

can I do this without code modification?

Thanks,

John

John

• Tuesday, November 20, 2012 12:43 PM

You may try like below:

EXECUTE <yourprocname> WITH RECOMPILE

• Tuesday, November 20, 2012 6:13 PM

Hi,

as said I cannot change the code nor can I change the way how the procs are executed since its a product from Microsoft. Are there any other options like plan guide, etc.?

Thanks,

John

John

• Tuesday, November 20, 2012 6:29 PM

admit it sometimes I’m a little late to the party. Even though they’ve been around since SQL Server 2000, I haven’t had the pleasure of getting acquainted with User Defined Functions (UDFs) inside SQL Server. But now that I know about them, I can see how useful they can be. There are several kinds of UDFs, today let’s chat about the scalar types.

A scalar UDF returns one, and only one value from the function. You can pass in parameters, have it do everything from simple to complex calculations, then return a result. Let’s take a look at how one is coded, using the AdventureWorks database.

create function dbo.f_ContactFullName(@id as int)

returns varchar(101)

as

begin

declare @FullName varchar(101);

select @FullName = FirstName + ‘ ‘ + LastName

from Person.Contact

where ContactID = @id;

return @FullName;

end;

• Tuesday, November 20, 2012 7:16 PM
Moderator

Breanch,

Sorry, but how does your post help to the issue at hand?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Thursday, November 22, 2012 12:55 AM

Hi,

are there any other options like except refactoring, like plan guide, etc.?

Thanks again,

John

John