Bowling Challenge - The Results
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarr
-
The entry below was submitted about three hours after the challenge first appeared on the web site. It uses the Scores table as the table was originally defined (instead of a modified version), it seems to address the challenge more directly, it seems to pass all the tests, and the scoring algorithm (the stated focus) is much shorter. Could you please elaborate on the criteria that favored the winning entry over this one?
Thanks!
------
CREATE PROCEDURE BowlingChallenge
@user-id int
AS
SET NOCOUNT ON
DECLARE @RowID tinyint,
@Count tinyint,
@Frame tinyint,
@Score smallint,
@Value tinyint,
@Ball1 tinyint,
@Ball2 tinyint,
@Ball3 tinyint
CREATE TABLE #Pins (NumberOfPins tinyint, RowID tinyint IDENTITY(1,1))
INSERT #Pins -- small temporary table for code simplicity because a contiguous set of RowID values was not guaranteed
(NumberOfPins)
SELECT TOP 21
NumberOfPins
FROM Scores
WHERE UserID = @user-id
ORDER BY RowID
SET @Count = @@ROWCOUNT
SET @RowID = 1
SET @Frame = 0
SET @Score = 0
WHILE @Frame < 10 AND @RowID < @Count
BEGIN
SET @Frame = @Frame + 1
SET @Ball3 = NULL
SELECT @Ball1 = NumberOfPins FROM #Pins WHERE RowID = @RowID
SELECT @Ball2 = NumberOfPins FROM #Pins WHERE RowID = @RowID + 1
SELECT @Ball3 = NumberOfPins FROM #Pins WHERE RowID = @RowID + 2
SET @Value =
CASE
WHEN @Ball1 = 10 THEN @Ball1 + @Ball2 + @Ball3 -- strike
WHEN @Ball1 + @Ball2 = 10 THEN @Ball1 + @Ball2 + @Ball3 -- spare
ELSE @Ball1 + @Ball2
END
SET @RowID = @RowID +
CASE
WHEN @Ball1 = 10 THEN 1 -- strike
WHEN @Ball1 + @Ball2 = 10 THEN 2 -- spare
ELSE 2
END
SET @Score = @Score + @Value
IF @Ball1 + @Ball2 < 10 OR @Ball3 IS NOT NULL PRINT 'Frame: ' + STR(@Frame,2) + ' - Score: ' + STR(@Score,3)
END
DROP TABLE #Pins
RETURN (0)
Example data...
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 1, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 1, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 2, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 2, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 3, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 3, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 4, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 4, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 5, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 5, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 6, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 6, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 7, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 7, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 8, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 8, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 9, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 9, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10, 1)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10, 9)
INSERT INTO Scores (UserID, FrameNumber, NumberOfPins) VALUES (1, 10,10)
-
Including validation in the script earned extra points? That should deduct points, IMO. Data validation belongs in constraints, not in data access code. Yes, this was just a contest, but now it's posted as an example of a "good solution". Data validation in data access code is not a good solution and should probably be written up in one of the "worst practices" articles... As a matter of fact, I think I'll get to work on that right after I hit send on this message
--
Adam Machanic
whoisactive -
My Solution used 3 functions and 1 stored proc, used the original table. I added a game id for testing and presumed scores would be entered in order.. I know this is a year on but see below:-
CREATE FUNCTION FUN_BOW_getLastFrameScore
(
@userid INT,
@GameId INT,
@FrameNumber SMALLINT
)
RETURNS INT
AS
BEGIN
DECLARE @RetVal INT
SELECT @RetVal = SUM(NumberOfPins) FROM Scores WHERE FrameNumber = @FrameNumber - 1 AND UserId = @userid AND GameId = @GameId
RETURN ISNULL(@RetVal,0)
END
CREATE FUNCTION FUN_BOW_getLastFrameShots
(
@userid INT,
@GameId INT,
@FrameNumber SMALLINT
)
RETURNS INT
AS
BEGIN
DECLARE @RetVal INT
SELECT @RetVal = Count(1) FROM Scores WHERE FrameNumber = @FrameNumber - 1 AND UserId = @userid AND GameId = @GameId
RETURN ISNULL(@RetVal,0)
END
CREATE FUNCTION FUN_BOW_getNextShotValue
(
@userid INT,
@GameId INT,
@FrameNumber SMALLINT,
@NextShots SMALLINT
)
RETURNS SMALLINT
BEGIN
DECLARE @RETVAL INT
DECLARE @SHOTNUMBER INT
SELECT @SHOTNUMBER=MAX(ShotNumber)
FROM
(
SELECT
(SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber,
FrameNumber,
NumberOfPins
FROM
Scores S
WHERE UserId = @userid AND GameId = @GameId
) SN
WHERE FrameNumber = @FrameNumber
SELECT @SHOTNUMBER = ISNULL(@SHOTNUMBER,0)
IF (SELECT Count(1) FROM (SELECT (SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber, FrameNumber, NumberOfPins FROM Scores S WHERE UserId = @userid AND GameId = @GameId) SN WHERE ShotNumber BETWEEN @SHOTNUMBER + 1 AND @SHOTNUMBER + @NextShots) = @NextShots
SELECT @RETVAL = SUM(NumberOfPins) FROM
(
SELECT
(SELECT Count(1) FROM Scores WHERE RowId <= S.RowId) as ShotNumber,
FrameNumber,
NumberOfPins
FROM
Scores S
WHERE UserId = @userid AND GameId = @GameId
) SN
WHERE ShotNumber BETWEEN @SHOTNUMBER + 1 AND @SHOTNUMBER + @NextShots
ELSE
SELECT @RetVal = -1
RETURN ISNULL(@RetVal,0)
END
--USP_BOW_getUserBowlingGame 1,3
CREATE PROCEDURE USP_BOW_getUserBowlingGame
(
@userid INT,
@GameId INT
)
AS
--DECLARE @userid INT
--DECLARE @GameId INT
--SELECT @userid = 1, @GameId = 3
--Used whilst debugging Stored Procedure
SELECT
FrameNumber,
FinishedGo,
CASE WHEN FinishedGo = 1 AND (Pins = 10 AND Split > -1 AND Strike >-1 OR Shots >= 2) THEN --If they have finished the go display the score.
(
SELECT SUM(Score)
FROM
(
SELECT
FrameNumber,
CASE WHEN FrameNumber < 10 THEN --Normal bowling rules.
CASE WHEN SUM(NumberOfPins) < 10 THEN --Have not finished go yet.
SUM(NumberOfPins)
ELSE
CASE WHEN COUNT(FrameNumber) = 1 THEN --Strike.
CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) END
ELSE --Split
CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) END
END
END
ELSE
CASE WHEN Count(FrameNumber) < 2 THEN --If the Frame isn't finished.
0
ELSE
SUM(NumberOfPins) --We just want the number
END
END as Score
FROM
Scores
WHERE
UserId = @userid AND
GameId = @GameId
GROUP BY
FrameNumber
 
as BowlingSqWHERE FrameNumber <= Bowling.FrameNumber
 
ELSE
NULL
END
as RollingScore
FROM
(
SELECT
FrameNumber,
Count(FrameNumber) Shots, --Number of shots
SUM(NumberOfPins) Pins, --Number of Pins
dbo.FUN_BOW_getLastFrameScore(@UserId, @GameId, FrameNumber) LastScore, --LastScore
dbo.FUN_BOW_getLastFrameShots(@UserId, @GameId, FrameNumber) LastShots, --LastShots
dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) Split, --Score for a split.
dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) Strike, --Score for a strike.
CASE WHEN FrameNumber < 10 THEN --Normal Frame
CASE WHEN Count(FrameNumber) = 2 OR SUM(NumberOfPins) = 10 THEN 1 ELSE 0 END
ELSE
CASE WHEN SUM(NumberOfPins) >= 10 THEN --Strike or Spare in the 10th Frame
CASE WHEN Count(FrameNumber) = 3 THEN 1 ELSE 0 END
ELSE --Screwed up in the 10th
CASE WHEN Count(FrameNumber) = 2 THEN 1 ELSE 0 END
END
END FinishedGo,
CASE WHEN FrameNumber < 10 THEN --Normal bowling rules.
CASE WHEN SUM(NumberOfPins) < 10 THEN --Have not finished go yet.
SUM(NumberOfPins)
ELSE
CASE WHEN COUNT(FrameNumber) = 1 THEN --Strike.
CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,2) END
ELSE --Split
CASE WHEN dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) = -1 THEN 0 ELSE SUM(NumberOfPins) + dbo.FUN_BOW_getNextShotValue(@UserId,@GameId, FrameNumber,1) END
END
END
ELSE
CASE WHEN Count(FrameNumber) < 2 THEN --If the Frame isn't finished.
0
ELSE
SUM(NumberOfPins) --We just want the number
END
END as Score
FROM
Scores
WHERE
UserId = @userid AND
GameId = @GameId
GROUP BY
FrameNumber
) as Bowling
GO
Ive done tests on this and it works pretty well. I think loops should be shot dead.
-
Since the whole thing seems to be revamped, here's the solution I proposed one year ago.
It's pure set-based T-SQL, no cursor or structured instructions (the If's are there only for debug).
/********************************************************************************/
create procedure UserScore
/*
Created by Salvor for the SSC's Bowling Challenge
Purpose: A bowling score calculator
Usage:
exec UserScore @user-id -- show the current score for the user
exec UserScore @user-id,1 -- show the current score for the user with additional debug information
*/
@user-id int
,@Debug bit = 0
as
set nocount on
declare @UserScores table (
RowID int IDENTITY (1, 1) NOT NULL ,
FrameNumber tinyint NOT NULL ,
NumberOfPins tinyint NOT NULL
)
insert into @UserScores(FrameNumber,NumberOfPins)
select FrameNumber,NumberOfPins
from Scores
where UserID=@UserID
order by FrameNumber,RowID
if @Debug=1
begin
print 'User ' + cast(@UserID as varchar) + ' throws:'
select * from @UserScores
end
Declare @UnfoldedScores TABLE (
FrameNumber tinyint NOT NULL ,
NumberOfPins int NOT NULL ,
RecordType tinyint NOT NULL
)
insert into @UnfoldedScores (FrameNumber,NumberOfPins,RecordType)
select FrameNumber,NumberOfPins,0
from @UserScores
union all
select s2.FrameNumber, s1.NumberOfPins,1
from @UserScores s1
inner join (
select FrameNumber, max(RowID) RowID
from @UserScores
group by FrameNumber
having sum(NumberOfPins)=10 and count(*)=2
) s2 on s1.RowID=s2.RowID+1
union all
select s2.FrameNumber, s1.NumberOfPins,2
from @UserScores s1
inner join (
select FrameNumber, max(RowID) RowID
from @UserScores
group by FrameNumber
having sum(NumberOfPins)=10 and count(*)=1
) s2 on s1.RowID in(s2.RowID+1,s2.RowID+2)
If @Debug=1
begin
print 'User ' + cast(@UserID as varchar) + ' unfolded scores:'
print 'RecordType=0 -> Single throw score record'
print 'RecordType=1 -> Spare bonus record'
print 'RecordType=2 -> Strike bonus record'
print 'RecordType=3 -> Previous frame score record'
select FrameNumber,NumberOfPins,RecordType
from @UnfoldedScores
union all
select s1.FrameNumber,s2.NumberOfPins, 3
from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1 join
(select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber) s2
on s1.FrameNumber>s2.FrameNumber
end
select @user-id 'UserID', s1.FrameNumber 'Frame number',sum(s2.NumberOfPins) 'Running score'
from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1
inner join (
select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber
) s2 on s1.FrameNumber>=s2.FrameNumber
group by s1.FrameNumber
if @Debug=1
begin
select @user-id 'UserID', s.FrameNumber 'Frame number',s.NumberOfPins 'Single throw score',t.NumberOfPins 'Running score'
from @UserScores s
inner join (
select s1.FrameNumber ,sum(s2.NumberOfPins) NumberOfPins
from (select FrameNumber from @UnfoldedScores group by FrameNumber) s1
inner join (
select FrameNumber,sum(NumberOfPins) NumberOfPins from @UnfoldedScores group by FrameNumber
) s2 on s1.FrameNumber>=s2.FrameNumber
group by s1.FrameNumber
) t on s.FrameNumber=t.FrameNumber
end
GO
/********************************************************************************/
How it works.
The key concept of this algorithm is 'unfolding the user scores'.
The score of a frame but the last is given by:
the sum of the scores of the frame's throws
+
if the frame is a spare, the score of the next throw (that is the first of the next frame)
if the frame is a strike, the scores of the next two throws (which can belong to two different frames if the user scores another strike)
+
the scores of the previous frames (this could suggest a recursive approach, but I won't go that way)
For the last frame, both the spare bonus and the stryke bonus are just an extra throw for the same frame, so we only have:
the sum of the scores of the frame's throws
+
the scores of all the previous frames
As we will see, the very nature of the last frame is such that we won't need extra code to differentiate the algorithm's behaviour (it will just be a side-effect).
Let's begin thinking SQL.
To find 'the sum of the scores of the frame's throws' we'll have of course to group-by on the FrameNumber, summing the NumberOfPins; if the player scored a mark in the current frame, we could also insert the scores of the next frames as belonging to the current frame (that is, where the mark occurred), and the group-by would also include these bonus records. Likewise, if we also inserted the scores of the previous frames as scores of the current frame, the group-by would take care of everything. But, in the latter case there's a catch: the scores of the previous frames should already be inclusive of the bonuses for previous marks: this simply means that we will have to first insert the records for the bonuses, and then calculate and insert the records for previous frames' scores.
Let's see an example (of course, in the sp we won't modify the Scores table):
If the Scores table contained these records:
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 10
1 2 4
1 2 5
we would need to insert the following records:
insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,1,4) -- 1st strike bonus record
insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,1,5) -- 2nd strike bonus record
This way, the Scores table would look like this:
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 10
1 2 4
1 2 5
1 1 4
1 1 5
As you can see, if we grouped by FrameNumber, we'd obtain 19 as sum(NumberOfPins) for frame 1, thus we would need to insert this value for FrameNumber 2:
insert into Scores(UserID,FrameNumber,NumberOfPins) values(1,2,19) -- Previous frame score record
Now we'd have
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 10
1 2 4
1 2 5
1 1 4
1 1 5
1 2 19
Grouping by FrameNumber, we'll get:
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 19
1 2 28
Suppose now the the game goes on, and the player's situation is:
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 10
1 2 4
1 2 5
1 3 5
1 3 5
1 4 3
1 4 0
Ok, we now have 4 frames and a spare at frame 3 (and of course the strike at frame 1).
After inserting all the calculated records, we would get
UserID FrameNumber NumberOfPins
------ ----------- ------------
1 1 10 Frame 1 first throw score (strike)
1 2 4 Frame 2 first throw score
1 2 5 Frame 2 second throw score
1 3 5 Frame 3 first throw score
1 3 5 Frame 3 second throw score (spare)
1 4 3 Frame 4 first throw score
1 4 0 Frame 4 second throw score
1 3 3 Spare bonus record (first throw of spare 4 added to spare 3)
1 1 4 Strike bonus record (first throw of spare 2 added to spare 1)
1 1 5 Strike bonus record (second throw of spare 2 added to spare 1)
1 2 19 Frame 1 score added to frame 2
1 3 19 Frame 1 score added to frame 3
1 4 19 Frame 1 score added to frame 4
1 3 9 Frame 2 score added to frame 3
1 4 9 Frame 2 score added to frame 4
1 4 13 Frame 3 score (including spare bonus) added to frame 4
and so on...
In a way, we are unfolding the scores.
Now, how can we recognize spares and strikes?
Easy done: a frame scoring 10 with just one throw is a strike, with two throws is a spare.
In SQL this translates as:
--Strikes
select FrameNumber
from Scores
group by FrameNumber
having sum(NumberOfPins)=10
and count(*)=1
--Spares
select FrameNumber
from Scores
group by FrameNumber
having sum(NumberOfPins)=10
and count(*)=2
Once we've found spares and strikes, we'll need a way to find the subsequent throws that, as mentioned before, could belong to different frames if the user scores more strikes in a row. We'll see how to handle this in the code.
Now we can almost start coding, but first we need to make a few assumptions:
1. The process (or user) that feeds the Scores table is responsible for assuring that the data are correct/consistent: i.e. no more than two throws for each frame but the last, no more than three throws on the last frame but only if the player scores a strike or a frame, no more the 10 pins for each throw ecc... The script doesn't complain for wrong data, but of course the output will be also wrong (or better, will be consistent with the wrong input).
2. Since the Score table doesn't provide a way to identify a single game, we can assume that each player is assigned a new UserID for each game: that is, the UserID identifies both the player and the game which I'm asking the score of.
3. Since the Score table doesn't provide a way to identify a single throw (ok, at this point I could've modified the table to contain GameID and Throw number, but I'm too lazy sometimes), we can assume that, given the same FrameNumber, the record with lower RowID corresponds to the first throw. Knowing which is the first throw score is essential to calculate a spare bonus.
Ok, let's start coding.
First, we extract the scores of the user we're passing as input parameter, and put them in the table variable @UserScores.
declare @UserScores table (
RowID int IDENTITY (1, 1) NOT NULL ,
FrameNumber tinyint NOT NULL ,
NumberOfPins tinyint NOT NULL
)
insert into @UserScores(FrameNumber,NumberOfPins)
select FrameNumber,NumberOfPins
from Scores
where UserID=@UserID
order by FrameNumber,RowID
This way, we are sure that the user RowID's will be consecutive, which can not be said for those in Scores (where we have the scores of every user). Besides, we'll be dealing with a smaller structure than Scores, which could grow quite large. Lastly, should Scores' structure or its population logic change (see assumption 2. and 3.), we'll only need to modify this query to select tha data we need, not the following steps.
Now that the RowID's are consecutive, we can easily find the throws following a spare or a strike: we just need the RowID of the record where the mark occured, and then take the records RowID+1 (spare and strike) and RowID+2 (only for strike).
We can now make the first unfolding step: inserting the bonus records (for the sake of clarity and debugging purpose I've used a new table to store the unfolded scores, but these could as well be appended to @UserScores, thus avoiding the first UNION ALL).
Declare @UnfoldedScores TABLE (
FrameNumber tinyint NOT NULL ,
NumberOfPins int NOT NULL ,
RecordType tinyint NOT NULL
)
insert into @UnfoldedScores (framenumber,numberofpins,RecordType)
select framenumber,numberofpins,0 -- Insert single throw scores
from @UserScores
union all
select s2.framenumber, s1.numberofpins,1 -- Insert spare bonus records
from @UserScores s1
inner join (
select framenumber, max(rowid) rowid
from @UserScores
group by framenumber
having sum(numberofpins)=10 and count(*)=2
) s2 on s1.rowid=s2.rowid+1
union all
select s2.framenumber, s1.numberofpins,2 -- Insert strike bonus records
from @UserScores s1
inner join (
select framenumber, max(rowid) rowid
from @UserScores
group by framenumber
having sum(numberofpins)=10 and count(*)=1
) s2 on s1.rowid in(s2.rowid+1,s2.rowid+2)
The field RecordType is there for debugging purpose (see later).
But, wait! What about the last frame - usually the 10th, but we could imagine an extended game with more than 10 frames, and this query will still work - which we should avoid adding the bonus records for? Well, the query above already answers the question: in fact, since we are taking the max(RowID) of the frame where a mark occured, records RowID+1 and RowID+2 must belong to a subsequent frame. By its very nature, the LAST frame has no subsequent RowID's, thus it can't satisfy the join conditions. (As a matter of fact, this is true for each last frame you have in a game: that is, you can't calculate the bonus for a mark at the 4th frame until the 5th throw)
Let's go on to the second (and last) unfolding step: inserting the previous frames' scores and grouping the frames. Really, at this point it simply means to calculate the running totals for each frames, which can be done in a 'standard' way.
Also, instead of inserting the records and then selecting them back, I'm returning them back directly to the caller:
select s1.framenumber 'Frame number',sum(s2.numberofpins) 'Running score'
from (select framenumber from @UnfoldedScores group by framenumber) s1
inner join (
select framenumber,sum(numberofpins) numberofpins from @UnfoldedScores group by framenumber
) s2 on s1.framenumber>=s2.framenumber
group by s1.framenumber
Here, the last frame has no special treatment.
Well, we're done... almost.
We need to test the sp.
To use the sp, just insert the records into Scores (remember, the sp assumes the data are correct) and call the sp passing the UserID. The sp also accepts a second parameter @Debug which defaults to 0: with @Debug=0, the sp only shows the frames scores, with @Debug=1 the sp also shows the contents of the @UserScores table, the unfolded results, with a RecordType field describing the origin of each record, and a join between single throws (@UserScores table) and frames results.
And now a few test samples I ran:
-- 1) 2 players game:
truncate table Scores
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,1,2)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,1,6)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,1,10)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,2,4)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,2,5)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,2,9)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,2,1)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,3,5)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,3,5)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,3,9)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,3,0)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,4,3)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,4,0)
exec UserScore 1,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,4,6)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,4,2)
exec UserScore 2,1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,5,10)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,5,3)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,5,7)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,6,10)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,6,10)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,7,4)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,7,5)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,7,9)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,7,1)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,8,5)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,8,5)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,8,9)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,8,0)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,9,3)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,9,0)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,9,6)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,9,2)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1,10,10)
exec UserScore 1
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,3)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,7)
exec UserScore 2
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (2,10,10)
exec UserScore 2
-- 2) A perfect game:
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,1,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,2,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,3,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,4,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,5,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,6,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,7,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,8,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,9,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (3,10,10)
exec UserScore 3
-- 3) An extended game (20 frames)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,1,2)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,1,6)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,2,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,3,4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,3,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,4,9)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,4,1)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,5,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,5,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,6,9)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,6,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,7,3)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,7,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,8,6)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,8,2)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,9,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,10,3)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,10,7)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,11,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,12,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,13,4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,13,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,14,9)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,14,1)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,15,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,15,5)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,16,9)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,16,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,17,3)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,17,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,18,6)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,18,2)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,19,10)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,3)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,7)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (4,20,10)
exec UserScore 4
-- the EndOfArray test
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,1,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,2,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,3,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,4,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,5,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,6,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,7,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,8,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,9,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,1,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,2,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,3,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,4,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,5,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,6,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,7,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,8,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,9,0)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,2)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,8)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (5,10,10)
exec UserScore 5
Salvor -
temp tables... boo hoo.
-
It's not a temp table, it's a table variable
Salvor -
Sorry its a in-memory temp table until it get too big, then its stored in the temp database.
He he..
Looked at yours liked the way that you have made the game length flexible.. It would be quite simple to add that to mine.. Just gotta change some hard coded variables.
-
you're right, still it's conceptually different, e.g. table variables don't get involved in transactions.
Salvor -
It appears that some people tend to single out one small aspect of a T-SQL solution, an aspect they personally dislike, and use that to justify dismissal of the entire solution. Loops and temporary tables have been singled out in this topic, but I see these things (and many others) being singled out in other topics and in other forums. In most cases, the opinion being expressed makes it sound like the aspect being singled out is the most terrible coding construct one could ever implement. The same posts usually contain claims that the problem can be solved without using the coding construct being criticized. It seems like there is a masochistic glee to implementing solutions using massive and convoluted SELECT statements.
Unlike scoring rules for bowling, business requirements tend to change. When they do change any T-SQL code that implements them has to change as well. Which solution proposed in this topic would you prefer to maintain? The solution proposed by DBAdmin includes both a loop and a temporary table. Does that make the solution bad? Does that make the solution less efficient? I doubt it, but I have not done any testing. It looks a lot shorter and cleaner to me and I would choose to maintain that one if the performance characteristics are comparable to the others.
I'm not advocating the use of loops, temporary tables, cursors, dynamic SQL, or any other coding construct that gets portrayed as a scourge to T-SQL programming. I think all of these coding constructs, as well as complex SELECT statements, can be used appropriately with great success or inappropriately with dismal results. I think it's silly and sheepish to criticize the coding constructs themselves while ignoring the context. I think the quality of any particular solution is determined by how the coding constructs are applied.
-
This is a friendly community.
If your willing to post a solution you must be willing to take critism.
I never claimed that my solution was in fact 'THE BEST'.. Ive always taken the view personally why create temp tables and loops when a solution can be acheived without doing so.
In my opinion.. SELECT statements and FUNCTIONS are the lowest form of T-SQL for a DBA to administer.. If a DBA cannot under-stand or reverse engineer Functions or SELECT statements then I do believe it is time to hang up your hat and mosey on home.
Well commented / Documented T-SQL is never hard to maintain.
-
"If your willing to post a solution you must be willing to take critism."
I assume your statement applies to everybody.
I finally took the time to test your solution and the solution posted by DBAdmin. I started with the original table. I added GameID to the table and to the solution posted by DBAdmin, in order to accommodate your solution. I added a temporary table to the solution posted by DBAdmin to make it return a result set like your solution. I used the sample data supplied by DBAdmin. The two solutions produced the same results. I did not check for accuracy of the results. I assumed both solutions do the scoring correctly.
I tested each solution independently. I used identical code to make each solution score the sample data five times. I ran the code repeatedly to get a range of execution times. Here are the results:
Your solution took 7.55 to 7.65 seconds.
The DBAdmin solution took 0.06 to 0.07 seconds.
Are you willing to endure a 100+ times slower execution in order to avoid temp tables and loops?
I hope somebody else repeats these tests to verify my findings.
-
To be honest.. I don't give a flying poop.. Temp tables suck.
I presume you done some work with execution plans / indexes to make your findings fair ?
Or did you run the first sql statment on a duel zeon box and mine on a 386 ?
-
All tests were performed on the same server, in the same database, using the same table of sample data.
I would be happy to repeat the tests using any index(es) you think would help on a table with 21 narrow rows. I strongly suspect that the performance gap would widen as the row count increases.
I did not expect a reasoned, or even rational, reply from you. Your reply was neither. It's typical that those who engage in venomous criticism (as you have) react badly when they find their criticism proven very unfounded. It's typical that those who proclaim broad generalities (as you have) react badly when they find their beliefs and assumptions proven very misguided.
-
Why do temp tables "suck"?
Purely set-based solutions are not always the best in terms of performance. There is a time and place for everything -- even cursors have some utility. Don't drop something from your tool kit just because certain gurus want you to think you shouldn't use it.
--
Adam Machanic
whoisactive
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply