Subject RES: [firebird-support] Re: Problem slow sorting/distinct in Firebird
Author Fabiano
I think the problem is the use of "LCASE(something) like '% something%'"

When FB encounter a %% statement it will perform a full table scan. If you
have 10 statements like this (and you have much more) you have at least 10
full table scans to do the job.

First: Rewrite your SQL code and DO NOT USE no one %% statement. Check if
you have the correct indexes (tr.ItemType, tr.Marke1, tr.NODELEVEL,
tr.LINKTYPE) - Try one index for all these fields first.

When you finish you will have a very fast statement. The next step is create
concatenate all columns in a virtual one and do only one %% statement.

Istead of:

(LCASE(art.Artikelnummer) like '%a4 papier%') or
(LCASE(art.Artikelnr_leverancier) like '%a4 papier%')


(LCASE(art.Artikelnummer|| art.Artikelnr_leverancier) like '%a4 papier%')

In this case FB will do only ONE full table scan instead of 2 from the above

Sorry about my poor English, I hope you can understand my point of view. If
you need more help create a simple database with these tables populated
inside and publish on the internet and answer with the link to download.


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