Hi

This is just a comment, not a problem.

I had some code that built a filter like this:-

Filter := 'NOT LetterText LIKE ''%{Landlord|%'''
   + ' AND NOT LetterText LIKE ''%{Property|%'''
   + ' AND NOT LetterText LIKE ''%{Tenant|%''';

.... and found that it took 40 seconds to populate a combobox from the
filtered table.

So I read up about Text indexes and created a text index:-

CREATE TEXT INDEX "idxText" ON "Letters" ("LetterText" COLLATE "UNI_CI")
INDEXED WORD LENGTH 10
WORD GENERATOR "Default"

.... and re-coded the filter as:-

Filter := '(LetterText DOES NOT CONTAIN ''{Landlord|'') AND ' +
          '(LetterText DOES NOT CONTAIN ''{Property|'') AND ' +
          '(LetterText DOES NOT CONTAIN ''{Tenant)|'')';

This sped everything up - too fast to time manually!

All fine so far!

Then I ran the program on a database which hadn't had the text index
created.  SURPRISE it also ran too fast to time!

Maybe I'll go through my app and look at all the LIKE's.

Is this to be expected?

Cheers

Jeff

Jeff


I don't remember with DBISAM but I ran into a similar problem with ElevateDB. Essentially, in my view, the LIKE operator is just too clever.,  Again, my comments apply to ElevateDB but may be be applicable to DBISAM. In order to accommodate test such as LIKE '%text_moretext__stillmoretext%' (I think its an underscore for a single wild character - I've just never used it) Tim has had to implement a sliding window style of test. For many small (whatever that means) fields  it doesn't matter but for larger memo fields it can start to bite.

In my case I think  I was searching emails when I thought LIKE '%text%' was to slow. This was SQL generated by my end user query generator. I modified things to use POS(UPPER(text),UPPER(field)) which went like lightening. Note the UPPERs are needed because in ElevateDB (POS is case sensitive).

I'm surprised that TEXTSEARCH on an unindexed column is as fast as you say, and you may want to consider a change of strategy to use POS

Roy Lambert

Jeff


I've just realised that you're posting on the ElevateDB newsgroups in which case why didn't you get something like this for a query

ElevateDB Error #700 An error was found in the statement at line 1 and column 25 (Invalid expression "_guidance0" found, CONTAINS or DOES NOT CONTAIN can only be used with a text-indexed column with a matching collation)

or

ElevateDB Error #1001 A filter error occurred (ElevateDB Error #700 An error was found in the filter expression at line 1 and column 1 (Invalid expression "_guidance0" found, CONTAINS or DOES NOT CONTAIN can only be used with a text-indexed column with a matching collation))

for a filter

when the full text index isn't there?

I think either Tim has given you a better version than I have or you have a problem. (guess which way I'm betting

Roy Lambert

Jeff,

<< Then I ran the program on a database which hadn't had the text index created.  SURPRISE it also ran too fast to time! >>

Roy is correct - the use of CONTAINS should have caused an error on any table that was lacking an appropriate text index.

Tim Young
Elevate Software
www.elevatesoft.com

On 10/08/2016 12:06 AM, Tim Young [Elevate Software] wrote:
> Jeff,
>
> << Then I ran the program on a database which hadn't had the text index created.  SURPRISE it also ran too fast to time! >>
>
> Roy is correct - the use of CONTAINS should have caused an error on any table that was lacking an appropriate text index.
>
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Hi Roy and Tim

You are both right of course!

This is what comes of having databases with the same name open on 3
servers.  I must have got myself muddled.

Comes from watching too much Olympic sport on the TV - and New Zealand
"only" getting silver in the Womens Rugby 7's.

Cheers

Jeff