Hi!

I need to do a partial text search on a table with +300.000 records with C/S
Using Locate to find a record by its full field value is really fast, but
filtering records which contains a partial word takes about 45 seconds on a
local network (using C/S, not shared folder).

Which is the best practices and recommendations for the table structure
(index type, compression, case-ins, etc...) and the fastest search method
(Locate, Filter, FindFirst, etc...)?

Thanks!

Luis C.

Luis


Have a look at TEXTSEARCH, plus there's been a couple of threads (in the ElevateDB groups) about speeding up partial word searches.

Roy Lambert [Team Elevate]

Hi, Roy...

I have tried TEXTSEARCH, but it seems it doesn't allow partial matches.
With LIKE I can do something like:

   DESCR Like '%TRO%'

....and it returns all records that contains the word 'TRO', at the
beginning, end or in the middle of the text.

However, with TEXTSEARCH, if I do something like:

   TEXTSEARCH('*TRO*' IN DESCR)

....it returns no records at all.

Is it possible to use wildcards with TEXTSEARCH to find records that
contains that word in any part of the text?

Thanks!


"Roy Lambert"  escribió en el mensaje de
noticias:6C792F0E-4C6C-454B-90E9-5CBF92D46644@news.elevatesoft.com...

Luis


Have a look at TEXTSEARCH, plus there's been a couple of threads (in the
ElevateDB groups) about speeding up partial word searches.

Roy Lambert [Team Elevate]

Luis

>Is it possible to use wildcards with TEXTSEARCH to find records that
>contains that word in any part of the text?

Not as such, but what can be done is to write a UDF which will generate all partial words. The index becomes larger but it does give rapid search facilities.

Have a hunt through the newsgroups and you'll find the threads I mentioned. They should give you some idea.

Roy Lambert [Team Elevate]

Reading newsgroups, I found this from Tim Young:

   Not currently, no. It is something that we are investigating for
ElevateDB
   at some point, and then it might be back-ported into DBISAM.

   (http://www.elevatesoft.com/forums?action=view&category=dbisam&id=dbisam_sql&page=1&msg=12212#12212)

Are there any news about this? Or any other solution?

Thanks!



"Roy Lambert"  escribió en el mensaje de
noticias:6C792F0E-4C6C-454B-90E9-5CBF92D46644@news.elevatesoft.com...

Luis


Have a look at TEXTSEARCH, plus there's been a couple of threads (in the
ElevateDB groups) about speeding up partial word searches.

Roy Lambert [Team Elevate]

Luis

> Not currently, no. It is something that we are investigating for
>ElevateDB
> at some point, and then it might be back-ported into DBISAM.
>
> (http://www.elevatesoft.com/forums?action=view&category=dbisam&id=dbisam_sql&page=1&msg=12212#12212)
>
>Are there any news about this? Or any other solution?

Firstly Tim will have to finish EWB then there are all the other enhancements, bug fixes and suggestions for both DBISAM and ElevateDB. Even when/if he does get to it then unless he does something very clever (and I know he is) its not to likely to be much faster than LIKE. Unlike a full or starting portion of a word unless you build a full index for the partial words there's nowhere really to use to speed the search up.

If the column you're wanting to index isn't one that's frequently changed the speediest approach will be to write a UDF word generator to generate all partial words of x or more letters, bolt it into the engine and then use TEXTSEARCH. The simplest approach will be to continue to use LIKE.

I'm happy to email you a copy of the UDF I created for use in DBISAM. It only generates complete words but it will give you a start.

Roy Lambert [Team Elevate]

Luis,

<< Are there any news about this? Or any other solution? >>

Nothing new on this front.  The best that EDB could do would be allowing
multiple wildcards, and then doing a full index scan to satisfy the request.
Unfortunately, this type of functionality isn't possible in DBISAM due to
the way that the indexes work.  In general, EDB can do index scans for LIKE
in situations where DBISAM cannot.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com