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

Question

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.

Thanks for any advice

John

---

John

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Hi,

any other thought how to tackle this?

Thanks,

John

---

John

Answer 7

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

Answer 8

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

Answer 9

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

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 10

Hi,

can I do this without code modification?

Thanks,

John

---

John

Answer 11

You may try like below:

EXECUTE <yourprocname> WITH RECOMPILE

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 12

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

Answer 13

 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;

Answer 14

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

Answer 15

Hi,

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

Thanks again,

John

---

John