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(DischargeEventTime AS
float), 0, 1) AS datetime) AS TimeValue FROM tblItemData WHERE
DischargeEventTime between '2007-02-02' and '2007-10-02'
GROUP BY CAST(ROUND(CAST(DischargeEventTime AS float), 0, 1) AS datetime)

Then I create:

CREATE FUNCTION [dbo].[RoundDateTimeToDate]
(
@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.RoundDateTimeToDate(DischargeEventTime)
AS TimeValue FROM tblItemData WHERE DischargeEventTime between
'2007-02-02' and '2007-10-02'
GROUP BY dbo.RoundDateTimeToDate(DischargeEventTime)

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