April 7, 2010 at 7:15 pm
I was populating a Calendar table, and decided i wanted to add Lunar Phases as well, so i could find things like the following:
when is the next Friday the 13th that has a Full Moon?
when is the Halloween that has a Full Moon?
when is my next birthday that has a Full Moon?
when is the darkest(new moon) night in December?
I could not find a canned TSQL function, so I adapted the math off of a web site, and created both a TableValueFunction, and a Scalar Function, with both of them returning correct results. Sweet so far.
now, when i apply the TVF against my 100-year calendar table, it is much slower than the scalar function..it's affecting only 36,500 rows and maybe the tipping point where the TVF is faster is more, but I was really expecting it to outperform on this number of rows.
on my machine it takes 51 seconds for the TVF, and less than a second for the scalar function, all for the same 36500 rows.
someone with fresh eyes, please take a peek and tell me why my TVF might be so slow.
the sqlplan is attached, and here is my test code:
--drop table TallyCalendar
IF OBJECT_ID('TallyCalendar') IS NOT NULL
DROP TABLE dbo.TallyCalendar
GO
CREATE TABLE dbo.TallyCalendar (
TheDate DATETIME NOT NULL PRIMARY KEY,
[DayOfWeek] VARCHAR(50),
IsHoliday bit DEFAULT 0,
IsWorkHoliday bit DEFAULT 0,
HolidayName VARCHAR(100) )
DECLARE @NumberOfYears INT
SET @NumberOfYears = 50 --x years before and after todays date:
;WITH TallyNumbers AS
(
SELECT DATEADD( dd,(-365 * @NumberOfYears) + RW ,DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS N
FROM (
SELECT TOP (730 * @NumberOfYears)
row_number() OVER (ORDER BY sc1.id) AS RW
FROM MASTER.dbo.SysColumns sc1
CROSS JOIN MASTER.dbo.SysColumns sc2
) X
)
INSERT INTO dbo.TallyCalendar(TheDate,[DayOfWeek])
SELECT
TallyNumbers.N,
DATENAME(dw,TallyNumbers.N)
FROM TallyNumbers
GO
IF OBJECT_ID('dbo.pr_LunarPhaseTVF') IS NOT NULL
DROP FUNCTION dbo.pr_LunarPhaseTVF
GO
--adapted from
--http://home.att.net/~srschmitt/zenosamples/zs_lunarphasecalc.html
CREATE FUNCTION dbo.pr_LunarPhaseTVF(@TheDate datetime)
RETURNS @results table (results varchar(30))
WITH SCHEMABINDING
AS
BEGIN
declare
@pi decimal (20,19),
@ip decimal (20,16),
@ag decimal (20,16),
@year int,
@month int,
@day int,
@yy int,
@mm int,
@k1 int,
@k2 int,
@k3 int,
@JD int
--initialize our vars
SELECT @pi = 3.1415926535897932385,
@year = YEAR(@TheDate),
@month = MONTH(@TheDate),
@day = DAY(@TheDate)
-- % calculate the Julian date at 12h UT
SELECT @yy = @year - floor( ( 12 - @month ) / 10 ),
@mm = CASE
WHEN (@month + 9) >= 12
THEN (@month + 9) - 12
ELSE @month + 9
END
SELECT @k1 = floor( 365.25 * ( @yy + 4712 ) ),
@k2 = floor( 30.6 * @mm + 0.5 ),
@k3 = floor( floor( ( @yy / 100 ) + 49 ) * 0.75 ) - 38
SELECT @JD = @k1 + @k2 + @day + 59 -- % for dates in Julian calendar
IF (@jd > 2299160)
SELECT @JD = @JD - @k3 --% for Gregorian calendar
--% calculate moon's age in days
SELECT @ip = (( @JD - 2451550.1 ) / 29.530588853) - (floor( ( @JD - 2451550.1 ) / 29.530588853 ))
SELECT @ag = @ip * 29.53
INSERT INTO @results(results)
SELECT CASE
WHEN @ag < 1.84566
THEN 'New Moon'
WHEN @ag < 5.53699
THEN 'Waxing crescent'
WHEN @ag < 9.22831
THEN 'First quarter'
WHEN @ag < 12.91963
THEN 'Waxing near full moon'
WHEN @ag < 16.61096
THEN 'Full Moon '
WHEN @ag < 20.30228
THEN 'Waning near full moon'
WHEN @ag < 23.99361
THEN 'Last quarter'
WHEN @ag < 27.68493
THEN 'Waning crescent'
ELSE 'New Moon'
END
RETURN --will return the defined table
END --function
GO
IF OBJECT_ID('dbo.pr_LunarPhase') IS NOT NULL
DROP FUNCTION dbo.pr_LunarPhase
GO
--adapted from
--http://home.att.net/~srschmitt/zenosamples/zs_lunarphasecalc.html
CREATE FUNCTION dbo.pr_LunarPhase(@TheDate datetime)
RETURNS varchar(30)
WITH SCHEMABINDING
AS
BEGIN
declare
@results varchar(30),
@pi decimal (20,19),
@ip decimal (20,16),
@ag decimal (20,16),
@year int,
@month int,
@day int,
@yy int,
@mm int,
@k1 int,
@k2 int,
@k3 int,
@JD int
--initialize our vars
SELECT @pi = 3.1415926535897932385,
@year = YEAR(@TheDate),
@month = MONTH(@TheDate),
@day = DAY(@TheDate)
-- % calculate the Julian date at 12h UT
SELECT @yy = @year - floor( ( 12 - @month ) / 10 ),
@mm = CASE
WHEN (@month + 9) >= 12
THEN (@month + 9) - 12
ELSE @month + 9
END
SELECT @k1 = floor( 365.25 * ( @yy + 4712 ) ),
@k2 = floor( 30.6 * @mm + 0.5 ),
@k3 = floor( floor( ( @yy / 100 ) + 49 ) * 0.75 ) - 38
SELECT @JD = @k1 + @k2 + @day + 59 -- % for dates in Julian calendar
IF (@jd > 2299160)
SELECT @JD = @JD - @k3 --% for Gregorian calendar
--% calculate moon's age in days
SELECT @ip = (( @JD - 2451550.1 ) / 29.530588853) - (floor( ( @JD - 2451550.1 ) / 29.530588853 ))
SELECT @ag = @ip * 29.53
SELECT @results = CASE
WHEN @ag < 1.84566
THEN 'New Moon'
WHEN @ag < 5.53699
THEN 'Waxing crescent'
WHEN @ag < 9.22831
THEN 'First quarter'
WHEN @ag < 12.91963
THEN 'Waxing near full moon'
WHEN @ag < 16.61096
THEN 'Full Moon '
WHEN @ag < 20.30228
THEN 'Waning near full moon'
WHEN @ag < 23.99361
THEN 'Last quarter'
WHEN @ag < 27.68493
THEN 'Waning crescent'
ELSE 'New Moon'
END
RETURN @results
END --function
GO
--all rows
select TallyCalendar.*, dbo.pr_LunarPhase(thedate)
from TallyCalendar
select * from TallyCalendar
cross apply dbo.pr_LunarPhaseTVF(thedate)
--all the future friday the 13ths that are also a full moon
select TallyCalendar.*, dbo.pr_LunarPhase(thedate)
from TallyCalendar
where DAY(thedate) = 13
and [DayOfWeek] = 'Friday'
and dbo.pr_LunarPhase(thedate) = 'Full Moon'
and TheDate > GETDATE()
select * from (
select * from TallyCalendar
cross apply dbo.pr_LunarPhaseTVF(thedate)
) X
where DAY(thedate) = 13
and [DayOfWeek]='Friday'
and results='Full Moon'
and TheDate > GETDATE()
Lowell
April 7, 2010 at 7:53 pm
Any chance you had the Actual Plan on when you were testing? I show the scalar as the fastest, but without the plan on none of them are that high.
Results with inserts into temp table instead of display:
------------------------ TVF ----------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4078 ms, elapsed time = 4250 ms.
(36500 row(s) affected)
------------------------ SCALAR ----------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 620 ms.
(36500 row(s) affected)
----------------------- CROSS APPLY -------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 4172 ms, elapsed time = 4293 ms.
(36500 row(s) affected)
The execution plan for all 3 on my machine(Desktop, not server) is attached.
April 7, 2010 at 7:59 pm
Seth yes i had actual plan on, but even with it off it's still ~36 seconds;
there's no space expansion or anything going on behind the scenes; i can run the whole script back to back mutiple times with identical results.
i had similar results with my other desktop, but posted the question from my home machine.
Lowell
April 7, 2010 at 8:09 pm
You probably need it to be an inline-TVF.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2010 at 8:28 pm
RBarryYoung (4/7/2010)
You probably need it to be an inline-TVF.
That's what my thought was as well, at least to cover the difference in 4s vs. 690ms. I'd heard that they were optimized differently, didn't realize 'differently' meant 'beat by a scalar' =).
April 8, 2010 at 5:27 am
Scalar T-SQL functions suck.
Multi-statement T-SQL functions suck more.
In-line T-SQL functions are awesome.
Multi-statement functions do have their uses - but only if the logic cannot be written in a single SELECT, and if multiple rows are returned, and if you can't write .NET code, and...well it's a long list.
Just calling a multi-statement function is as slow as calling a scalar, but constructing the table variable, inserting any generated rows into it, and joining the results back into the query plan makes a multi-statement function perform worse than a scalar for single-row result sets. If a multi-statement function can replace several calls to the scalar equivalent, it can be faster.
A SQLCLR scalar function would be faster than the T-SQL scalar function (always, and more so with increasing computation) but, if the logic can be written as a single SELECT, the in-line function beats all.
April 8, 2010 at 6:08 am
I'll admit it, I'm weak on inline TVFs; had to look it up;
i've saved a lot of Paul's recent code that was converting other's scalars to inline tvf's but my learning curve hasn't been met yet. I'll try to change it to an inline TVF today, as soon as i really understand the diff.
Lowell
April 8, 2010 at 7:41 am
Paul White NZ (4/8/2010)
Scalar T-SQL functions suck.Multi-statement T-SQL functions suck more.
In-line T-SQL functions are awesome.
Multi-statement functions do have their uses - but only if the logic cannot be written in a single SELECT, and if multiple rows are returned, and if you can't write .NET code, and...well it's a long list.
Just calling a multi-statement function is as slow as calling a scalar, but constructing the table variable, inserting any generated rows into it, and joining the results back into the query plan makes a multi-statement function perform worse than a scalar for single-row result sets. If a multi-statement function can replace several calls to the scalar equivalent, it can be faster.
A SQLCLR scalar function would be faster than the T-SQL scalar function (always, and more so with increasing computation) but, if the logic can be written as a single SELECT, the in-line function beats all.
Great Info Paul. Guess I should have done some research into MLTVF's before now. On the bright side, everywhere I'm using them they actually do return multiple rows (I think), so I might have avoided some of this.
As far as I know, the only criteria for an INLINE TVF vs a MLTVF is that it has to be all one statement. IE. Begins with WITH or SELECT and doesn't use anything like local variables etc. And you'd just use RETURNS TABLE instead of defining the fields.
April 8, 2010 at 7:49 am
Lowell (4/8/2010)
I'll admit it, I'm weak on inline TVFs; had to look it up;i've saved a lot of Paul's recent code that was converting other's scalars to inline tvf's but my learning curve hasn't been met yet. I'll try to change it to an inline TVF today, as soon as i really understand the diff.
I rewrote your MLTVF as an in-line TVF last night and was testing it against your scalar function and it was still slower, approx 3 seconds vs approx 1 second.
I was trying to improve it, but I haven't been able to speed it up. Also, I didn't bring it with me to work so I don't have it handy.
April 8, 2010 at 8:10 am
i just re-wrote it as well, but the math is quite not right; i'm basically using multiple CTE's to do the intermediate variables as columns, as soon as i have it i'll post it.
Lowell
April 8, 2010 at 8:21 am
Lowell (4/8/2010)
i just re-wrote it as well, but the math is quite not right; i'm basically using multiple CTE's to do the intermediate variables as columns, as soon as i have it i'll post it.
I had to change this:
SELECT @ip = (( @JD - 2451550.1 ) / 29.530588853) - (floor( ( @JD - 2451550.1 ) / 29.530588853 ))
to this:
SELECT @ip = (( @JD - 2451550.1 ) / 29.530588853) - cast((floor( ( @JD - 2451550.1 ) / 29.530588853 )) as decimal(20,16))
inside my derived table. If you can wait until I get home this evening, I'll post the code I have written so far.
April 8, 2010 at 10:54 pm
Garadin (4/8/2010)
As far as I know, the only criteria for an INLINE TVF vs a MLTVF is that it has to be all one statement. IE. Begins with WITH or SELECT and doesn't use anything like local variables etc. And you'd just use RETURNS TABLE instead of defining the fields.
That's as good an explanation as I can think of too.
April 8, 2010 at 10:56 pm
Lynn Pettis (4/8/2010)
I rewrote your MLTVF as an in-line TVF last night and was testing it against your scalar function and it was still slower, approx 3 seconds vs approx 1 second.
Wow - what did you do??? :laugh:
Can't wait to see the code.
April 9, 2010 at 2:29 am
Paul White NZ (4/8/2010)
Wow - what did you do??? :laugh:
Can't wait to see the code.
As crazy as it sounds, Im seeing the same as Lynn at the moment.
This is running contrary to all that i hold dear 😉
Ive added columns Year , month, and day to the calendar table as well.
alter table TallyCalendar
add Year integer
go
alter table TallyCalendar
add Month integer
go
alter table TallyCalendar
add Day integer
go
update TallyCalendar
set Year = YEAR(theDATE),
month = MONTH(TheDate),
Day = DAY(thedate)
go
Drop FUNCTION dbo.pr_LunarPhaseITVF
go
CREATE FUNCTION dbo.pr_LunarPhaseITVF(@Year integer,@Month integer,@Day integer)
returns table
as
return(
--initialize our vars
with cteInitVars
as
(
SELECT year = @YEAR,
month = @MONTH,
day = @DAY
),
cteJulian
as
(
Select year,month,day,
yy = year - floor( ( 12 - month ) / 10 ),
mm = CASE
WHEN (month + 9) >= 12
THEN (month + 9) - 12
ELSE month + 9
END
from cteInitVars
)
,
cteCalc
as
(
Select year,month,day,
yy,mm,
k1 = floor( 365.25 * ( yy + 4712 ) ),
k2 = floor( 30.6 * mm + 0.5 ),
k3 = floor( floor( ( yy / 100 ) + 49 ) * 0.75 ) - 38
from cteJulian
)
,
cteCalc2
as
(
Select pi=0,year,month,day,
yy,mm,
k1,k2 ,k3,
jd = k1 + k2 + day + 59 -- % for dates in Julian calendar
from cteCalc
),
cteCalc3
as
(
Select pi,year,month,day,
yy,mm,
k1,k2 ,k3,
jd = case when jd > 2299160 then jd - k3 end --% for Gregorian calendar
from cteCalc2
),
cteCalc4
as
(
Select pi,year,month,day,
yy,mm,
k1,k2 ,k3,
jd,
ip = (( jd - 2451550.1 ) / 29.530588853) - cast((floor( ( jd - 2451550.1 ) / 29.530588853 )) as decimal(20,16))
from cteCalc3
),
cteCalc5
as
(
Select pi,year,month,day,
yy,mm,
k1,k2 ,k3,
jd,
ip, ag = ip * 29.53
from cteCalc4
)/*,
ctePhases
as
(
Select 1.84566 as Ltag ,'New Moon' as Phase union all
select 5.53699 ,'Waxing crescent' union all
select 9.22831 , 'First quarter' union all
select 12.91963 ,'Waxing near full moon'union all
select 16.61096,'Full Moon 'union all
select 20.30228 , 'Waning near full moon'union all
select 23.99361 ,'Last quarter'union all
select 27.68493 , 'Waning crescent'union all
select 50.0000 , 'New Moon'
)
Select top 1 Phase
from cteCalc5
join ctePhases
on cteCalc5.ag < ctePhases.ltag
order by cteCalc5.ag*/
SELECT Phase = CASE
WHEN ag < 1.84566
THEN 'New Moon'
WHEN ag < 5.53699
THEN 'Waxing crescent'
WHEN ag < 9.22831
THEN 'First quarter'
WHEN ag < 12.91963
THEN 'Waxing near full moon'
WHEN ag < 16.61096
THEN 'Full Moon '
WHEN ag < 20.30228
THEN 'Waning near full moon'
WHEN ag < 23.99361
THEN 'Last quarter'
WHEN ag < 27.68493
THEN 'Waning crescent'
ELSE 'New Moon'
END
from cteCalc5
)
go
select TallyCalendar.*, dbo.pr_LunarPhase(thedate) -- 796 ms
from TallyCalendar
select * from TallyCalendar
cross apply dbo.pr_LunarPhaseITVF(YEAR,MONTH,day) -- 1876 ms
Very curious about this .....
April 9, 2010 at 2:40 am
Hmmm
Removing the Ip calculation and just setting it to zero , brings the run time crashing down to 600 - 700 ms..
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply