i've got a school-like setup going (7.0.3potato / debian with
PLPGSQL as the language) and i'm wondering if the seasoned folks
here could shed some light on making this a bit more
responsive--i can see where redundant lookups/updates are made,
but i don't know how to do it in one fell swoop, or if it's
possible to use cursors for this:
table 'ratings' links to tables
course
prof
and 'course' links to
topic
and 'topic' links to
school
also, 'prof' links to
person
and for each of those linked-to tables, i'd like to propagate a
"sum total counter" reflecting the feedback for items in that
table:
professor plum teaches course 'mystery101' at clue-free U.
if student 'scarlet' gives him an 'A' for his excellent
teachings in that course, then
update prof set a=a+1 where id='plum';
update person set a=a+1 where id='plum';
update course set a=a+1 where id='mystery101';
update topic set a=a+1 where id='mystery';
update school set a=a+1 where id='clue-free U';
but of course it's not that straightforward, since the tables
inter-link:
<given profID>
<given courseID>
select id into topicID from topic
where course.id=courseID and course.topic=topic.id;
select id into schoolID from school
where topic.id=topicID and topic.school=school.id;
select id into personID from person
where prof.id=profID and prof.person=person.id;
update course set a=a+1 where id=courseID;
update topic set a=a+1 where id=topicID;
update school set a=a+1 where id=schoolID;
update prof set a=a+1 where id=profID;
update person set a=a+1 where id=personID;
i'm thinking: okay, since the WHERE lookups already positioned
some row pointer (cursor?) right where we want it, can we just
use that pointer without having to iterate through another WHERE
clause in the update?
<given profID>
<given courseID>
update course set a=a+1
where id=courseID;
update topic set a=a+1
where course.id=courseID and course.topic=topic.id;
-- since school is two links away, we need this mess:
update school set a=a+1
where course.id=courseID and course.topic=topic.id
and topic.school=school.id;
-- and that double-lookup is costly!
update prof set a=a+1
where id=profID;
update person set a=a+1
where prof.id=profID and prof.person=person.id;
the way it is now, each additional record that gets added to the
ratings table, takes an EXPONENTIALLY longer time to insert, as a
result.
any optimizability suggestions? (maybe keep some date/time stamp
field in another table and propagate these kinds of things after
hours?)
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
