Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

Formatting Dates with 3 Character Months (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 8:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 337, Visits: 2,284
Jeff,

With runaway generation of rows I do not mean these rows end up in the result or are part of additional processing by the consuming parts of the query. But that rows are generated before the TOP operator takes effect when dealing with parallel execution plans. Not always, but frequent enaugh to cause issues of unpredictable large slowdowns. The processing is in the generation of the rows itself for large sets and not anything that depends on the rows that come out of the top operator.

It is a bug obviously and one that the connect issue can use an extra votes for :)
Post #1297899
Posted Thursday, May 10, 2012 8:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 35,858, Visits: 32,529
Wow! I just read Paul's article. He really does do a nice job explaining some of these things.

To summarize, TOP apparently doesn't fix things for 2008 and above. You need to add OPTION(MAXDOP 1) to really get it to behave properly. That's ok because it still screams.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1297900
Posted Friday, July 19, 2013 12:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 21, 2014 11:05 PM
Points: 17, Visits: 58
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'

Post #1475286
Posted Friday, July 19, 2013 4:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
fregatepallada (7/19/2013)
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'


Thank you! I was about to mention the format() function as well.

If all you want is the month, you can use MMM or Mmm as the format string. Plus you can change locales!

Of course, the caveat is that it doesn't work in versions of SQL Server prior to 2012. Sadly, that rules it out for most of what I do. The good news is that we BI people pre-build date dimensions with all of that stuff anyway, so I only ever have to worry about this sort of thing when actually building a date dimension. And of course, the general rule is that in almost every case, return a date as a date and let the front end application do the formatting.
Post #1475368
Posted Friday, July 19, 2013 5:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:41 AM
Points: 515, Visits: 1,743
fregatepallada (7/19/2013)
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'



that method, while super easy to read and understand, is super slow.

using the FORMAT function on my laptop, the results are sorta bad....

  PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;

========== Format Methed of Conversion ==========

SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.
Post #1475385
Posted Friday, July 19, 2013 5:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 2, 2014 7:45 AM
Points: 212, Visits: 31
The other advantage of using datename for days of the week, is that you don't have to remember if 0 is Sun or Mon etc ! (or set it at the top of the code)


Post #1475393
Posted Friday, July 19, 2013 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:10 PM
Points: 38, Visits: 179
Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'



that method, while super easy to read and understand, is super slow.

using the FORMAT function on my laptop, the results are sorta bad....

  PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;

========== Format Methed of Conversion ==========

SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.

Jeff let's compare apples with apples - just remove select value from table for starters !
Post #1475428
Posted Friday, July 19, 2013 7:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:56 AM
Points: 3,135, Visits: 3,257
Hi Jeff,

Another useful article, Thank You!

I repeated your work on my machine and the improvement was between 1.5 and 1.7 times.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1475460
Posted Friday, July 19, 2013 7:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 35,858, Visits: 32,529
Irozenberg 1347 (7/19/2013)
Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'



that method, while super easy to read and understand, is super slow.

using the FORMAT function on my laptop, the results are sorta bad....

  PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;

========== Format Methed of Conversion ==========

SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.

Jeff let's compare apples with apples - just remove select value from table for starters !


What do you mean "remove select value from table"? Why wouldn't you use this against a table?

Geoff (the other "Jeff"), did the exact same test as what's in the article which contains a million rows. I'd say he did a pretty good job of comparing "apples-to-apples". I did the original test in the article on an 11 year old single CPU desktop, which is MUCH slower than any modern day laptop, and the "new" 2012 method is still 10 times slower according to Geoff's tests.

I will admit though... both of us used SET STATISTICS to get our times which has been known to make a mess of things when testing Scalar Functions in T-SQL. Perhaps the new FORMAT function has the same problems as Scalar Functions written in T-SQL. I don't have 2012 loaded on any of my boxes at home or at work (explains why I didn't load it at home), yet. I do have a vacation starting tomorrow. Maybe I'll have time to load 2012 on my laptop during the vacation.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475468
Posted Friday, July 19, 2013 7:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:38 PM
Points: 35,858, Visits: 32,529
Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this (valid only in SQL Server 2012):
-- Current date 
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'



that method, while super easy to read and understand, is super slow.

using the FORMAT function on my laptop, the results are sorta bad....

  PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @BitBucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;

========== Format Methed of Conversion ==========

SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.


Wow! Unless SET STATISTICS is really putting the hammer on this like it does on Scalar Functions (see the following article on that subject), it looks like MS may have might have made a terrible mistake in their code. That's really too bad because a lot of people were really looking forward to this FORMAT functionality.

Hat's off to you for being one of those good people that actually tests the new stuff instead of just blindly using it. Hmmmm... I wonder if the formatting in SSIS has the same massive performance problem as this. It's ultra rare to find anyone that's doing performance tests in SSIS. I don't test in SSIS because I avoid using it altogether.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475476
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse