I have a reasonably large database in Firebird 1.5 (about 150,000 rows
in the main table), and I'm trying to issue the following query:

SELECT FIRST 20 SKIP 0 JOB.JOB_ID,
CLIENT.CLIENT_NAME,
CLIENT_SITE.SITE_DESC,
JOB.ENTERED_TS,
JOB_STATUS.JS_NAME,
CHARGE_TYPE.CT_NAME,
JOB.JOB_DESC_SHORT,
CLIENT.CLIENT_ID,
JOB.TECH_ALLOC_DISPLAY,
JOB.HAS_WIP,
JOB_STATUS.ICON_URL
FROM JOB
INNER JOIN JOB_PRIORITY ON (JOB.FK_JP_ID = JOB_PRIORITY.JP_ID)
INNER JOIN CLIENT ON (JOB.FK_CLIENT_ID = CLIENT.CLIENT_ID)
INNER JOIN JOB_STATUS ON (JOB.FK_JS_ID = JOB_STATUS.JS_ID)
INNER JOIN CHARGE_TYPE ON (JOB.FK_CHARGETYPE_ID =
CHARGE_TYPE.CT_ID)
LEFT OUTER JOIN CLIENT_SITE ON (JOB.FK_SITE_ID =
CLIENT_SITE.SITE_ID)
LEFT OUTER JOIN JOB_TECH ON (JOB.JOB_ID = JOB_TECH.JOB_ID);

All related tables are joined with foreign keys, and I've checked that
all are in place correctly. Execution is about 51ms, according to the
plan analyzer, so that's great.

The problem is when I add an ORDER BY clause to this query. If I make
it really simple and just ORDER BY the primary key of the JOB table
(JOB.JOB_ID), it slows the entire query down to 7.5 seconds.

Does anyone have any suggestions as to why it would do this, and what I
might be able to do so that I can find out where the bottleneck is with
this query?

Myles
--
-----------------------------
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
www.techsolusa.com
Phone +1-480-451-7440