Hi, this is not true that FB use 10 full table scans it use only one table scan and check 10 combination of fields
try this on emplooye.fdb database SELECT * FROM CUSTOMER r where r.CUSTOMER like '%S%' 15 seq reads and then try SELECT * FROM CUSTOMER r where r.CUSTOMER like '%S%' or r.CUSTOMER like '%B%' or r.CUSTOMER like '%M%' you got the same 15 seq reads if you you notice more reads, then this is an error and you should report it into fbtracker Karol Bieniaszewski --- In [email protected], "Fabiano" <fabianoaspro@...> wrote: > > 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. > Example: > > > > Istead of: > > (LCASE(art.Artikelnummer) like '%a4 papier%') or > (LCASE(art.Artikelnr_leverancier) like '%a4 papier%') > > > > Use: > > (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 > example. > > > > 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. > > > > Fabiano. > > > > [Non-text portions of this message have been removed] >
