January 20, 2011 at 2:35 pm
I can improve that to 1578 ms if I hard code a 3 for the legnth I cut off the LEN function's return.
PRINT '============ CONVERT to handle foreign languages 2=========';
SET STATISTICS TIME ON;
SELECT
RIGHT(CONVERT(VARCHAR(100),somedate,106),LEN(CONVERT(VARCHAR(100),somedate,106)) - 3)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
========== Original Method of Conversion ==========
SQL Server Execution Times:
CPU time = 2187 ms, elapsed time = 2317 ms.
========== DATENAME Method of Conversion ==========
SQL Server Execution Times:
CPU time = 1157 ms, elapsed time = 1276 ms.
========== CONVERT Method of Conversion ==========
SQL Server Execution Times:
CPU time = 1031 ms, elapsed time = 1030 ms.
============ CONVERT to handle foreign languages 2=========
SQL Server Execution Times:
CPU time = 1578 ms, elapsed time = 1587 ms.
January 20, 2011 at 2:50 pm
Thanks for the fine article Jeff. I learned a lot about this.
For us English speakers, I wish there was a way to include the last letters for June and July. It just irks me to see them abbreviated to three letters when, for those two months, there's just that one more little letter... :ermm:
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 20, 2011 at 3:29 pm
Been a big fan of CONVERT since I discovered you could use it when converting strings back to dates as well!
For the ultra purist uses the fact that with date format 0 the short month name comes first and will be followed by a space.
SELECT RTRIM(SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 0), 1, CHARINDEX(' ', CONVERT(VARCHAR(20), GETDATE(), 0))))
January 20, 2011 at 5:04 pm
Great stuff Jeff - thanks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2011 at 5:12 pm
Excellent! Thanks Jeff. I've briefcased it!
Cheers,
Nicole Bowman
Nothing is forever.
January 20, 2011 at 11:21 pm
I nowadays do the original conversion like a mechnical machine. Just type those 10-15 lines to convert the date. Thanks Jeff I will now switch over to Datename. Great writing man!!!!!
January 20, 2011 at 11:26 pm
Hey Jeff, thanks for article.. well written and full of information! Thanks a bunch! 🙂
January 21, 2011 at 3:36 am
Thanks again Jeff for the discussions you always seem to bring up among us.
Time to put in a few thoughts and findings of my own. As someone already observed, DATENAME is a rather slow function on more modern machines. It just shows that the best model to do benchmark against is something that is similar to where the code is expected to eventually run on.
Here is a common fast technique for looking up fixed length substrings (as is with short month names in most languages):
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 3 )
This will return a three letter short month name very fast. Now subsequent string concatenations can slow it down a bit, but if a month is all you need, this one will be hard (if not impossible) to beat. Also note that substituting the last argument (3) with (4) gives you a free concatenated space at the end of the month. The same sort of trick you can use to prefix a month with a space or a space and a comma. While fast on its own, subsequent string operations, such as adding a year as text will slow it down again. Also note that in case of variable length substrings names, it is still cheap to pad them with spaces to make them all the same length and then do a right trim (rtrim) over it.
I also took a look at the quite interesting sys.syslanguages table, but what i miss there is list of short months compatible with my above requirement. That said, it isn’t hard at all to derive a customized version of that table with only the stuff you expect to use. Another good option in my opinion is the use of calendar tables where you can store short representation texts in a denormalized way and retrieve it very fast. Granted, you miss the multi-language aspect, but I never really needed that in my work anyway.
January 21, 2011 at 4:44 am
I did some more testing and as it turns out, much of the cost in slower solutions is in dissecting a date multiple times and/or using an inefficient method. And as often in SQL, there are many ways to skin a cat, each with different performance characteristics.
/* Five methods to get a year as text from a date */
select convert( char(4), getDate(), 112 )
select datename( yyyy, getDate() )
select cast( datepart( year, getDate() ) as char(4) )
select cast( year( getDate() ) as char(4) )
select cast( 1900 + dateDiff( year, 0, getDate() ) as char(4) )
Note the use of format 112 in the above convert, it is a deterministic format, so can be used in deterministic functions.
As it turns out, the last and reletivly complex is the fastest of all and thus even beats the specialised 'datepart' and 'year' functions at their own game. I suspect dateDiff uses an optimised code path, whereas datepart first disects the whole datetime and then takes the date part. The year function is problably nothing more then a wrapper and is internally converted into a datepart.
Here are my timings (test code first, followed by results):
DECLARE @Bitbucket CHAR(8),
@NBitBucket NCHAR(8)
;
PRINT '========== Original Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CASE DATEPART(mm,SomeDate)
WHEN 01 THEN 'Jan'
WHEN 02 THEN 'Feb'
WHEN 03 THEN 'Mar'
WHEN 04 THEN 'Apr'
WHEN 05 THEN 'May'
WHEN 06 THEN 'Jun'
WHEN 07 THEN 'Jul'
WHEN 08 THEN 'Aug'
WHEN 09 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
ELSE 'Dec'
END + ' ' + CAST(DATEPART(yy,SomeDate) AS CHAR(4))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== DATENAME Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @NBitBucket = SUBSTRING(DATENAME(mm,SomeDate),1,3) + N' ' + DATENAME(yy,SomeDate)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== CONVERT Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(CONVERT(CHAR(11),SomeDate,106),8)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Substring + year() Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( year( SomeDate ) as char(4) )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Substring + datepart( year ) Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( datepart( year, SomeDate ) as char(4) )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Substring + ISO convert of year Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + convert( char(4), SomeDate, 112 )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Substring + datediff (generally fastest) Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
========== Original Method of Conversion ==========
SQL Server Execution Times:
CPU time = 1453 ms, elapsed time = 1463 ms.
========== DATENAME Method of Conversion ==========
SQL Server Execution Times:
CPU time = 1031 ms, elapsed time = 1030 ms.
========== CONVERT Method of Conversion ==========
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 736 ms.
========== Substring + year() Method of Conversion ==========
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 752 ms.
========== Substring + datepart( year ) Method of Conversion ==========
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 751 ms.
========== Substring + ISO convert of year Method of Conversion ==========
SQL Server Execution Times:
CPU time = 954 ms, elapsed time = 954 ms.
========== Substring + datediff (generally fastest) Method of Conversion ==========
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 723 ms.
It makes me wonder if month( somedate ) can also be optimised as I did with year( somedate )
January 21, 2011 at 5:05 am
I DISCOVERED AN ERROR IN MY CODE HERE, SKEWING THE RESULTS, PLEASE IGNORE THIS PART OF MY CONTRIBUTION
(In one of the two datediffs, I used getDate() instead of "SomeDate" that is read from the large table of testdata)
As I suspected, it can be optimised further by discarding all use of datepart, month, year and convert functions.
The new testcode (only included original and fastest running versions)
DECLARE @Bitbucket CHAR(8),
@NBitBucket NCHAR(8)
;
PRINT '========== Original Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CASE DATEPART(mm,SomeDate)
WHEN 01 THEN 'Jan'
WHEN 02 THEN 'Feb'
WHEN 03 THEN 'Mar'
WHEN 04 THEN 'Apr'
WHEN 05 THEN 'May'
WHEN 06 THEN 'Jun'
WHEN 07 THEN 'Jul'
WHEN 08 THEN 'Aug'
WHEN 09 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
ELSE 'Dec'
END + ' ' + CAST(DATEPART(yy,SomeDate) AS CHAR(4))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
select @Bitbucket;
PRINT '========== CONVERT Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(CONVERT(CHAR(11),SomeDate,106),8)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
select @Bitbucket;
PRINT '========== Substring + datediff for year Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
select @Bitbucket;
PRINT '========== Substring + (datediff for all date processing) Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ( dateDiff( month, 0, getDate() ) % 12 + 1 ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
select @Bitbucket;
========== Original Method of Conversion ==========
SQL Server Execution Times:
CPU time = 1640 ms, elapsed time = 1618 ms.
========== CONVERT Method of Conversion ==========
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 751 ms.
========== Substring + datediff for year Method of Conversion ==========
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 724 ms.
========== Substring + (datediff for all date processing) Method of Conversion ==========
SQL Server Execution Times:
CPU time = 547 ms, elapsed time = 555 ms.
The lastest code is no less then 3 times faster then the original, now that is progress 🙂
The big lesson for me in all this is to be shy of the datepart and similar functions, avoid them whenever you can!
January 21, 2011 at 8:14 am
Very cool, Peter! Thanks for the coding, the testing, and the hypothesis! I'll do some additional testing over the weekend.
--Jeff Moden
January 21, 2011 at 10:11 am
Jeff,
I'm waiting for you to blow us away with your tally table solution to pull the correct month from the comma separated field in sys.syslanguages.
Or will I have to try to do it myself? :w00t:
January 21, 2011 at 2:55 pm
Thanks for another great piece of spackle!
January 21, 2011 at 4:24 pm
David McKinney (1/21/2011)
Jeff,I'm waiting for you to blow us away with your tally table solution to pull the correct month from the comma separated field in sys.syslanguages.
Or will I have to try to do it myself? :w00t:
Heh... thanks for the confidence, David, but I believe the Tally Table solution would be slower than Peter's good method. I wish it were otherwise but the Tally Table isn't a panacea. 🙂
--Jeff Moden
January 22, 2011 at 2:25 pm
Well I didn't use your speciality, Jeff, the tally table...instead I used my speciality the CTE 😎
I was mucking about with recursive CTEs (actually I rather like them at the moment)
and ended up constructing a view which converts the shortmonths column of sys.syslanguages into something useful. If you don't want the view just try the select.
The view could be used as the basis of a solution which would work for all languages.
Hope you like it!
CREATE VIEW ShortMonths as
With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, CHARINDEX(',',sl.shortmonths, m.Position+1)+1 from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
),
ShowMonths as
(select m1.lcid,m1.MonthNumber,m1.shortmonths,m1.Position as StartPosition, isnull(m2.Position-1,len(m1.shortmonths)+1) as EndPosition FROM Months m1 left join Months m2 on m1.MonthNumber=m2.MonthNumber-1 and m1.lcid=m2.lcid)
select sm.lcid, SUBSTRING(sm.shortmonths,sm.StartPosition,sm.EndPosition-sm.StartPosition) as ShortMonth,sm.MonthNumber FROM ShowMonths sm
Viewing 15 posts - 46 through 60 (of 108 total)
You must be logged in to reply to this topic. Login to reply