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

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

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

Christian Ulrich (christian@ulrichs.dk) writes:
> 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?!?

I assume that you use SQL 2000? The overhead for calling scalar UDFs is
considerable in SQL 2000. This is better in SQL 2005, but note that you
still should be careful with UDFs that perform data access.

In any case, rather than using a function, you can use a derived table:

SELECT COUNT(*), TimeValue
FROM (SELECT convert(char(8), DischargeEventTime, 112)
FROM tblItemData
WHERE DischargeEventTime BETWEEN '20070202' AND '20071002') AS c
GROUP BY TimeValue

Logically, a derived table is a temp table within the query, but the actual
computation order is often different, as the optimizer considers the query
as a whole.

Note also the format of the dates. Don't use YYYY-MM-DD, as this format
is subject to different interpretation depending on language and datetime
settings.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

Erland Sommarskog wrote:

>
> I assume that you use SQL 2000? The overhead for calling scalar UDFs is
> considerable in SQL 2000. This is better in SQL 2005, but note that you
> still should be careful with UDFs that perform data access.
>

No actualy it is 2005.

> In any case, rather than using a function, you can use a derived table:
>
> SELECT COUNT(*), TimeValue
> FROM (SELECT convert(char(8), DischargeEventTime, 112)
> FROM tblItemData
> WHERE DischargeEventTime BETWEEN '20070202' AND '20071002') AS c
> GROUP BY TimeValue
>
> Logically, a derived table is a temp table within the query, but the actual
> computation order is often different, as the optimizer considers the query
> as a whole.
>
Lession learned :-)

> Note also the format of the dates. Don't use YYYY-MM-DD, as this format
> is subject to different interpretation depending on language and datetime
> settings.

Yes dates are the problem child.


Thanks for your help and reply.

Best regards,
Christian