Bowling Challenge - The Results

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    I didn't realise this would happen when I posted the solution.

    From what I can tell I didn't sl@g anyone off, Just pointed out about the use of temp tables and now I am being bullied.

    Even if my solution doesn't perform as well as the others, it should be put to the table at least as a proof of concept. 

    There is always more than one way to skin a cat,  to be fair I use temp tables all the time when Im working with remote data and have not removed them from my tool kit.

    It seems some people have become upset by my comments.  This was not the intention.

    On a final note.. Its nice to see people like Adam who do not take this kind of thing too seriously. 

    Thanks for taking the time to read my posts and examine my solution in great depth.  🙂

    I now know that using cursors and temp tables is the way forward

  • bwalker

    SSC Veteran

    Points: 286

    Thank YOU for proving my points.  You do it very well.

     

  • Salvor

    Ten Centuries

    Points: 1189

    I'm the one whom Gary first expressed his feelings about temp tables to, but since Gary and I had already come to an agreement (that is, I like them, he doesn't ), I refrained from joining the discussion.

    But now I have a question for Adam. Why don't you consider temp table as part of a purely set-based solution? I mean, they do represent sets, and can partecipate in set operation.

    Or did I get your words wrong?


    Salvor

  • Adam Machanic

    SSCoach

    Points: 15259

    Salvor,

    You're right, they are set-based. But when a lot of people talk about set-based solutions (see posts by certain "gurus"), they show disdain for temp tables because they're proprietary. As a result I've gotten into the habit of not considering them when I think of 100% declarative solutions. Which is probably a mistake on my part.

    --
    Adam Machanic
    whoisactive

  • GaryMcAllister

    SSC-Addicted

    Points: 411

    Adam can I also ask the overhead involved on large SQL Server databases which use lots of temp-tables?

    i.e. If the bowling site was to be used by 100,000's of concurrent users would SQL Server having to create temp-tables for every request have any adverse effects on performance ?

  • Salvor

    Ten Centuries

    Points: 1189

    Adam,

    I see your point. Maybe those 'gurus' should elevate their minds a little more. Temp tables are not proprietary per se, only the way you declare them is, and all major RDBMS give you a way to use them.

    Hey, I've been promoted to Rookie!


    Salvor

  • Adam Machanic

    SSCoach

    Points: 15259

    Possibly. There are both tembdb contention issues and recompilation issues to worry about.

    You can greatly reduce tempdb contention by following this KB article:

    http://support.microsoft.com/kb/328551

    As for the recompilation, you can use table variables instead of temp tables to get around that, but since they don't have statistics they won't perform well for larger sets of data. I don't think that would be a problem for the Bowling Challenge.

    --
    Adam Machanic
    whoisactive

  • Adam Machanic

    SSCoach

    Points: 15259

    I suppose the attitude is, if it's not in the Standard, it's proprietary

    --
    Adam Machanic
    whoisactive

  • DaveItz

    SSCrazy

    Points: 2146

    I threw this together when I first saw the article (Missed the "One Year Ago:" part of the message.)

    I used a pretty messy approach:

    CREATE PROCEDURE GetScores( @GameID int, @userid int)

    AS

    BEGIN

       SET nocount on

       SELECT GameID, UserID, FrameNumber,

              BallsPerFrame = Count(*),

              FirstBall = Min(RowID),

              SecondBall = Max(RowID),

              ScoreAdjustment = Sum(NumberOfPins),

              BonusAdjustment = Convert(int, 0),     --Didn't bother altering the table

              ScoredThisFar = Convert(int, 0)

       INTO #GameResults

       FROM scores

       WHERE GameID = @GameID

         AND UserID = @userid

       GROUP BY GameID, UserID, FrameNumber

       UPDATE #GameResults

       SET SecondBall = t2.FirstBall

       FROM #GameResults t

       LEFT JOIN (select * from #GameResults) t2 ON t2.FrameNumber = t.FrameNumber + 1

       WHERE t.FirstBall = t.SecondBall  

       UPDATE #GameResults

       SET BonusAdjustment = isnull(s1.NumberOfPins,0) + isnull(s2.NumberOfPins,0)

       FROM #GameResults t

       LEFT JOIN (select * from #GameResults) t2 ON t2.FrameNumber = t.FrameNumber + 1

       LEFT JOIN scores s1 on s1.RowID=t2.FirstBall

       LEFT JOIN scores s2 on s2.RowID=t2.SecondBall and t.BallsPerFrame=1

       WHERE t.ScoreAdjustment = 10

       UPDATE #GameResults

       SET ScoredThisFar = (select Sum(ScoreAdjustment)+ Sum(BonusAdjustment)

                            from #GameResults t2

                            where t2.FrameNumber <= #GameResults.FrameNumber)

       WHERE FrameNumber <= 10             

       SET nocount off

       SELECT case

              when FrameNumber <= 10 then

                  'Frame ' + Convert(char(2), FrameNumber)

              else ''

              end,     

              case

              when ScoreAdjustment = 10 then

                  case when BallsPerFrame=1 then 'Strike'

                       when BallsPerFrame=2 then 'Spare'

                  end +

                  case when FrameNumber <=10 then

                     ' (' + Convert(varchar(3), ScoredThisFar) + ')'

                  else ''

                  end           

              else

                  Convert(varchar(3), ScoredThisFar)

              end

       FROM #GameResults t

       ORDER BY FrameNumber

       --Implicitly dropping the temporary table. So, sue me.

    END

    I was actually appreciative of the fact that the contest winner didn't follow the rules of the contest as it was strictly laid out. It's what would normally happen when receiving a "this is what we need" request.

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply