Guten Tag Myles Wakeham,

am Sonntag, 5. September 2010 um 00:05 schrieben Sie:

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

This is a matter of execution order of the statements
your query will just get the first 20 record in natural order no
sorting necessary

an ORDER BY will precede the FIRST
so all 150000 records will be processed ,
then sorted
then the first 20 sent

to speed it up with an order by you shud consider a where clause to
reduce the subresult which needs to be sorted


--
Mit freundlichen Grüssen
Hannes Streicher mailto:HStreicher@...