I use the LIKE comparison in many of my queries. This is unoptimized, so
I was wondering if there is some trick to do the same thing without using
LIKE?

ie I want to do a search (in string fields) for something like  

somefield LIKE 'bris%'     (even somefield LIKE '%bris%')

This a large database, this query is way too slow.


can I do something similar to

upper(somefield) >= upper('bris')
and
upper(somefield) <= upper('brisZ')

This query seems to give me the correct results (or near enough), but this
is not optimised either. it is not very fast either, of course.

Rather than reinvent the wheel, I was wondering if there were some clever
answers to this problem out there?

Kerry

You can use an indexed field and select record using TextSearch.
This is very fast but you can define each field you want to be indexed.

You can use:
select <some fields>  from table where textsearch('bris*' in field1)

Eduardo

Eduardo,

is it also possible to use the wilcard to "both sides" :

select <some fields>  from table where textsearch('*bris*' in field1)

as an alternative for

LIKE %abc%


regards

Erik



"Jose Eduardo Helminsky" <contato@hpro.com.br> schreef in bericht
news:0D05A87A-F98C-4AAA-8190-B75BE6049E74@news.elevatesoft.com...
> Kerry
>
> You can use an indexed field and select record using TextSearch.
> This is very fast but you can define each field you want to be indexed.
>
> You can use:
> select <some fields>  from table where textsearch('bris*' in field1)
>
> Eduardo
>

Erik,

<< is it also possible to use the wilcard to "both sides" : >>

No, unfortunately it can only use trailing wildcards.

--
Tim Young
Elevate Software
www.elevatesoft.com

>
> You can use:
> select <some fields>  from table where textsearch('bris*' in field1)
> Eduardo
>

Ok - a good idea. I tried this out and it is no faster that using LIKE. In
fact, it is slightly slower, but not by much. Since you also have the drawback
of having to add the field to textsearch, and the fact that you cannot use
a leading wildcard, makes the use of Textsearch not much use in simple text
fields.

I use textsearch ONLY in memo fields, where you have no choice, or very large
text fields (ie 100 characters or so).

I have found, that BY FAR, the best trick to speed up your queries is to
use the TOP xx command. In my particular application, the user only wants
a read-only snapshot, and usually is only trying to find a very few records.
In fact, usually only 1 or 2. So I can limit my queries to returning only,
say 10, 100 or 1000 at most, records, then the results of searching even
a 100,000 record table is only a second or so.

even such a dog of a WHERE like this

WHERE UPPER(file.filenumber) LIKE upper('%bris%)
ORDER BY file.id
TOP 10

returns records pretty fast. in fact, in my test query on 100,000 records,
it returns in 0 seconds! That is what DBSYS says, anyway. You cannot get
much fast than 0 seconds! No doubt this is due to caching, but it is very
fast nonetheless.

There are actually 33 seconds that match this criteria in the table, and
a full run, with no TOP, takes 22 seconds.

Kerry

<<
Ok - a good idea. I tried this out and it is no faster that using LIKE. In
fact, it is slightly slower, but not by much. Since you also have the
drawback of having to add the field to textsearch, and the fact that you
cannot use
a leading wildcard, makes the use of Textsearch not much use in simple text
fields.
>>

Are you sure you make the field (memo) indexed ?

Just remember, if you don´t have an indexed field DBISAM will use brute
force to perform the search and this indeed is very slow.

In my apps the speed is very good.

Eduardo

Yes, I did index the textsearch field(s) in question. It took ages, in fact
(an hour or so??).  

>
> Are you sure you make the field (memo) indexed ?
>
> Just remember, if you don´t have an indexed field DBISAM will use
> brute force to perform the search and this indeed is very slow.
>
> In my apps the speed is very good.
>
> Eduardo
>

Kerry,

<< Yes, I did index the textsearch field(s) in question. It took ages, in
fact (an hour or so??).   >>

If you post the query plan, we can at least see if the TEXTSEARCH() function
is actually using an index.  That's the most important item in terms of
optimization.  And yes, text indexes in DBISAM can take a while to build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

> If you post the query plan, we can at least see if the TEXTSEARCH() function
> is actually using an index.  That's the most important item in terms of
> optimization.  And yes, text indexes in DBISAM can take a while to build.

Is like using a index ?

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr

Michael,

<< Is like using a index ? >>

Not in DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com