We run a student scoring system with PostgreSQL as a backend. After the results
for each student are inputted into the system, we display many reports for
them. We haven't had a problem with efficiency or speed, but it has come up
that perhaps storing the rolled up scores of each student may be better than
calculating their score on the fly. I have always coded the SQL to calculate on
the fly and do not see any benefit from calculating on the fly. For a test with
over 100 questions and with 950 students having taken it, it calculates all
their relevant score information in less than half a second. Would there be any
obvious benefit to caching the results?
I would greatly appreciate any thoughts on this.
Here is the structure:
A database to store and calculate student results.
Information about the student and which test they took:
\d test_registration;
Table "public.test_registration"
Column | Type | Modifiers
---------------------+-----------------------------+------------------------
id | uuid | not null
sid | character varying(36) | not null
created_date | timestamp without time zone | not null default now()
modified_date | timestamp without time zone | not null
test_administration | uuid | not null
The actual results (what the student marked):
\d test_registration_result (linked to test_registration.id above)
Table "public.test_registration_result"
Column | Type | Modifiers
-------------------+-----------------------+-----------
test_registration | uuid | not null
question | uuid | not null
answer | character varying(15) |
\d question (information on each question)
Table "public.question"
Column | Type | Modifiers
-------------------+------------------------+---------------
id | uuid | not null
test | uuid | not null
question | integer | not null
weight | double precision |
\d question_answer (the answers for the question)
Table "public.question_answer"
Column | Type | Modifiers
----------+-----------------------+-----------
question | uuid | not null
answer | character varying(15) | not null
With a SQL query:
SELECT sid, raw_score, weighted_score, number_questions, total_weights,
( weighted_score / total_weights ) * 100.00 as mp_percentage,
total_weights
FROM
(
SELECT
tr.sid as sid,
sum (
(
SELECT (case when a.answer = r.answer then 1 else 0 end
)
)
) as raw_score,
sum (
(
SELECT (case when a.answer = r.answer THEN q.weight
end )
)
) as weighted_score,
.....
For 953 students on a test with 145 questions, this takes less than half a
second to calculate. Is is worth storing the score?
\d score_set
Table "public.score_set"
Column | Type | Modifiers
------------------------+-----------------------------+------------------------
id | uuid | not null
sid | uuid | not null
test_registration_id | uuid | not null
test_administration_id | uuid | not null
score | double precision | not null
Will it be much faster? I know more storage will be needed.
Thank you
Ogden
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
