Subject Re: [firebird-support] Another sloooow query
Author Svein Erling Tysvaer
At 10:46 26.06.2003 +0000, you wrote:
>Any idea to make the query run faster?

Well, the best advice would probably be to give us more information.
However, you do have IN (<subselect>), something which at least in older
versions used to slow down things. Try changing to

SELECT s.student_id, s.priority, s.is_selected, b.grade, b.birth_date
FROM student_selection s
JOIN student_biodata b ON s.student_id=b.student_id
/*In general, SQL-92 is preferable to SQL-89
WHERE s.priority<=2
AND'School A'
/* exclude students who already accepted in another school */
AND not exists(
SELECT * FROM student_selection ss
WHERE s.student_id=ss.student_id
AND ss.is_selected='T'
AND ss.priority<2
AND<>'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;

If IN (<subselect>) is the reason for your query being slow (though from
the information you have given us, those 18.22 seconds could include the
time needed to transfer 300000 records over the Internet with a poor
connection, so I'm not yet convinced it is slow), then you'll see execution
time drop drastically.