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 12345»»»

The T-SQL Quiz Expand / Collapse
Author
Message
Posted Monday, April 30, 2007 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 14,835, Visits: 27,311
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/2973.asp

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #362081
Posted Sunday, May 27, 2007 9:45 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
It's funny nobody, even author, mentioned an obvious solution:

SELECT
ISNULL(
NULLIF(
CASE WHEN Number % 3 = 0 THEN 'Bizz' ELSE '' END +
CASE WHEN Number % 5 = 0 THEN 'Buzz' ELSE '' END
, '')
, CAST(Number AS nvarchar(10) )
)
from master.dbo.spt_values
where type = 'P' AND Number > 0 AND Number < 100
order by Number

When you're doing databases you should not reference the same value twice in your query.


And not dropping table Nums is not bad.
Not having it before is bad.
And the way it's populated in the example is terribly wrong.
Post #369253
Posted Sunday, May 27, 2007 10:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489

Let's use a little logic. Any number divisible by both 3 and 5 will be divisible by 15. You don't need to check for both 3 & 5, you simply need to check for 15.

Declare @Counter int

Set @Counter = 1

While @Counter <= 100
  Begin
     Print Case When @Counter % 15 = 0 Then 'BizzBuzz'
           When @Counter % 5 = 0 Then 'Buzz'
           When @Counter % 3 = 0 Then 'Bizz'
           Else Str(@Counter)
     End

     Set @Counter = @Counter + 1
  End

 





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #369255
Posted Sunday, May 27, 2007 10:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:18 PM
Points: 19, Visits: 135

--Here's a set based solution.

Declare @n Table (i integer Not Null, Primary Key(i))
Insert @n
Select 0 as i Union All
Select 1 Union All
Select 2 Union All
Select 3 Union All
Select 4 Union All
Select 5 Union All
Select 6 Union All
Select 7 Union All
Select 8 Union All
Select 9

Select
 x,
 Case When x % 3 = 0 Then 'Bizz' Else '' End + Case When x % 5 = 0 Then 'Buzz'  Else '' End as tag
