just the date, please
-
OK Jeff, you don't have to place the code in a UDF since performance is slower. But this was not the first OP asking how to accomplish this task.
You yourself posted performance results for several methods:
===== Rounding method 2 ======
720 Milliseconds
===== DateDiff/DateAdd method ======
826 Milliseconds
===== Rounding method 1 ======
2970 Milliseconds
===== Convert method ================
5093 Milliseconds
===== Johnathons's Integer function =====
640 Milliseconds
===== Floor method =====
1813 Milliseconds
===== Minus method =====
783 Milliseconds
So how would you suggest enforcing use of the "best" method, when using the inline version of the code?
-
With SQL Server 2005, this must be even faster since you can make the computed column persisted!
N 56°04'39.16"
E 12°55'05.25" -
Computed columns can be persited in 2000 also ( by creating an index on them
)
* Noel -
Jeff, you're comparing to the wrong thing.
To be closer to real life you should compare performance of function vs. something what would be used by normal developers.
Like this:
select Left(GetDate(),11)
simply because it's less typing
_____________
Code for TallyGenerator -
If it's "inline" code, as you say, I've previously told the OP's to use the DATEADD/DATEDIFF method to ensure that there's no chance of an "anomoly" like what appeared in Rounding method 2 (fixed by Peter). Peter's "test" demonstrated that "Johnathon's Integer 'Function'" works correctly and very quickly as does Peter's Minus method (although I swear I previously found an instance where Johnathon's did not work correctly, still looking at that).
There appear to be 3 performance candidates that work correctly... DATEADD/DATEDIFF, Johnathon's, and now Peter's... because of my need for speed, I'll be doing some more testing in different environment's for those 3 before I cinch-up what I'll be recommending in the future. I may end up recommending that OP's test each of the 3 in their environment to see which one is best for their environment... For example, Peter's method won the performance footrace on my machine while Johnathon's method appears to have won that same race on your machine. "It Depends". But, none of my recommendations will likely include wrapping those methods in a UDF because of the performance hit... they can come to that mistake... er.. ah... conclusion if they value that more than performance/scalability.
So how would you suggest enforcing use of the "best" method, when using the inline version of the code?
Same way as what's required for the use of a UDF... code reviews... can't enforce squat without them.
Actually, if you have "control" over the table design, I think the "best" way is as what appeared in the response that Serqiy posted... make a deterministic calculated column using the formula that works best for your environment and include that column in the index(es) you need for max speed. Still, takes a code review to ensure the column was referenced instead of a calc.
And, please, folks... I'm not trying to be confrontational about this whole UDF thing or the best method for this... I just think that taking a 10:1 performance hit for the sake of code reuse is a bit much no matter how big or small a database may be.
--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) -
Heh... just can't bring myself to do that, Serqiy... that would mean I'd have to demo saving the date as an integer that looks like an ISO date or storing Year, Month, and Date as separate VARCHAR columns
--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) -
I just think that taking a 10:1 performance hit for the sake of code reuse is a bit much no matter how big or small a database may be.
As one would say, it depends.
Typically slowest "convert to varchar" method would not take more than 1% of total query time. So, improving performance of it by 10 times you improve overall processing time by less than 1%.
Is sacrificing of code reusability worth it?
It's the same story as with explicit dropping # tables in SP.
Yes, it's overhead, it makes SP slower.
But typically time wasted on it is "beyond of precision". You never notice it until you set up special test for it.
And guys who like to have "clean up" explicitly in code can keep their habits because it fits their mind better and does not really affect overall performance.
So, such overheads are still overheads, but not big deals.
And if you are not the only person cutting SQL code on your projects you better supply other developers with robust functions than try to fix bugs they embedded in their code.
_____________
Code for TallyGenerator -
Jeff, I think you ARE being confrontational about this whole UDF thing, simply because you don't agree with it. You speak of "code reviews" but who made you the expert at code review, anyway?
I agreed that the UDF adds some overhead, but it many cases it will be insignificant with small datasets or super-fast servers.
And the comments about losing credibility as the "go-to guy" and the "lead" have no place in this thread. I've seen plenty of go-to guys and leads who were total experts in an extremely narrow technical field, but when it came to applying the business rules and real-world scenarios, they couldn't tell their debits from their credits, if you know what I mean.
Just let it go, man.
-
You're kidding...right?
--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) -
-
And the flaming starts again. I think the professionalism of this site is starting to wane.
-
Heh... yeaup... and this time, the newbie started it
Can't flame if there's no fuel so I'm gonna let William think what he wants and bail. I hope somebody got some good info off this thread 'cause I sure did try.
--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) -
I partly agree with that, but some else has heaped some fuel on it as well (not you, Jeff). I got some good info that could come in handy.
-
Jeff,
by the way, if to go to extreme performance tuning you need to locate all your databases on raw partitions.
They are faster.
But how many of you databases use it?
_____________
Code for TallyGenerator -
Jeff, I did not really mean to offend you, you're a very clever guy but I think you went over over the line with a few of your comments.
You said:
"But, none of my recommendations will likely include wrapping those methods in a UDF because of the performance hit... they can come to that mistake... er.. ah... conclusion if they value that more than performance/scalability."
By using "mistake" is that not itself a flame? That sure sounds like an insult.
You also said:
"I'm not trying to be confrontational about this whole UDF thing" but that tells me that you knew that you were being confrontational, but you were just trying to brush it aside.
As far as calling me a "newbie" my programming career started in the 1970's with 8080/Z-80 assembler for a 1-MHz CPU with 32K RAM and 8" floppy disks, so back then CPU cycles were precious and we had to make every byte count. We used plenty of tricks & shortcuts just to fit into that address space.
Welcome to the 21st Century. Why do you suppose Windows has 1,000's of API calls? There must be some overhead there, why not just inline every function? Because encapsulation, object and code reuse is today's programming paradigm. When a change is required in a module, that DLL or whatever can be changed out and it propagates throughout the OS.
As far as "the lead" goes, the good ones supervise their team and guide them thru the rough spots. It's their job to make sure everybody knows about good pre-baked code, whether it is in a code library, VSS or UDF. I was once on a team of 8 where the lead insisted on using T-SQL joins and he'd actually spend his weekends re-writing our ANSI joins. Bet he was surprised when he learned that 2005 doesn't accept *= and =* for outer joins.
In another bad example, some "experts" converted customer data from an old Windows app to their new web app, but they only created an A/R balance forward for members that owed money; it never occurred to them that a member might have a credit balance.
Anyhow, I hope I am not hated and reviled by all...
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply