Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 5 of 5 total |
Surprising (good) speed increase without a text index. |
Mon, Aug 8 2016 11:48 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Tue, Aug 9 2016 4:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 9 2016 4:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 9 2016 8:06 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Aug 9 2016 6:59 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |