Subject Re: Sloooow query
Author Bisma J
I'm making student selection automation application using Firebird as the
database. The task of the application is automatically obtain appropriate
students for a school based on student's grade and student's selection
priority for the school. There are 10 schools available to select by every
student and each student allowed to select more than one school by priority.
And there are about 300,000 students who want to be accepted in those

The "is_selected" field is marked by the application and if it is marked
(true) means the student accepted in the "school". I have to make sure that
my application only marks one row for every student selection data, because
a student cannot be accepted in more than one school.

To do the task, I have a query like this:

/* select students who select School A and the priority is 2 or below */
SELECT s.student_id, s.priority, s.is_selected, b.grade, b.birth_date
FROM student_selection s, student_biodata b
WHERE s.student_id=b.student_id
AND s.priority<=2
AND'School A'
/* exclude students who already accepted in another school */
AND s.student_id NOT IN
(SELECT student_id FROM student_selection
WHERE is_selected='T'
AND priority<2
AND school<>'School A')
/* sort grade from highest to lowest to make sure */
/* the school get first highest grade of the students */
ORDER BY b.grade DESC, b.birth_date;

The query execution took 18.22 seconds! For table "student_biodata", the
primary key is "student_id". And for table "student_selection", the primary
keys are "student_id" and "priority".

Any idea to make the query run faster?
For any kind of responses, I thank you in advance.