Full Text Search

What I need to do to use a TextMemo column in HyperFile for full text search ?


Message forwarded from pcsoft.us.windev

Hi Marco...

2 answers:

1. slow method: you read all records one after the other and do a
position in the memo (nothing to do to prepare, very slow to find results)

2. you create a dictionary:
Each time you add/modify the memo, you add each and every word contained
in it to a dictionary file organized as follow:
Key (not unique): The word itself
Record Number in which the word is contained
Optionally, File containing the word (can be done on multiple files)
Optionally, field containing the word (can be done on every text fields
of a record)
Optionally, position of the word in the field

When you want to search for a word, you search on the dictionary, then
you open display the corresponding records of the main files...

It's extremely fast to get an answer, but takes some time each time you
add/modify a record

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

More information on [www.fabriceharari.com]


Marco Rego wrote:
> What I need to do to use a TextMemo column in HyperFile for full text search ?
>
>
Message forwarded from pcsoft.us.windev

Hi Marco,

> I just wanted to avoid the hard work :-) of putting the memo in a string, filter the repeated words, removing the punctuation, removing the accents, make the words uppercase and insert each of them in a table along with the id of the memo.

There is a nice WD Function for that, in case you need it: StringFormat()

The "Option" parameter allows you to do uppercase, no accents, no
punctuation. As for word duplication, well, if you store the word's
position, you want them to do text search with a given order in the words.

> Is it possible to put a HF table in memory ? This way the full text search would be very fast.

/Maybe Fabrice can give more details on that./ But I experienced that HF
in-string search (indexed) on a PC with 512 Mb RAM is super slow (35K
records) taking up to 4 minutes. (Compared to 30 seconds with competitor
products). Same performance in HF C/S.

BUT when I upgraded my memory to 4Gb it went super fast! Why? Because HF
was loading itself up in memory when possible. The same search was
taking 2 seconds. Same for HF C/S.

The Tech Support does not believe me yet, and I must create a demo
application for them. Hopefully, they will be able to optimize HF
performances to reach a 30 seconds search since their competitors are
able to do so (on a network! whilst it was local for HF in my test.)

So my point is: HF will load itself in memory if it can. (Local access,
enought memory, etc.)


*To force a table in memory*, I know only one trick: HCreateView() In
the documentation it says that this will cause the file (the new view)
to be loaded in memory. (Read it in the help, right at the top.)


Best regards.

--
Alexandre Leclerc
Message forwarded from pcsoft.us.windev



Edited 1 time(s). Last edit at 09/23/2008 07:03PM by Jimbo.

Hi

>> Is it possible to put a HF table in memory ? This way the full text search would be very fast.
>
> /Maybe Fabrice can give more details on that./ But I experienced that HF
> in-string search (indexed) on a PC with 512 Mb RAM is super slow (35K
> records) taking up to 4 minutes. (Compared to 30 seconds with competitor
> products). Same performance in HF C/S.
>
> BUT when I upgraded my memory to 4Gb it went super fast! Why? Because HF
> was loading itself up in memory when possible. The same search was
> taking 2 seconds. Same for HF C/S.
>
> The Tech Support does not believe me yet, and I must create a demo
> application for them. Hopefully, they will be able to optimize HF
> performances to reach a 30 seconds search since their competitors are
> able to do so (on a network! whilst it was local for HF in my test.)
>
> So my point is: HF will load itself in memory if it can. (Local access,
> enought memory, etc.)
>

Loading a table in memory is not 'explicitely' possible... What I mean
is that creating a view (or any other method) will still have to read
all the records on the disk to load them in memory...

The only way to do that is to use an external program to emulate a hard
drive in the PC memory (there a re few of those on the market), but
that's an expansive solution that can be used only in specific cases
(spectacular results though)

Even adding memory to the pc will help so much... The FIRST time you
read the record, they are still coming from the disk... Then the second
query is much faster...

The only real way to do this kind of processing fast is with the
dictionnary system (it's what every search engine is doing, either on
the web or on desktop systems, like google desktop)

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

More information on [www.fabriceharari.com]

>
> *To force a table in memory*, I know only one trick: HCreateView() In
> the documentation it says that this will cause the file (the new view)
> to be loaded in memory. (Read it in the help, right at the top.)
>
>
> Best regards.
>
Message forwarded from pcsoft.us.windev



Edited 1 time(s). Last edit at 09/23/2008 07:07PM by Jimbo.

Hi Fabrice,

Good to see you're back!

Cheers

Bob (UK)