Functions

Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
PhilHow about giving us some specs first. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
Phil|||Phil,
It's my experience that scalar functions that do not access
any tables and that don't have long loops or recursion are not
going to run faster or slower if rewritten - the work they incur
is largely in the passing of parameters and returning of a result.
The only real optimization is to write the function inline. This
might be close:
convert(char(6),
dateadd(
month,
datediff(
month,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime),
cast(right(@.edition,6)+'01' as datetime)
)/@.period*@.period,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime)
),
112)
Steve Kass
Drew University
Phil wrote:

>Hi all,
>I have read of information about how to performance tune stored procedures,
>but is there any way of doing the same with functions or just some rules of
>thumb that I should adhere to, my code looks like this :-
>ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
>INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
>100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
>11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
>12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
>END
>
>Just wondering if there is a better way to write this or hints for
>performance.
>Thanks in advance
>Phil
>|||Hi Tom,
Sorry about that, getting a bit late should of really left this till the
morning where I was thinking a little better. Not really sure what you need
to know but I am using sql server 2000, the code is used for determining
contract start dates by passing in e.g. 200503 YYYY/MM dates, my code works
fine just wondering if there are any options that you can either turn on or
off that may affect how the funtion performs. The reason I ask is I know
that you can set things like SET NOCOUNT in stored procedures that does have
some affect on performance just wanting to know if there is anything similar
for funtions, I really should of made this more clear in my first post, sorr
y.
Thanks Phil
"Tom Moreau" wrote:

> How about giving us some specs first. ;-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
> Hi all,
> I have read of information about how to performance tune stored procedures
,
> but is there any way of doing the same with functions or just some rules o
f
> thumb that I should adhere to, my code looks like this :-
> ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
> INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
> 100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
> 11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
> 12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
> END
>
> Just wondering if there is a better way to write this or hints for
> performance.
> Thanks in advance
> Phil
>