InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:39:07 GMT20http://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Lynn Pettis (3/1/2013)[/b]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.[/quote][b]If you have a limited amount of time, spend your time on the code which goes into (onto) the production system.[/b]I use a huge amount of queries studying the database and effects of code on the database (performance, frequency studies, fragmentation). I try to write these queries fast (and unoptimised). Yes sometimes I wait for two minutes and sometimes I improve those queries.And Yes this forum is of great value, for both production systems (were efficiency counts) and for producing code which works fast enough for non production systems. Often good code and readability goes hand in hand. But sometimes readability is offered to increase efficiency. But if I have to make a choice readability is of greater importance except if performance really matters. But this forum has attention for that as well. For me this forum is of more importance to learn and produce the code myself than, allthough I often use code of this forum as wel, which is also of great value.Thanks all for taking part in this thread,benMon, 04 Mar 2013 04:10:03 GMTben.brugmanhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxOugh! Gentlemen, no needs to argue so much on this subject. I guess everyone involved in this discussion is right on some points. 1900 is definitely more recognisable than 22800, however small comment comment like "22800 = 1900 * 12" would do enough to explain. It's possible and highly advisable to write reusable code (even for your own re-use purposes), but in the same time it's impossible to enforce code reuse in a large organisations, so re-inventing the wheel is a almost everyday developer's procedure (eg. non-sql ones). And on the bright note, I have some topic idea for your Congress: [quote] .... like trying to get Congress to agree on something.[/quote]What about "Double congressmen salary"? That should not take too much troubles to agree upon.Yeah, I know it would be couple of idiots there who will try to play "good guy" saying something against this great idea, but for some reason, I think, they will be convinced to vote YES in matter of hours!:hehe:Mon, 04 Mar 2013 01:59:43 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr]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.[/quote]Since you've worked for a company with "hundreds of developers", I'm genuinely surprise by your "be real" comment. You'll find many so called "decent" developers that get hired into a company of that size that don't even know what DATEADD is never mind that 1900 is the base date for an underlying serial number.[/quote]That's a self-contradictory claim. By definition, a decent T-SQL developer would know what DATEADD is. A decent developer that develops professionally with TSQL for a period of time would at least know that DATEADD is. Int'l Paper was a $25B+ company at the time, with over 100,000 employees (I think they're down to 70K now). If you want to imply that a company that size would not have "hundreds of developers", that's your choice. I won't bother with FedEx, as I assume you'll make the same unsupported "asnides" about them as well. As for myself, I won't imply that people are liars w/o some [b]actual evidence[/b]. Patently silly claims, otoh, can easily be identified [i]a priori[/i] as false.[/quote]C'mon Scott. "Decent" was in quotes and I never suggested nor even implied that you were a liar. I guess we'll have to agree to disagree. You agree to giving up on some aspects of performance for the sake of readability and I suggest that a simple comment in the code will give you both.Fri, 01 Mar 2013 17:24:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr]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.[/quote]Since you've worked for a company with "hundreds of developers", I'm genuinely surprise by your "be real" comment. You'll find many so called "decent" developers that get hired into a company of that size that don't even know what DATEADD is never mind that 1900 is the base date for an underlying serial number.[/quote]That's a self-contradictory claim. By definition, a decent T-SQL developer would know what DATEADD is. A decent developer that develops professionally with TSQL for a period of time would at least know that DATEADD is. Int'l Paper was a $25B+ company at the time, with over 100,000 employees (I think they're down to 70K now). If you want to imply that a company that size would not have "hundreds of developers", that's your choice. I won't bother with FedEx, as I assume you'll make the same unsupported "asnides" about them as well. As for myself, I won't imply that people are liars w/o some [b]actual evidence[/b]. Patently silly claims, otoh, can easily be identified [i]a priori[/i] as false.Fri, 01 Mar 2013 14:57:59 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr]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.[/quote]Then you should never use things like a Tally table or a difference between row numbers for performance. ;-)Fri, 01 Mar 2013 14:57:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr]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.[/quote]Since you've worked for a company with "hundreds of developers", I'm genuinely surprise by your "be real" comment. You'll find many so called "decent" developers that get hired into a company of that size that don't even know what DATEADD is never mind that 1900 is the base date for an underlying serial number.Fri, 01 Mar 2013 14:44:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Lynn Pettis (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote]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.[/quote]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.[/quote]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..[/quote]So, instead of doing all that, just keep reinventing the wheel, right?[/quote]I'm saying it's a massive effort to set up properly, and, more significantly, to train developers and others to use properly.Some companies that have tried it have discovered that for much of the code, it actually cost them more time to find and reuse existing code than to write new code.There is no perfect method for sharing code that everyone automatically knows all code that is available for reuse and when to use it.Again, it's very easy to glibly posit that everyone should do that. It's vastly harder to actually set up it and make it work well.The real world has a way of destroying easily-imagined fantasies.Fri, 01 Mar 2013 14:30:12 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Eugene Elutin (2/28/2013)[/b][hr]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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code][/quote]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.[/quote]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 [b]are[/b] massive, or [b]genuinely[/b] 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.[/quote]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.[/quote]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.Fri, 01 Mar 2013 14:23:59 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote]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.[/quote]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.[/quote]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..[/quote]So, instead of doing all that, just keep reinventing the wheel, right?Fri, 01 Mar 2013 14:21:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Eugene Elutin (2/28/2013)[/b][hr]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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code][/quote]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.[/quote]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 [b]are[/b] massive, or [b]genuinely[/b] 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.[/quote]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.[/quote][i]Reductio ad absurdum[/i]. 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.Fri, 01 Mar 2013 14:20:48 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]ScottPletcher (3/1/2013)[/b][hr][quote]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.[/quote]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.[/quote]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..Fri, 01 Mar 2013 14:18:36 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr][quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Eugene Elutin (2/28/2013)[/b][hr]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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code][/quote]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.[/quote]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 [b]are[/b] massive, or [b]genuinely[/b] 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.[/quote]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.Fri, 01 Mar 2013 14:12:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (3/1/2013)[/b][hr][quote]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.[/quote]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.Fri, 01 Mar 2013 14:08:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ben.brugman (3/1/2013)[/b][hr][quote][b]Jeff Moden (2/26/2013)[/b][hr]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.[/quote]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;-)).[/quote]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.Fri, 01 Mar 2013 14:04:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Lynn Pettis (3/1/2013)[/b][hr][quote][b]ben.brugman (3/1/2013)[/b][hr][quote][b]Jeff Moden (2/26/2013)[/b][hr]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.[/quote]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;-)).[/quote]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.[/quote]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.Fri, 01 Mar 2013 10:42:57 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b][hr][quote][b]Eugene Elutin (2/28/2013)[/b][hr]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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code][/quote]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.[/quote]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 [b]are[/b] massive, or [b]genuinely[/b] 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.Fri, 01 Mar 2013 10:40:28 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote]...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....[/quote]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 answerlet say CALENDAR (Date date, Year int, Month int, Day int, FirstDayOfMonth date, LastDayOfMonth date, ...)so you can simply query it (eg using CROSS APPLY) asSELECT FirstDayOfMonth, LastDayOfMonth FROM CALENDAR WHERE Year = @Year AND Month = @Month AND Day = 1For 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.;-)Fri, 01 Mar 2013 09:14:39 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ben.brugman (3/1/2013)[/b][hr][quote][b]Jeff Moden (2/26/2013)[/b][hr]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.[/quote]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;-)).[/quote]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.Fri, 01 Mar 2013 08:31:13 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (3/1/2013)[/b]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. [/quote]Smarter words have never been spoken. My corollary that my team lives by on the whiteboard every day is: [i]If it doesnt scale, it isnt built right.[/i]Fri, 01 Mar 2013 08:20:45 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (2/26/2013)[/b][hr]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.[/quote]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;-)).Fri, 01 Mar 2013 07:50:22 GMTben.brugmanhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Eugene Elutin (2/28/2013)[/b][hr]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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code][/quote]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.Fri, 01 Mar 2013 07:14:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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. [/quote]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.[quote]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. [/quote]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.Fri, 01 Mar 2013 07:04:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxThis 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):[code="sql"]select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOMselect dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM[/code]Thu, 28 Feb 2013 10:52:37 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (2/26/2013)[/b][hr]Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier. IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;--===== Create and populate the test table on-the-fly. SELECT TOP 10000000 TheYear = ABS(CHECKSUM(NEWID()))%14+2000, TheMonth = ABS(CHECKSUM(NEWID()))%12+1 INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;GO[/code]Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.[code="sql"] RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0) FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME))) FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket int; SET STATISTICS TIME ON; SELECT @BitBucket = TheYear * 10000 + TheMonth * 100 + 1 FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8)) FROM #TestTable; SET STATISTICS TIME OFF;GO[/code]Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).[code="plain"]========== INTEGER MATH Conversion ============================== SQL Server Execution Times: CPU time = 3151 ms, elapsed time = 3146 ms.========== Lynn's Conversion ============================== SQL Server Execution Times: CPU time = 3229 ms, elapsed time = 3241 ms.========== Scott's INT Conversion ============================== SQL Server Execution Times: CPU time = 2262 ms, elapsed time = 2253 ms.========== Scott's IMPLICIT STRING Conversion ============================== SQL Server Execution Times: CPU time = 6318 ms, elapsed time = 6313 ms.[/code]A couple of points to bring up here.First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. :-PLike Granny used to say... "Mind the pennies and the dollars will take care of themselves." :-D[/quote]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 [b]multiple orders of magnitude[/b] more expensive than computer clock-time seconds. 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 [i]all string[/i] manipulation/handling in SQL Server is relatively [i]extremely slow[/i].Thu, 28 Feb 2013 10:27:22 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (2/26/2013)[/b][hr][quote][b]candide (2/26/2013)[/b][hr]Hi,Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?[/quote][/quote]Jeff,I searched some time for a solution, but most answers work with string handling like 'CAST blabla varchar blabla' which may work but is not what I think of adjacent handling of date values. A numeric solution like yours I never saw before, and it's simple and fast. Nothing more to say...Waiting for cool date values handling in next SQL Server version...:-)Thu, 28 Feb 2013 03:26:17 GMTcandidehttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxJust to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier. IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;--===== Create and populate the test table on-the-fly. SELECT TOP 10000000 TheYear = ABS(CHECKSUM(NEWID()))%14+2000, TheMonth = ABS(CHECKSUM(NEWID()))%12+1 INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;GO[/code]Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.[code="sql"] RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0) FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME))) FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket int; SET STATISTICS TIME ON; SELECT @BitBucket = TheYear * 10000 + TheMonth * 100 + 1 FROM #TestTable; SET STATISTICS TIME OFF;GO RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;DECLARE @BitBucket DATETIME; SET STATISTICS TIME ON; SELECT @BitBucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8)) FROM #TestTable; SET STATISTICS TIME OFF;GO[/code]Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).[code="plain"]========== INTEGER MATH Conversion ============================== SQL Server Execution Times: CPU time = 3151 ms, elapsed time = 3146 ms.========== Lynn's Conversion ============================== SQL Server Execution Times: CPU time = 3229 ms, elapsed time = 3241 ms.========== Scott's INT Conversion ============================== SQL Server Execution Times: CPU time = 2262 ms, elapsed time = 2253 ms.========== Scott's IMPLICIT STRING Conversion ============================== SQL Server Execution Times: CPU time = 6318 ms, elapsed time = 6313 ms.[/code]A couple of points to bring up here.First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. :-PLike Granny used to say... "Mind the pennies and the dollars will take care of themselves." :-DTue, 26 Feb 2013 20:13:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Michael Valentine Jones (2/26/2013)[/b][hr][quote][b]Jeff Moden (2/25/2013)[/b][hr]You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2013, @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)[/code]Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2012, --Leap Year! @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)[/code]Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.[/quote]That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:Make Date function (like in VB)[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339[/url][/quote]Thanks, Michael. This time, I'm going to add that URL to the code snippet.Tue, 26 Feb 2013 18:20:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]Jeff Moden (2/25/2013)[/b][hr]You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2013, @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)[/code]Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2012, --Leap Year! @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)[/code]Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.[/quote]That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:Make Date function (like in VB)[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339[/url]Tue, 26 Feb 2013 17:29:04 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxThe integer value for the first day of the month is even easier:[code="sql"]SELECT BOM = @ThisYear * 10000 + @ThisMonth * 100 + 1,[/code]but you [i]must[/i] CAST it to char(8) before storing it in a date/datetime column.[code="sql"]DECLARE @BOM datetimeSELECT @BOM = CAST(@ThisYear * 10000 + @ThisMonth * 100 + 1 AS char(8))SELECT @BOM[/code]Tue, 26 Feb 2013 15:43:05 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]candide (2/26/2013)[/b][hr]Hi,Jeff's solution works great:-Pexactly what I neededthanx[/quote]Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?Tue, 26 Feb 2013 08:01:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]ben.brugman (2/26/2013)[/b][hr]And another alternative solution, based on 'strings'[code="sql"]declare @Month int = 2, @Year int = 2013declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)select CONVERT(varchar(7),@thisdate,121)+'-01'select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'[/code]ben brugman[/quote]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.Tue, 26 Feb 2013 07:59:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxAnd another alternative solution, based on 'strings'[code="sql"]declare @Month int = 2, @Year int = 2013declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)select CONVERT(varchar(7),@thisdate,121)+'-01'select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'[/code]ben brugmanTue, 26 Feb 2013 05:09:16 GMTben.brugmanhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxHi,Jeff's solution works great:-Pexactly what I neededthanxTue, 26 Feb 2013 04:43:27 GMTcandidehttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxYou can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2013, @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)[/code]Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT; SELECT @ThisYear = 2012, --Leap Year! @ThisMonth = 2; SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0), EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)[/code]Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.Mon, 25 Feb 2013 22:38:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxThis will work:[code="sql"]DECLARE @ThisYear INT, @ThisMonth INT;SET @ThisYear = 2013;SET @ThisMonth = 2;SELECT DATEADD(MONTH, @ThisMonth - 1, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME))) BOM, DATEADD(DAY, -1, DATEADD(MONTH, @ThisMonth, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME)))) EOM[/code]Mon, 25 Feb 2013 15:45:57 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspx[quote][b]candide (2/25/2013)[/b][hr]Hi,i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two new columns with the first and last day of this year/month:pinch:ex.2013 02 => 2013-02-01 2013-02-28etc.thanx[/quote]This will work.[code]declare @Month int = 2, @Year int = 2013declare @ThisDate datetimeset @ThisDate = cast(@Year as char(4)) + right('0' + cast(@Month as varchar(2)), 2) + '01'select @ThisDateselect dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this monthselect dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) -- End of this month[/code]Take a look at Lynn's article for a number of datetime routines here. [url=http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/]http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]If at all possible you should consider storing datetime information as datetime instead of multiple integer columns.Mon, 25 Feb 2013 15:21:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423790-391-1.aspxHi,i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two new columns with the first and last day of this year/month:pinch:ex.2013 02 => 2013-02-01 2013-02-28etc.thanxMon, 25 Feb 2013 15:11:48 GMTcandide