Re: Speed up UDF

how would I use an inline udf in an existing stored procedure? I would
need to pass in a field such as table.date below :

SELECT
(
SELECT XDate FROM TimeFormat (table.Date, 'Month')
) AS formattedXDate,
table.Title,
table2.Title AS title2.....



Mike C# wrote:
How about an inline table-valued UDF? And getting rid of all those CASTs
and CONVERTs... After all you're inputting a DATETIME and returning a
DATETIME:

CREATE FUNCTION dbo.TimeFormat
(
@input DATETIME,
@groupformat VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT CASE @groupformat
WHEN 'DAY' THEN @input
WHEN 'WEEK' THEN DATEADD(day, 1 - DATEPART(dw, @input), @input)
WHEN 'MONTH' THEN DATEADD(day, -DATEPART(d, @input)+1, @input)
END AS [XDate]
)

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'DAY')

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'WEEK')

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'MONTH')

"ujjc001" <ujjc001@xxxxxxxxx> wrote in message
news:1151013715.191251.45940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello all-
Given the following UDF, in sql 2000 can it be sped up, complied or
anything of the like. A query returning 300,000 + rows times out when
ran through the udf, inline case statements returns the rows in 5
seconds.
Thanks!
Jeff

CREATE FUNCTION dbo.TimeFormat
(
@input datetime,
@groupformat varchar(20) --DAY, WEEK, MONTH
)

RETURNS datetime

AS

BEGIN
declare @dtvar as datetime

if @groupformat = 'DAY'
set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)
else if @groupformat = 'WEEK'
set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),
@input, 101)), CONVERT(char(10), @input, 101)) AS datetime)
else if @groupformat = 'MONTH'
set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)
return @dtvar
END


.

Relevant Pages

  • Re: Speed up UDF
    ... How about an inline table-valued UDF? ... After all you're inputting a DATETIME and returning a ... SELECT XDate ... @groupformat varchar--DAY, WEEK, MONTH ...
    (comp.databases.ms-sqlserver)
  • Speed up UDF
    ... A query returning 300,000 + rows times out when ... ran through the udf, inline case statements returns the rows in 5 ... RETURNS datetime ...
    (comp.databases.ms-sqlserver)
  • Re: Inserting with OpenXML - error converting type="xs:dateTime" to
    ... including a UDF, which I call from the Select statement of the OpenXML ... correctly converts the dateTime string to a SQL datetime value. ... copied the dateTime string from the XML dump file, and input it to the UDF. ...
    (microsoft.public.sqlserver.programming)
  • Re: User-defined functions in where clause
    ... UDF is used to row by row process similar how cursors work. ... avoid using udf with large set result. ... > create function ZeroTime(@Date datetime) ... > Run this query in query analyzer with the server trace option on and you ...
    (microsoft.public.sqlserver.programming)
  • Re: Speed up UDF
    ... Actually a calendar table is pretty simple to use, ... RETURNS datetime ... declare @dtvar as datetime ...
    (comp.databases.ms-sqlserver)