Hi,

as I see - there is no reason to use indexed reads for LOCATIONITEMS
table because engine needs to scan all records. Please post metadata of
used tables (at least indexes).

M.

Dne 22.6.2011 15:11, kerryneighbour napsal(a):

>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.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.
>
>

[Non-text portions of this message have been removed]