B,

Don't hi-jack threads. Better start a new one.

Is there an index on SCHOOL?

Is there an index on PRIORITY?

Use the SQL92 JOIN syntax:

FROM student_selection s JOIN student_biodata b ON
(s.student_id=b.student_id)



With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com

> 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
> schools.
>
> 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 s.school='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?