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

Christian Ulrich (christian@xxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: help with asp/sql convert data to date for comparison
    ... It must be MS SQL Server ... columns and store proper datetime values in a single column. ... Adjust that until you are creating proper datetimes. ... You should see data in the proper format. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Date: Can you have one that is just mm/dd or mm/yyyy etc?
    ... If so, they are right, just display it with less resolution might be your ticket. ... they were thinking of there was mixed resolutions in one DATETIME column. ... One thing to watch is to NOT take my suggestion and print out everything ... determine if a day exists in it, and build the SQL accordingly. ...
    (comp.lang.java.programmer)
  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ... The above relies on the Jet implementation to coerce a DATETIME to ...
    (microsoft.public.access.queries)
  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ... The above relies on the Jet implementation to coerce a DATETIME to ...
    (microsoft.public.access.queries)
  • Re: Query Question for the Gurus
    ... datetime as an input parameter and returns the YYYYMMDDHHMM as a string ... select dOctets, myfunc, count ... I'm away from SQL or I could write a small test for you.... ... you could also do datefunctions which return a datetime datatype (rounded to ...
    (microsoft.public.sqlserver.programming)