From
 (Select
  tens.i * 10 + units.i + 1 as x
 From
  @n units
 Cross Join
  @n tens
  as A
Order By
 x

 

 

Post #369256
Posted Sunday, May 27, 2007 10:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
I'm afraid you wouldn't make it to the 2nd round. If you're interviewing for a job, you're not going to already have that table in there. Your query would return an error, and when you tell them that it's their fault for not having the table there already, your interview is over.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #369258
Posted Sunday, May 27, 2007 10:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489

Interesting example, but fails to follow the directions. The directions is to substitute the value with the string, not provide it as a second value.





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #369262
Posted Sunday, May 27, 2007 10:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
1st of all - my query DOES NOT return an error.
Try before you say.

2nd, if I would include new table into my solution I would separate CREATE TABLE statement from the solution and prefix it with NOT EXIST check.

By the way, my solution takes less than 3ms. At least SQL Server cannot catch that time interval.
Post #369264
Posted Sunday, May 27, 2007 11:16 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 10:48 PM
Points: 4,570, Visits: 8,317
BTW, the question itself is so "front end"!

The whole nature of a professional database developer protests against mixing different datatypes in one column.
Post #369265
Posted Sunday, May 27, 2007 11:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Just a slight modification to one of the examples:

WITH Nbrs(n) AS (
   
SELECT 1
    UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 100)
SELECT CASE WHEN n%15 = 0 THEN 'BizzBuzz'
    WHEN n%3 = 0 THEN 'Bizz'
    WHEN n%5 = 0 THEN 'Buzz'
    ELSE CAST(n AS VARCHAR(8))
    END
FROM
Nbrs

Saying n is divisible by 3 and n is divisible by 5 is just another way of saying n is divisible by 15.  I liked this option best, and my solution would have been similar, although my first thought would have been to create a Numbers table  first, especially if I planned on doing this type of operation often -- although this method with the CTE works well too

Post #369268
Posted Monday, May 28, 2007 1:34 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300

(This is going to sound like a bit of a rant... and it probably is.   Sorry... )

Although Grant mentioned it in the end, there's a bit of irony in the article in that I wouldn't hire anyone who wrote such code as that shown in the article (simple as it was). 

Folks, getting the correct answer isn't enough... you have to do it with performance and scalability in mind and you must keep both in mind all the time.  Add code readability to that, as well.

Let's bump up the stakes a bit... lets use 1,000,000 rows for the same code...

First, Grant's code doesn't work, as is... it dies at 1,000 so it's NOT SCALABLE.  He wouldn't make it past the first round because he didn't write the code to be scalable despite what the requirements said (100 rows).  Here's the error his code produces...
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value '*' to a column of data type int.

Det's first attempt has a second fault that Grant missed altogether... it has a GO in it that just isn't required and causes the code to fail if you declare any timing variables before it.  He also should have done a SET NOCOUNT ON to keep from printing a million (1 row(s) affected) messages which really slowed his code down.

Det's second attempt isn't even worth mentioning because it would produce a million single row result sets, as Grant stated.

Let's look at a couple of other things... Scott made the mistake of formatting the output.  For the most part, formatting the output in SQL is a Bozo-No-No.  That type of formatting should be done in the app, if there is one.

Except for Det's first attempt, everyone took it at face value that the output should be printed instead of properly returned as a single usable result set.  The requirements never said Print anywhere... it said Count.  Ya gotta think about being able to use the output from any query as a single usable result set all the time.

John Chapman has the right idea but it'll take a bit more code to reach a million rows.  At least he tried a setbased solution! (Michael Valentine Jones has a dandy function that operates similar to John's performance enabled code but I can't find the URL for it).

Serqiy certainly has the right idea, but again, no scalability for the future.

I can't test the CTE solution because I don't have 2k5, but I'll just bet it's fast.

And... everyone failed the "test" because no one included any documentation!

After repairing Grant's code, here's the results from the million row tests (not truly scalable but certainly more than 100 rows )...

Grant's code took        203.626 seconds and pegged my single CPU the whole time.
Chris' code took          193.640 seconds and pegged my single CPU the whole time.
Det's first snippet took 286.156 seconds and pegged my single CPU the whole time.
Scott's code took        285.296 seconds and pegged my single CPU the whole time.

Those are some pretty hefty times for only a million rows.  So... how would I do this?  Like this, interview or not... would be even faster with MVJ's function...

/*************************************************************
 Test code requirements:
 1. Count from 1 to 100. (modified to use 1 million rows)
 2. For each number evenly divisible by 3, substitute 'Bizz'.
 3. For each number evenly divisible by 5, substitute 'Buzz'.
 4. For each number divisible by both substitute 'BizzBuzz'

 Revision History:
 Rev 00 - 05/28/2007 - Jeff Moden - Initial creation/unit test.
                     - Deviation: Added duration counter to
                       measure run duration.
 Rev 01 - 05/28/2007 - Jeff Moden - Modified to use 1 million
                       rows for performance/scalability test.
*************************************************************/
--===== Declare and start the duration timer
DECLARE @StartTime DATETIME
    SET @StartTime = GETDATE()

--===== Declare and set a variable to control the number of
     -- rows created.
DECLARE @DesiredRowCount INT
    SET @DesiredRowCount = 1000000

--===== Disable the auto-display of rowcounts for appearance
     -- and speed
    SET NOCOUNT ON

--===== If the scratchpad table already exists, drop it
     -- Note this is just so you can rerun the test.
     IF OBJECT_ID('TempDB..#Nums','U') IS NOT NULL
        DROP TABLE #Nums

--===== Limit the number of rows to be built
    SET ROWCOUNT @DesiredRowCount

--===== Create and populate the table on the fly
  SELECT IDENTITY(INT,1,1) AS RowNum,
         CAST(NULL AS VARCHAR(10)) AS DesiredResult
   INTO #Nums
   FROM Master.dbo.SysColumns sc1 WITH (NOLOCK),
        Master.dbo.SysColumns sc2 WITH (NOLOCK)

--===== Restore the number of rows to return to normal
     -- Note: Can replace with TOP @variable in SQL 2k5

    SET ROWCOUNT 0

--===== Produce the desired results according to the
     -- requirements in the code header.  Avoids concatenation which
     -- would cause the code to run a second slower...

 SELECT CASE
             WHEN RowNum % 15 = 0 THEN 'BizzBuzz' --Divisible by 15
             WHEN RowNum %  3 = 0 THEN 'Bizz'     --Divisible by 3
             WHEN RowNum %  5 = 0 THEN 'Buzz'     --Divisible by 5
             ELSE CAST(RowNum AS VARCHAR(10))
        END AS DesiredResult
   FROM #Nums
  ORDER BY RowNum

--===== Display the run duration (not part of result set)
  PRINT DATEDIFF(ms,@StartTime,GETDATE())

... the total run time is 8.610 seconds (NOT a type-o... less than 9 seconds and a good 20 times (2000%) faster than the fastest loop code) and, yes, it pegged my single CPU... but what would you rather have... a pegged CPU for 3+ minutes or 9 seconds?  Speed is important and RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") in the form of single row loops and cursors should be avoided at all cost.  There are a small handful of exceptions for loops in functions, but for the most part, they are an evil thing.



--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #369278
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse