Speed up UDF

[ujjc001]

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

[Mike C#]

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" <uj*****@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

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

[mmarovic]

I allways recommend returning raw data by sql and doing formatting on
the client side.

[Erland Sommarskog]

ujjc001 (uj*****@gmail.com) writes:

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.

Scalar UDFs can incur a large overhead in SQL 2000, and a lot of this
lies in the overhead for the call itself. So you are better off without
the function and using inline code.

In SQL 2005 you could write a UDF like this in C# or VB .Net, and you
could very well get performance than inline SQL. Overall, the overhead
cost for a call to a scalar UDF is lower in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ujjc001]

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" <uj*****@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

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

[Erland Sommarskog]

ujjc001 (uj*****@gmail.com) writes:

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.....

Yeah, I asked myself he same thing. In SQL 2005 you could do it if you
use the CROSS APPLY operator, but in SQL 2000 there is not a way, as
far as I can see. Maybe Mike C# has some more tricks up his sleeve?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mike C#]

Nope, just wrapping it in another scalar UDF LOL. I misread it and didn't
realize he was passing them thar columns in.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

ujjc001 (uj*****@gmail.com) writes:

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.....

Yeah, I asked myself he same thing. In SQL 2005 you could do it if you
use the CROSS APPLY operator, but in SQL 2000 there is not a way, as
far as I can see. Maybe Mike C# has some more tricks up his sleeve?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Stu]

Another alternative would be to modify a calendar table in such a way
as to return the values you want. You could then join on the calendar
table (using the date value as a lookup) and return the start day of
the week or month as needed.

Stu
ujjc001 wrote:

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

[ujjc001]

for some reason that sounds painful. Well I went with the simple case
statement inline, which works nicely just takes up a lot more room in
the sp. Another note, I did need the cast and converts to get my date
in a nice format for reporting purposes. I don't recally my exact
reasoning why, but it would have been much more difficult in crystal to
format my report the way I needed by passing in the raw data or for
that mater, anything w/ time on the end. The cast converts remove the
time too.
Thanks for the help.
Jeff
Stu wrote:

Another alternative would be to modify a calendar table in such a way
as to return the values you want. You could then join on the calendar
table (using the date value as a lookup) and return the start day of
the week or month as needed.

Stu
ujjc001 wrote:

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

[Stu]

Actually a calendar table is pretty simple to use, and very effecient
for situations like these. If you haven't used one before, it's very
simple to set up. Check out http://www.aspfaq.com/show.asp?id=2519

ujjc001 wrote:

for some reason that sounds painful. Well I went with the simple case
statement inline, which works nicely just takes up a lot more room in
the sp. Another note, I did need the cast and converts to get my date
in a nice format for reporting purposes. I don't recally my exact
reasoning why, but it would have been much more difficult in crystal to
format my report the way I needed by passing in the raw data or for
that mater, anything w/ time on the end. The cast converts remove the
time too.
Thanks for the help.
Jeff
Stu wrote:

Another alternative would be to modify a calendar table in such a way
as to return the values you want. You could then join on the calendar
table (using the date value as a lookup) and return the start day of
the week or month as needed.

Stu
ujjc001 wrote:

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