June 24, 2008 at 9:27 am
Hi All,
Upfront, Thanks for any help offered!...Here's my situation, I am a little new to this stuff - I will try to keep this short:
I have several tables with student reponses from some tests. The goal is to return the student_id and a calculated score for the most recent test taken. I am currently using cursors (I know they are bad) that are nested (even worse) but I cannot conceive of another way to do this that isn't row by row. And if it has to be row by row can I do it without the use of cursors? What I am doing currently works, but it is very inefficient.
I have a students table which includes student_id
I have a testsTaken table which includes test_id and student_id
I have a questions table with question_id
I have a responses table with test_id and question_id and student_response
My sproc currently selects the most recent test_id for all students into a cursor (testsCursor), so I end up with a list of all tests that need to be scored. I then move through that cursor row by row and for each row I open another cursor (responsesCursor) which selects the student responses for the test_id in the current testsCursor row. I then loop through the responsesCursor comparing the response (provided by the student) to the "correct answer". From there I can calculate a score for the test. All of this goes into a temp table which is populated as I iterate through the nested cursors. This table is then returned in the final select stmt which includes the student_id, test_id, and calculated score.
Obviously this is not efficient. It has tolerable speed (which is probably still unacceptable) when it is a class of 20-30, but if we are trying to return results for an entire school, it takes forever (45 seconds for 1000 students (or tests).
Can anyone offer any advice - other than "get out while you can".
Thanks Again! I know there is a better way to do this...I have yet to figure it out.
June 24, 2008 at 9:45 am
Well, we need more info before we can really help. Besides seeing the code for you stored procedure, we also need to see the DDL (create statements) for your tables (include any indexes), sample data (in the form of insert statements that we can cut, paste, and execute to load your tables), and what the expected output from the store procedure should look like based on your sample data.
Here is a good article that should help you with all this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
😎
June 24, 2008 at 9:54 am
I will get to work on this...thanks!
Back shortly.
Bryan
June 24, 2008 at 10:03 am
Without actual table structure, I can't provide a full solution, but it seems to me you could pivot the columns per student-test into rows (1 row per question, per student, per test), then join that to a similar structure per test, by joining on student answer = correct answer.
A count function would then give you correct answers per student per test, which could be turned into a percentage very easily.
Here's one way to do that:
create table #StudentTests (
StudentID int not null,
TestID int not null,
DateTaken datetime not null default(getdate()),
Q1 int,
Q2 int,
Q3 int)
create table #Tests (
TestID int primary key,
Questions decimal(10,4),
Q1 int,
Q2 int,
Q3 int)
insert into #tests (testid, questions, q1, q2, q3)
select 1, 3, 1, 2, 3 union all
select 2, 2, 5, 6, null
insert into #studenttests (studentid, testid, datetaken, q1, q2, q3)
select 1, 1, '6/23/08', 1, 2, 3 union all
select 2, 1, getdate(), 1, 2, 3 union all
select 2, 2, getdate(), 5, 4, null union all
select 1, 2, getdate(), 5, 6, null
;with
TestsTaken (StudentID, TestID, Q, Answer) as
(select studentid, testid, 1, q1
from #studenttests
where datetaken >= cast(cast(cast(getdate() as float) as int) as datetime)
union all
select studentid, testid, 2, q2
from #studenttests
where datetaken >= cast(cast(cast(getdate() as float) as int) as datetime)
union all
select studentid, testid, 3, q3
from #studenttests
where datetaken >= cast(cast(cast(getdate() as float) as int) as datetime)),
Tests (TestID, Q, CAnswer) as
(select testid, 1, q1
from #tests
union all
select testid, 2, q2
from #tests
union all
select testid, 3, q3
from #tests),
CorrectAnswers (StudentID, TestID, Correct) as
(select studentid, teststaken.testid, count(*)
from teststaken
inner join tests
on teststaken.testid = tests.testid
and teststaken.q = tests.q
and teststaken.answer = tests.canswer
group by studentid, teststaken.testid)
select StudentID, correctanswers.TestID, correct/questions as Score
from correctanswers
inner join #tests
on correctanswers.testid = #tests.testid
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 24, 2008 at 2:04 pm
Thanks GSquared! Your code makes me think I really don't know what I am doing:w00t:. The structure is hitting me as unfamiliar. I got a little side tracked today after my post so I am getting back to this now I will spend some time with your post and see if that is a way for me to work.
I will also post the previously requested info....
Bryan
June 25, 2008 at 8:57 am
I probably should have commented/explained the code a bit better.
The first part is just creating some sample data to test the code on. I included a tests table, with correct answers stored in it, and a student-tests table, with the answers each student gave.
Next, I broke the tests down into columns, instead of rows. So question 1 is row 1, question 2 is row 2, and so on.
Then I did the same for each student. In that one, the complex looking part about getdate() is just to make sure I'm only grading tests taken today. It finds the beginning of the day, and then finds tests that were taken on/after that.
Then I did a simple join between student answers and correct answers, and determined how many correct answers each student got for each test.
After that, it's a simple matter of finding how many questions the test had (assuming not all tests have the same number of questions) and comparing that to the number of correct answers each student got, as a percentage. I'm assuming that's how you want to grade the tests.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply