Bowling Challenge - The Results
-
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
-
Thank YOU for proving my points. You do it very well.
-
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 -
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 -
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 ?
-
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 -
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 -
I suppose the attitude is, if it's not in the Standard, it's proprietary
--
Adam Machanic
whoisactive -
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, @user-id 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 = @user-id
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 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply