locked
xml data type versus several records saving the student answers to questions in a quiz RRS feed

  • Question

  • Which is better to save student answers to questions in quizzes

    I am looking for best performance and datastorage space. I do not want the student to get timed out while working on essay questions

    the quiz will include lots of questions some muliple choice and others essay

    Is it better to keep the answer of each question in a separate record or use xml data type to store all the answers for all the questions in a quiz in one xml field  and if I used xml data type is it easy and fast to update the answer for a particular qroup of questions at once i.e page by page

    or better to do it on separate records and save the answers in each record

    I have a huge huge number of students quizzes and questions

    CREATE TABLE [Quiz_Definition].[quiz_attempts_answers](
     [Quizid] [int] NOT NULL,
     [attemptid] [int] NOT NULL,
     [studentid] [int] NOT NULL,
     [layoutseq] [nvarchar](4000) NULL,
     [Answers] [xml] NULL,
     [timemodified] [datetime] NULL,
     CONSTRAINT [PK_quiz_attempts_answers_1] PRIMARY KEY CLUSTERED
    (
     [Quizid] ASC,
     [attemptid] ASC,
     [studentid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    where
    layoutseq is a list of questions in the order the student saw in this attempt
    answers is questionid and studentanswer it includes all questions and answers the student saw in this attempt

    vs for essay question
    CREATE TABLE [Quiz_Definition].[Quiz_Essay_Attempt_Answers_201223](
     [QuizID] [int] NOT NULL,
     [StudentID] [int] NOT NULL,
     [SectionID] [int] NOT NULL,
     [AttemptID] [int] NOT NULL,
     [QuestionID] [bigint] NOT NULL,
     [AnswerText] [nvarchar](max) NOT NULL,
     [TimeModified] [datetime] NOT NULL,
     [TimeCreated] [datetime] NOT NULL,
     [ModifiedBy] [int] NOT NULL,
     [Score] [decimal](8, 2) NOT NULL,
     CONSTRAINT [PK_Quiz_Essay_Attempt_Answers_201223] PRIMARY KEY CLUSTERED
    (
     [QuizID] ASC,
     [StudentID] ASC,
     [SectionID] ASC,
     [AttemptID] ASC,
     [QuestionID] ASC)

    and the following for multiple choice questions

    CREATE TABLE [Quiz_Definition].[Quiz_MC_Attempt_Answers_201223](
     [QuizID] [int] NOT NULL,
     [StudentID] [int] NOT NULL,
     [SectionID] [int] NOT NULL,
     [AttemptID] [int] NOT NULL,
     [QuestionID] [bigint] NOT NULL,
     [AnswerSeq] [nvarchar](100) NOT NULL,
     [TimeModified] [datetime] NOT NULL,
     [TimeCreated] [datetime] NOT NULL,
     [ModifiedBy] [int] NOT NULL,
     [Score] [decimal](8, 2) NOT NULL,
     CONSTRAINT [PK_Quiz_Attempt_Answers] PRIMARY KEY CLUSTERED
    (
     [QuizID] ASC,
     [StudentID] ASC,
     [SectionID] ASC,
     [AttemptID] ASC,
     [QuestionID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    sz

    Sunday, November 18, 2012 2:34 PM

Answers

All replies

  • You have to do some testing.

    XML columns can be indexed as well for good performance.

    In addition to the DDL, can you post the queries also?


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Sunday, November 18, 2012 9:02 PM
  • I am still in the design phase I did not do the queries yet

    But my question is 

    If I am going to store in the xml

    <quiz>

    <question1>

    <answer>

    xxxxxx

    </answer>

    </question1>

          <question2>

    <answer>

           </answer

    </question2>

    etc. 

    updating and inserting answers for questions in the quiz is it faster if I used xml datat type to store all the questionids and their answers for a quiz

    or using a record for each question's answer

    as mentioned above 

    Thanks


    sz

    Sunday, November 18, 2012 10:05 PM
  • You can only answer that question after optimization. XML can be optimized as well. A database expert can make it "fly" faster than a speeding bullet both ways.

    Generally, narrow tables with fixed colums are the best performing.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by Semika Sunday, November 18, 2012 11:43 PM
    Sunday, November 18, 2012 11:14 PM
  • Thank you

    sz

    Sunday, November 18, 2012 11:43 PM