--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

> Good to hear that my suspicions were wrong. The query you showed us should take a few seconds and absolutely not 7 minutes (unless we're talking huge blobs), show us the real query (with the plan and some information about the selectivity of indexes) and we'll see whether we can spot anything.
>

Sure. The main query
SELECT DISTINCT
LOCATIONITEMS.*,
ITEMTBL.ITEM_DESC,
ITEMTBL.ITEM_SHORT_DESC,
ITEMTBL.ITEM_IDEP,
ITEMTBL.ITEM_ISDP,
ITEMTBL.ITEM_IGRP,
ITEMTBL.ITEM_SUPP,
ITEMTBL.ITEM_DATE_DELETED,
ITEMTBL.ITEM_CTN_QTY,
ITEMTBL.ITEM_SIZEID as ITEMSIZEID,
ITEMTBL.ITEM_PARENT_NUMBER,
ITEMTBL.ITEM_CATEGORY1,
ITEMTBL.ITEM_CATEGORY2,
LOCATIONS.LOCATIONNUMBER,
LOCATIONS.LOCATIONNAME,
LOCATIONS.STOREID,
SYSCTBL.SYSC_COMPANY,
ITEMSIZES.SIZEDESCRIPTION as ITEMSIZE,
ITEMTBL1.ITEM_DESC AS PARENT_DESC,
ITEMSIZES1.SIZEDESCRIPTION as PARENT_SIZE
FROM
LOCATIONITEMS
LEFT OUTER JOIN ITEMTBL ON (LOCATIONITEMS.ITEMID = ITEMTBL.ITEM_NUMBER)
LEFT OUTER JOIN LOCATIONS ON (LOCATIONITEMS.LOCATIONID = LOCATIONS.ID)
LEFT OUTER JOIN SYSCTBL ON (LOCATIONS.STOREID = SYSCTBL.SYSC_NUMBER)
LEFT OUTER JOIN ITEMSIZES ON (ITEMTBL.ITEM_SIZEID = ITEMSIZES.SIZEID)
LEFT OUTER JOIN ITEMTBL ITEMTBL1 ON (ITEMTBL.ITEM_PARENT_NUMBER = ITEMTBL1.ITEM_NUMBER)
LEFT OUTER JOIN ITEMSIZES ITEMSIZES1 ON (ITEMTBL1.ITEM_SIZEID = ITEMSIZES1.SIZEID)
WHERE
(LOCATIONITEMS.ACTIVE_YN <>0)
AND (ITEMTBL.ITEM_ACTIVE_YN = 'Yes')
AND (ITEMTBL.ITEM_NONSTOCKTAKE_YN = 0 )
AND ((ITEMTBL.ITEM_PARENT_NUMBER IS NULL ) OR (ITEMTBL.ITEM_PARENT_NUMBER = '0') OR (ITEMTBL.ITEM_PARENT_NUMBER = ''))

Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (LOCATIONITEMS NATURAL, ITEMTBL NATURAL), LOCATIONS INDEX (ID)), SYSCTBL NATURAL), ITEMSIZES INDEX (ITEMSIZES_SIZEID)), ITEMTBL1 INDEX (ITEMTBL0)), ITEMSIZES1 INDEX (ITEMSIZES_SIZEID)))

------ Performance info ------
Prepare time = 47ms
Execute time = 7m 3s 797ms
Avg fetch time = 14,126.57 ms
Current memory = 46,280,892
Max memory = 92,409,076
Memory buffers = 3,000
Reads from disk to cache = 621
Writes from cache to disk = 0
Fetches from cache = 314,266,472

ITEMSIZES seems to be the only table that correctly uses and indexed lookup. All the others are using Natural lookups.

One 'problem' that might be an issue is that the foreign key is not always of the same TYPE. ie the ITEMID field is an integer that links to ITEMNUMBER (in the ITEMTBL) that is VARCHAR. It still holds a numeric value though, and is indexed. The other lookups do not have this issue though, but they don't work either.