table valued function performance

Last Post 05 Mar 2009 04:28 PM by trans53. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
05 Mar 2009 12:08 PM


Hi guys,

i was doing some test with a simple select inside a function and wondering
why cpu time for inline table_valued function = 2000 where cpu time for table-valued function = 200. 10 times faster?
I thought that inline table_valued function usualy fast.
trans53
New Member
New Member

--
05 Mar 2009 04:28 PM
Sure, here is the code.Thank you

--inline

CREATE FUNCTION [dbo].[ufn_GetDeliveryDateFromShipDate] (@ShipDate DATETIME)
RETURNS TABLE
AS
RETURN
(
SELECT
CASE

WHEN (7=DATEPART(weekday,@ShipDate)) THEN DATEADD(DAY,2,@ShipDate)

ELSE DATEADD(DAY,1,@ShipDate)
END AS DeliveryDate
WHERE CASE

WHEN (7=DATEPART(weekday,@ShipDate)) THEN DATEADD(DAY,2,@ShipDate)

ELSE DATEADD(DAY,1,@ShipDate)
END
NOT IN (SELECT DeliveryDateToExclude FROM DeliveryDatesException (NOLOCK))
)


-- regular

CREATE FUNCTION [dbo].[ufn_GetDeliveryDateFromShipDate] (@ShipDate DATETIME)
RETURNS @DeliveryDateTable TABLE (DeliveryDate DATETIME NULL)
AS

BEGIN

DECLARE @DeliveryDate DateTime

SET @DeliveryDate = NULL

SELECT @DeliveryDate = CASE

WHEN (7=DATEPART(weekday,@ShipDate)) THEN DATEADD(DAY,2,@ShipDate)

ELSE DATEADD(DAY,1,@ShipDate)
END
WHERE CASE

WHEN (7=DATEPART(weekday,@ShipDate)) THEN DATEADD(DAY,2,@ShipDate)

ELSE DATEADD(DAY,1,@ShipDate)
END
NOT IN (SELECT DeliveryDateToExclude FROM DeliveryDatesException (NOLOCK) )

INSERT INTO @DeliveryDateTable
SELECT @DeliveryDate

RETURN

END
SQLUSA
New Member
New Member

--
14 Jun 2009 11:25 PM
Have you loop tested timing for like 100 runs?

Include

DBCC DROPCLEANBUFFERS

in the testing loop.

Kalman Toth, SQL Sever 2008 Training
http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy