January 27, 2011 at 10:49 am
william-700725 (1/27/2011)
[font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]
I assume you mean UPPER and not UCASE right?
January 27, 2011 at 11:35 am
william-700725 (1/27/2011)
[font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]
BWAA-HAA!!! Nope... not "excessively pedantic" at all. 😛 It's a great tip. Way too many people jump through way too many hoops trying to format dates where native formats are at the ready and usually provide better performance.
Shifting gears and since you brought it up (I'm an old sub-sailor and DOD worker), my favorite mil-spec is [font="Arial Black"]MIL-TP-41[/font] and, to me, is the crux of ISO and SOX compliance... it means "Make It Like The Print for once". 😀
Thanks for the read and the feedback, William.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2011 at 11:55 am
UMG Developer (1/27/2011)
william-700725 (1/27/2011)
[font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]
I assume you mean UPPER and not UCASE right?
[font="Courier New"]Thanks for the correction.
Most of my mil-spec work (virtually all, except the project I started myself) is done in another T-SQL dialect where the earliest examples I copy-and-pasted from all used UCASE instead of UPPER. It's an ongoing habit which causes me needless grief when I'm porting between T-SQL dialects. On the other hand, at lease I did remember to use GETDATE() instead of TODAY( * ) or NOW( * ). ;-)[/font]
January 27, 2011 at 12:02 pm
william-700725 (1/27/2011)
[font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]
I prefer to use format 121 to get the full precision of the DATETIME and DATETIME2 datatypes in a sortable character string.
Example:
select
convert(varchar(23),getdate(),121)as [Datetime],
convert(varchar(27),sysdatetime(),121)as [Datetime2]
Results:
Datetime Datetime2
----------------------- ---------------------------
2011-01-27 12:58:07.567 2011-01-27 12:58:07.5678911
January 27, 2011 at 12:31 pm
Jeff Moden (1/27/2011)
... my favorite mil-spec is [font="Arial Black"]MIL-TP-41[/font] and, to me, is the crux of ISO and SOX compliance... it means "Make It Like The Print for once". 😀
[font="Courier New"]Hmm.... For some reason, the first time I read that my mind lept to the conclusion that '41 was the year that they wrote the spec for the toilet paper used in-theater (think C-rat TP on a roll). But that's also a context where "output buffer overflow" can't be overlooked...
Thanks for continuing to make my time spent here a worthwhile investment (a significant time suck going in, but a big-time reputation enhancer when asked to "take a look" at something that's not working quite right). Plus it's nice to see elapsed time treated as a significant metric -- you might be shocked at how little value some DBAs assign to getting results in 50-odd seconds vice 880-odd seconds (although the end-users sure noticed the difference).[/font]
January 27, 2011 at 12:46 pm
Michael Valentine Jones (1/27/2011)
william-700725 (1/27/2011)
[font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]
I prefer to use format 121 to get the full precision of the DATETIME and DATETIME2 datatypes in a sortable character string.
Example:
select
convert(varchar(23),getdate(),121)as [Datetime],
convert(varchar(27),sysdatetime(),121)as [Datetime2]
Results:
Datetime Datetime2
----------------------- ---------------------------
2011-01-27 12:58:07.567 2011-01-27 12:58:07.5678911
[font="Courier New"]That's a good idea when the added precision is significant and accurate. On the other hand, when data is getting entered on a daily basis when things work right, the added precision is too easily confused with added accuracy.
In my own experience, CONVERT( char ( 16 ) , GETDATE() , 120 ) is usually more than precise enough without adding extra volume to the the output -- in fact, more often than not I'll trim it down to char( 10 ).
Lest you think I'm maligning my users' average attention to detail, I will state for the record that I'm reasonably sure that at least 3 out of any given 4 can breath with their mouth closed.[/font]
January 28, 2011 at 2:07 am
Jeff Moden (1/23/2011)
You bet. Thanks for the feedback, the tips, and a couple of other things.As a side bar, recursive CTE's tend to be a bit slow and resource intensive for doing splits. You might want to consider one of the other methods.
Everything is relative!
My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!
SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime datetime
--Timer to measure total duration
SET @StartTime = GETDATE()
--===== Split or "Normalize" the whole table at once
SELECT mh.lcid ,row_number() over ( partition by mh.lcid order by t.N),
SUBSTRING(',' + mh.months + ',', N + 1,
CHARINDEX(',', ',' + mh.months + ',', N + 1) - N - 1) AS Value
FROM dbo.Tally t
CROSS JOIN sys.syslanguages mh
WHERE N < LEN(',' + mh.months + ',')
AND SUBSTRING(',' + mh.months + ',', N, 1) = ','
--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
SET @StartTime = GETDATE()
;With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position, charindex(',', sll.shortmonths,2) as endPosition FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, m.endPosition+1 as StartPosition, CHARINDEX(',',sl.shortmonths+',', m.endPosition+1) from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
) --SELECT lcid, m.shortmonths,m.Position, m.endPosition , m.endPosition-m.Position as sublength, len(m.shortmonths) as lenght FROM Months m
SELECT lcid, substring(m.shortmonths,m.Position, m.endPosition-m.Position+1) FROM Months m
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
January 28, 2011 at 9:58 am
David McKinney (1/28/2011)
Everything is relative!
My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!
[font="Courier New"]Been there, felt that, and still can't ditch the danged T-shirt...[/font]
February 2, 2011 at 11:25 pm
David McKinney (1/28/2011)
Jeff Moden (1/23/2011)
You bet. Thanks for the feedback, the tips, and a couple of other things.As a side bar, recursive CTE's tend to be a bit slow and resource intensive for doing splits. You might want to consider one of the other methods.
Everything is relative!
My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!
SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime datetime
--Timer to measure total duration
SET @StartTime = GETDATE()
--===== Split or "Normalize" the whole table at once
SELECT mh.lcid ,row_number() over ( partition by mh.lcid order by t.N),
SUBSTRING(',' + mh.months + ',', N + 1,
CHARINDEX(',', ',' + mh.months + ',', N + 1) - N - 1) AS Value
FROM dbo.Tally t
CROSS JOIN sys.syslanguages mh
WHERE N < LEN(',' + mh.months + ',')
AND SUBSTRING(',' + mh.months + ',', N, 1) = ','
--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
SET @StartTime = GETDATE()
;With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position, charindex(',', sll.shortmonths,2) as endPosition FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, m.endPosition+1 as StartPosition, CHARINDEX(',',sl.shortmonths+',', m.endPosition+1) from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
) --SELECT lcid, m.shortmonths,m.Position, m.endPosition , m.endPosition-m.Position as sublength, len(m.shortmonths) as lenght FROM Months m
SELECT lcid, substring(m.shortmonths,m.Position, m.endPosition-m.Position+1) FROM Months m
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2011 at 9:54 am
Jeff Moden (2/2/2011)
BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.
I can't wait, when do you think it will be released?
February 4, 2011 at 6:47 am
UMG Developer (2/3/2011)
Jeff Moden (2/2/2011)
BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.I can't wait, when do you think it will be released?
Not sure on that. Still doing some performance and "what if" and "it depends" testing on it along with everything else. I have a couple of versions of the same thing that I'm comparing for ease-of-use and performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2012 at 3:27 am
Jeff Moden (1/21/2011)
Very cool, Peter! Thanks for the coding, the testing, and the hypothesis! I'll do some additional testing over the weekend.
Not sure if your testing caught my error, but in my latest test, I used "GetDate()" instead of "SomeDate", skewing my final results as a semi-constant is obviously easier on the processing when applied over many rows.
May 10, 2012 at 7:18 am
I did miss that. Thank you for the update.
Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2012 at 7:39 am
Jeff Moden (5/10/2012)
I did miss that. Thank you for the update.Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
I know that particular splitter article as improvement was my own contribution after somone already brought a 10% boost.
And now we switched to the subject of the T-SQL string splitter, you will find great interst in this blog entry from Paul White:
http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx
There is a bug of some sort in 2008 and 2008 SE (I am not sure about 2012) that causes runaway generation of rows by the CTE. The bigger the number of rows the CTE can deliver, the bigger the problem becomes. Lucky for us, the splitter was dedicated to just 8000 rows to support varchar fully and 20*20*20 using cross joins would suffice and limit the performance damage this worst case issue brings.
Due to this bug I don't hold my breath for an T-SQL based varchar(max) anymore as even occasional problems could cause the runtimes to spike unacceptably. Using .NET code for this particular situation seems the reliable way to go, without good T-SQL alternative to fall back on. Unless you found a breaktrough of some sort of course 🙂
Kind regards,
Peter de Heer
May 10, 2012 at 7:51 am
peter-757102 (5/10/2012)
Jeff Moden (5/10/2012)
I did miss that. Thank you for the update.Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
I know that particular splitter article as improvement was my own contribution after somone already brought a 10% boost.
And now we switched to the subject of the T-SQL string splitter, you will find great interst in this blog entry from Paul White:
http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx
There is a bug of some sort in 2008 and 2008 SE (I am not sure about 2012) that causes runaway generation of rows by the CTE. The bigger the number of rows the CTE can deliver, the bigger the problem becomes. Lucky for us, the splitter was dedicated to just 8000 rows to support varchar fully and 20*20*20 using cross joins would suffice and limit the performance damage this worst case issue brings.
Due to this bug I don't hold my breath for an T-SQL based varchar(max) anymore as even occasional problems could cause the runtimes to spike unacceptably. Using .NET code for this particular situation seems the reliable way to go, without good T-SQL alternative to fall back on. Unless you found a breaktrough of some sort of course 🙂
Kind regards,
Peter de Heer
I'll take a look at Paul's post but I believe the use of TOP overcomes that problem. It turns out that Ben-Gan added that particular enhancement just a little bit after he first published the cascading CTE method. I missed his article on that enhancement.
Thanks again for your improvement on the code in the aritcle, Peter.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 108 total)
You must be logged in to reply to this topic. Login to reply