Wrapping T-SQL in Function and it gets very slow.

[Christian Ulrich]

Hi,

I have a "funny" problem that does not make sense to me.

I have a SELECT statement that manipulate a datetime :

SELECT COUNT(ID) AS Amount, CAST(ROUND(CAST (DischargeEvent Time AS
float), 0, 1) AS datetime) AS TimeValue FROM tblItemData WHERE
DischargeEventT ime between '2007-02-02' and '2007-10-02'
GROUP BY CAST(ROUND(CAST (DischargeEvent Time AS float), 0, 1) AS datetime)

Then I create:

CREATE FUNCTION [dbo].[RoundDateTimeTo Date]
(
@DateValue AS datetime
)
RETURNS datetime
AS
BEGIN
RETURN CAST(ROUND(CAST (@DateValue AS float), 0, 1) AS datetime)
END

So my SQL statement now can be:

SELECT COUNT(*) AS Amount, dbo.RoundDateTi meToDate(Discha rgeEventTime)
AS TimeValue FROM tblItemData WHERE DischargeEventT ime between
'2007-02-02' and '2007-10-02'
GROUP BY dbo.RoundDateTi meToDate(Discha rgeEventTime)

But this query takes 6 times longer than the first!

Why does "wrapping" SQL in a function cost so much?!?

Hope any body can explain this, and hopefully give a solution :-)

Best regards,
Christian - Denmark