Optimizations

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