Wow !!!!!

You are 100% right Svein. Changing my query to


SELECT *
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef + 0 <= Note the + 0
WHERE so.EntityRef = 4
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND sl.CancelFlag <> 'T'

Speeds it up massively. I now get results faster than all my other
tests (even the fast ones !). I assume this means the index on
SupOrdrRef has too lower selectivity in this case? Can someone explain
whats going on here.


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Svein Erling Tysvær wrote:

>--- In firebird-support@yahoogroups.com, Robert martin wrote:
>
>
>>Hi All
>>
>>I have a Query that is basically
>>
>>SELECT a,b,c
>>FROM Table1 t1
>>JOIN Table2 t2 t2 ON t2.pk = t1.FK
>>WHERE t1.PK = 4
>>AND t2.CancelFlag <> 'T'
>>
>>The final and clause (t2.CancelFlag <> 'T' ) is really slowing the
>>query down. It takes 1.3s with and is near instantaneous without.
>>The CancelFlag field is a Char(1) field that is NOT indexed (because
>>it would have low selectivity). Is there anything I can do to speed
>>up this query?
>>
>>In the data I am testing the table sizes are...
>>t1 = 3100
>>t2 = 72000
>>
>>
>
>OK Rob, I want you to try two things to tame your query!
>
>First, try to eliminate parts of your query that may include indexed
>fields that you do not want to be part of any plan:
>
>SELECT a,b,c
>FROM Table1 t1
>JOIN Table2 t2 t2 ON t2.pk = t1.FK
>WHERE t1.PK = 4
>AND (2=0 or (<the rest of the where clause))
>
>Secondly, prevent the query from using two indexes on t1:
>
>SELECT a,b,c
>FROM Table1 t1
>JOIN Table2 t2 t2 ON t2.pk = t1.FK+0
>WHERE t1.PK = 4
>...
>
>If neither of these two suggestions help (even when taken together),
>then I'm stumped.
>
>HTH,
>Set
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>