>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)))
>
>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.

Ouch, three NATURALs!

I've never tried joining on fields of different types. The first thing I would suggest, would be to change type of JOIN, replace

LEFT OUTER JOIN ITEMTBL ON (LOCATIONITEMS.ITEMID = ITEMTBL.ITEM_NUMBER)

with

JOIN ITEMTBL ON (LOCATIONITEMS.ITEMID = ITEMTBL.ITEM_NUMBER)

The reason for changing this, is that the optimizer gets more choice with an inner join than an outer join and that in reality you've implicitly turned this into an inner join already through your use of ITEMTBL in the WHERE clause. Also, check whether the other tables are truly LEFT JOINed to the other tables or whether they can be replaced by inner JOIN.

I don't know whether this will help you performancewise, if the plan doesn't change, the next thing to try is to do an explicit type cast in your join clause. With the plan you quoted above I'd try CAST(LOCATIONITEMS.ITEMID as VarChar(xx)), if ITEMTBL was the first table in the plan, I'd rather do CAST(ITEM_NUMBER as Integer). I've no idea why SYSCTBL doesn't use an index. Is it a very small table (if so, it is OK), or isn't SYSCTBL.SYSC_NUMBER indexed or with lousy selectivity?

HTH,
Set