Some opinions if you have the time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Some opinions if you have the time

Author

Topic

taylo
Yak Posting Veteran

82 Posts

Posted - 2001-08-08 : 16:49:32

I almost posted this earlier in the week but I decided to get it working first.

I have an online quiz which randomly (Mandatory) generates a different question option for each question. Each question has 15 options.
So question 1 could be any 1 of 15 options.
Question 2 could be any 2 of 15 options.
Question 3 could be any 3 of 15 options.
etc..
A user must see all 15 options before they see the same option twice. .

This is what I did

TempTableA holds all options for the question
Temp TableB holds all the options that have already been presented to the user
TempTable3 holds TempTableA minus TempTableB

The reason for the 3rd table is the randomization which needs an auto number field.

On average this method presents a new question in 2.5 seconds. But I have had it take up to 7 seconds. (I have roadrunner)
But I am concerned that under heaver traffic this will slow down even more. If it wasn't for the randomization this would be far easier but the randomization must stay.

I have included a snippet of code below that I customized
for question 1 just so you can see it if you like.
So what do you think?

Rob

CREATE TABLE #TempTable
(
QID numeric(10),
idNum int identity(1,1)
)
INSERT INTO #TempTable Select QID From BucketQs where Question = 1

Create Table #TempTableB
(
QID numeric(10),
idNum int identity(1,1)
)
INSERT INTO #TempTableB (QID) (Select QID From UsedAnswers where UserID = @user and Question = 1)

WHILE EXISTS(Select QID From #TempTableB)
begin
Select @holdID = QID from #TempTableB
Delete from #TempTable where QID = @holdID
Delete from #TempTableB where QID = @holdID
End

Create Table #TempTableC
(
QID numeric(10),
idNum int identity(1,1)
)
INSERT INTO #TempTableC (QID) (Select QID From #TempTable)
Select @nRecordCount = Count(*) from #TempTableC

If (@nRecordCount = 1)
begin
Select @Q = QID from #TempTableC
end
else
begin
Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0)
Select @Q = QID from #TempTableC where idNum = @nRandNum
Update QuizRow set Q1 = @Q WHere UserID = @user
End

INSERT INTO UsedAnswers(Question,UserID,QID) VALUES(1,@user,@Q)
Select Q from BucketQs Where QID = @Q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources