--- In firebird-support@yahoogroups.com, Myles Wakeham wrote:

> 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?

As Hannes wrote, the lack of a WHERE clause is the most likely cause, I'm sure that if you added WHERE JOB.JOB_ID < 1000, then you would get the result a lot quicker. I'm uncertain whether it can be improved without a WHERE clause, but show us the entire statement including both the ORDER BY clause and the PLAN, and then it might be possible to work out a modified statement that you may try (just in case the optimizer doesn't get it right). I'd say 150000 records is not all that much (well, unless each row contains huge blobs or you JOIN in a way that multiplies the number of rows returned).

Set