|
|
Posted by Erland Sommarskog on 10/19/07 10:20
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|