Sorting 14000 records shouldn't normally neither take four minutes,
nor increase the time 60-fold, and I have no idea why it does so. Are
you sure that it takes only four seconds to return all records, and
not just the first few in the unsorted result set (can you e.g. go to
the end of the result set in four seconds)? Excepting the sort, the
plans seems identical to me - I guess one index on ACTORITEM.ITEMID
could be useful, but that should cause the same problems in both
queries and doesn't influence the difference in time.

Though possibly more importantly: Why do you use LEFT (OUTER) JOIN? If
there are ITEMS which doesn't exist in ITEMINFORMAT or ACTORITEM, then
fine - you have to use some OUTER JOINs. Otherwise, using simply using
(INNER) JOIN is normally a lot better for the optimizer.

HTH,
Set

--- In firebird-support@yahoogroups.com, "markd_mms" wrote:
> I'm having trouble with the following query. When I run the
> following it returns 14016 record in just over 4 seconds:
>
> SELECT ITEM.TITLE AS "Title", ITEM.TITLE2 AS "Title2",
> ITEMINFORMAT.CATALOGUENO AS "Catalogue Number",
> ITEMINFORMAT.BARCODE AS "Barcode", FORMATS.DESCRIPTION AS "Format",
> DISTRIBUTORS.DESCRIPTION AS "Distributor",
> GENRES.DESCRIPTION AS "Genre",
> ITEM.DISTRIBUTOR_RELEASE_DATE AS "Release Date", ITEM.ITEMID
> FROM ITEM
> LEFT JOIN ITEMINFORMAT ON ITEMINFORMAT.ITEMID = ITEM.ITEMID
> LEFT JOIN ACTORITEM ON ITEM.ITEMID = ACTORITEM.ITEMID
> LEFT JOIN ACTORS ON ACTORITEM.ACTORID = ACTORS.ACTORID
> LEFT JOIN FORMATS ON ITEMINFORMAT.FORMATID = FORMATS.FORMATID
> LEFT JOIN DISTRIBUTORS
> ON ITEM.DISTRIBUTORID = DISTRIBUTORS.DISTRIBUTORID
> LEFT JOIN GENRES ON ITEM.GENREID = GENRES.GENREID
>
> but as soon as I add
>
> ORDER BY ITEM.TITLE ASC
>
> the query takes over 4 minutes to run. There is an ascending index
> on ITEM.TITLE that I tried removing but it doesn't make a
> difference.
>
> Does anyone know why it could be making such a big difference?
>
> TIA
> Mark