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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
return @dtvar
END

[Mike C#]

Re: Speed up UDF

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@gmail. com> wrote in message
news:1151013715 .191251.45940@i 40g2000cwc.goog legroups.com...[color=blue]
> 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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
> return @dtvar
> END
>[/color]

[mmarovic]

Re: Speed up UDF

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

[Erland Sommarskog]

Re: Speed up UDF

ujjc001 (ujjc001@gmail. com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.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

[ujjc001]

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:[color=blue]
> 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@gmail. com> wrote in message
> news:1151013715 .191251.45940@i 40g2000cwc.goog legroups.com...[color=green]
> > 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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
> > return @dtvar
> > END
> >[/color][/color]

[Erland Sommarskog]

Re: Speed up UDF

ujjc001 (ujjc001@gmail. com) writes:[color=blue]
> 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.....[/color]

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, esquel@sommarsk og.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

[Mike C#]

Re: Speed up UDF

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" <esquel@sommars kog.se> wrote in message
news:Xns97EBAD1 53EDDAYazorman@ 127.0.0.1...[color=blue]
> ujjc001 (ujjc001@gmail. com) writes:[color=green]
>> 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.....[/color]
>
> 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, esquel@sommarsk og.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[/color]

[Stu]

Re: Speed up UDF

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:[color=blue]
> 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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
> return @dtvar
> END[/color]

[ujjc001]

Re: Speed up UDF

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:[color=blue]
> 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:[color=green]
> > 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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
> > return @dtvar
> > END[/color][/color]

[Stu]

Re: Speed up UDF

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:[color=blue]
> 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:[color=green]
> > 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:[color=darkred]
> > > 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(ch ar(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(CH AR(6), @input, 112) + '01' AS datetime)
> > > return @dtvar
> > > END[/color][/color][/color]