March 1, 2013 at 7:04 am
ScottPletcher (2/28/2013)
Perhaps. But I think my code is instantly understandable when read. So unless you know or expect to be processing huge number of rows, developer time is multiple orders of magnitude more expensive than computer clock-time seconds.
BWHAAA!!!! THAT's what comments are for! So far as Developer time being more expensive than CPU time, that's absolutely true... until your code reaches the customer, performs poorly, gives the company a black eye that hurts business, and you end up having to rework little things like this.
That's why I almost always expect the code to be used on a huge number of rows. There are a whole lot of developers that don't know these things and will grasp onto ANY code that works no matter what the performance is. Besides, one of the purposes of this and other forums is to inform. I wasn't slamming you... I was stating a fact that the use of character conversions will make slower code.
Btw, weren't you the one that did testing that demonstrated that SET STATISTICS TIME ON itself caused, for example, scalar functions to appear worse than they really are? I'm not saying it's known, but isn't it possible that the process itself of determining the time used caused some of the spike in time? Although I know all string manipulation/handling in SQL Server is relatively extremely slow.
Yes, I'm the one. I did test in this case to make sure that the time it adds to each of the runs is equal. The reason I used it was so that people could take a measure of CPU time should parallism kick in. I didn't know for sure if it would or not but I wanted to show any differences if it did or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 7:14 am
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 7:50 am
Jeff Moden (2/26/2013)
Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.
I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.
If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)
Using the strings might even make the code more maintainable for others.
But if the code is repeatedly used, other code might be more suetable.
Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).
March 1, 2013 at 8:20 am
Jeff Moden (3/1/2013)That's why I almost always expect the code to be used on a huge number of rows. There are a whole lot of developers that don't know these things and will grasp onto ANY code that works no matter what the performance is. Besides, one of the purposes of this and other forums is to inform.
Smarter words have never been spoken. My corollary that my team lives by on the whiteboard every day is: If it doesnt scale, it isnt built right.
March 1, 2013 at 8:31 am
ben.brugman (3/1/2013)
Jeff Moden (2/26/2013)
Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.
If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)
Using the strings might even make the code more maintainable for others.
But if the code is repeatedly used, other code might be more suetable.
Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).
I'll have to disagree with you and side with Jeff. I have learned much from him and scalability and reuse actually come hand in hand. If you take the time now to build scalable code (read routines) that can be reused then spend the time. Just because it is good enough for a particular use does not mean it will be for another. Many times people will take what you have written and use it where it really shouldn't because it was just good enough for that one instance.
March 1, 2013 at 9:14 am
...
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
...
I was really joking about CPU cycles...
That was done purely for better readability and understanding, as 1900 can be easily recognised as a common SQL Server start year when 22800 looks a bit strange.
Actually, there is another way of having it done:
sometimes, detailed calendar table could be the answer
let say
CALENDAR (Date date, Year int, Month int, Day int, FirstDayOfMonth date, LastDayOfMonth date, ...)
so you can simply query it (eg using CROSS APPLY) as
SELECT FirstDayOfMonth, LastDayOfMonth
FROM CALENDAR
WHERE Year = @Year AND Month = @Month AND Day = 1
For some reason I think that with proper indexes on CALENDAR table the above will give the best performance.
Now, about using strings.
Actually, you will often hear two opinions on this subject:
1. My one
&
2. Wrong one
:hehe:
Seriously, some will still use string conversion as it's easier for them to understand really function does. Another ones, with a bit more knowledge about integer math behind of SQL dates, will be more than happy to use one based in calculations.
It's like using logic based on bit math operations. Some people just do not get it. So they do avoid it.;-)
March 1, 2013 at 10:40 am
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.
That code is much better since it's inherently understandable.
Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.
With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.
The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 1, 2013 at 10:42 am
Lynn Pettis (3/1/2013)
ben.brugman (3/1/2013)
Jeff Moden (2/26/2013)
Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.
If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)
Using the strings might even make the code more maintainable for others.
But if the code is repeatedly used, other code might be more suetable.
Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).
I'll have to disagree with you and side with Jeff. I have learned much from him and scalability and reuse actually come hand in hand. If you take the time now to build scalable code (read routines) that can be reused then spend the time. Just because it is good enough for a particular use does not mean it will be for another. Many times people will take what you have written and use it where it really shouldn't because it was just good enough for that one instance.
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 1, 2013 at 2:04 pm
ben.brugman (3/1/2013)
Jeff Moden (2/26/2013)
Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.
If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)
Using the strings might even make the code more maintainable for others.
But if the code is repeatedly used, other code might be more suetable.
Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).
Fortunately, you don't have to discover faster methods because I just handed one to you on a silver platter and you should incorporate it into your code library so you don't have to spend any time at it the next time you need it. 😉 The smart developer does spend some time learning new things to make it so they never have to hear the words "Hey! your code is slow!" from your boss.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 2:08 pm
ScottPletcher (3/1/2013)
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.
Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 2:12 pm
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.
That code is much better since it's inherently understandable.
Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.
With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.
The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.
If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.
If everyone wrote code to the lowest common denominator, all code would be dreadfully slow or resource inefficient.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 2:18 pm
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.
Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.
Very easy to say, extraordinarly hard to do in actual practice.
You have to determine in an existing needed function, say, already exists. Verify that it does indeed do what you are looking for, without unacceptable side effects, etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 1, 2013 at 2:20 pm
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.
That code is much better since it's inherently understandable.
Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.
With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.
The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.
If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.
If everyone wrote code to the lowest common denominator, all code would be dreadfully slow or resource inefficient.
Reductio ad absurdum. No one's advocating anything close to that.
Highly obscure coding practices to gain a few milliseconds per 100K+ rows, when realistically only 5,000 rows will ever be processed, just causes maintenance issues.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 1, 2013 at 2:21 pm
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.
Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.
Very easy to say, extraordinarly hard to do in actual practice.
You have to determine in an existing needed function, say, already exists. Verify that it does indeed do what you are looking for, without unacceptable side effects, etc..
So, instead of doing all that, just keep reinventing the wheel, right?
March 1, 2013 at 2:23 pm
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.
That code is much better since it's inherently understandable.
Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.
With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.
The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.
If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.
C'mon, be real, that code is clear enough that a decent developer will understand what it means. Anyone who's developed for SQL for any period of time understands that SQL's base date is 1900.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply