Function to convert a datetime value into a harvest season year

Question

Function to convert a datetime value into a harvest season year

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

joe-584802 (6/14/2016)
Thanks for the improvement Lynne, I'm guessing table valued functions are more efficient than in-line scalar valued functions?

Inline table valued functions are more efficient. Multi-statement table valued functions are a recipe for disaster.

Read more about this in here: http://www.sqlservercentral.com/articles/T-SQL/91724/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

joe-584802 (6/14/2016)
Thanks for the improvement Lynne, I'm guessing table valued functions are more efficient than in-line scalar valued functions?

Yes and no. There are two types of table valued functions (TVF); Multi-statement (MTVF) and Inline (ITVF) table valued functions. The MTVF is type is frequently much worse than even a Scalar Function. You can read about the 3 types of functions at the following link...

https://msdn.microsoft.com/en-us/library/ms186755.aspx

There's also the concept of an ISF (Inline Scalar Function), which is really just a "flavor" of an ITVF. You can read about that at the following link...

http://www.sqlservercentral.com/articles/T-SQL/91724/

EDIT - Missed the fact that Luis posted something similar yesterday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

GSquared (6/14/2016)
I just tested a Calendar table version against the UDFs defined here.
Calendar table was consistently about 10X faster.

Can you post your test code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Jeff Moden (6/15/2016)
GSquared (6/14/2016)
I just tested a Calendar table version against the UDFs defined here.
Calendar table was consistently about 10X faster.
Can you post your test code?

Nope. The Post function keeps crashing when I try to post code to the forums for the last 2 days. Not sure why.

But it's really simple:

If you don't have a Calendar table, build one (lots of articles and samples available if you search for it)

Add an Int column called HarvestYear

Populate that column using whatever math you would use in the UDF

Join to the Calendar table using normal math for that (e.g.: "ON Calendar.BaseDate = CAST(MyTable.DateColumn AS DATE)")

Query however you like. I did a Select Into using a temp table for each. Tested 10k, 100k and 1M row performance. Repeated each test 10 times.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

nick.hinton SSC Eights! Points: 956 More actions · Answer 5

Lynn,

is there a reason why you put the iTVF access in a subquery? I use this form:

SELECT

isnull(HarvestYear,0) HarvestYear

FROM

[tblFuelFlow]

outer apply dbo.Get_HarvestYear([ArrivalDateTime]);

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

nick.hinton (6/15/2016)
Lynn,
is there a reason why you put the iTVF access in a subquery? I use this form:
SELECT
isnull(HarvestYear,0) HarvestYear
FROM
[tblFuelFlow]
outer apply dbo.Get_HarvestYear([ArrivalDateTime]);
Thanks

I was building it is pieces and hadn't bothered to simplify it more. Is there a difference in execution plans?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

GSquared (6/15/2016)
Jeff Moden (6/15/2016)
GSquared (6/14/2016)
I just tested a Calendar table version against the UDFs defined here.
Calendar table was consistently about 10X faster.
Can you post your test code?
Nope. The Post function keeps crashing when I try to post code to the forums for the last 2 days. Not sure why.
But it's really simple:
If you don't have a Calendar table, build one (lots of articles and samples available if you search for it)
Add an Int column called HarvestYear
Populate that column using whatever math you would use in the UDF
Join to the Calendar table using normal math for that (e.g.: "ON Calendar.BaseDate = CAST(MyTable.DateColumn AS DATE)")
Query however you like. I did a Select Into using a temp table for each. Tested 10k, 100k and 1M row performance. Repeated each test 10 times.

Cool... in the absence of code, that should help just about anyone that needs it. Thanks, Gus. And, yeah... understood on not being able to post code. That happens to me all the time at work because of the checking they do. It can be really frustrating. Haven't had that problem from home so not sure if your particular problem is SSC or not. I do know that they made a change several months back that now requires me to use different compatibility view settings for SSC just to be able to paste for an article submission.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)