Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 10 total |
Any fast replacement for LIKE ?? |
Wed, May 2 2007 2:16 AM | Permanent Link |
Kerry Neighbour | 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? |
Wed, May 2 2007 6:27 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Wed, May 2 2007 11:03 AM | Permanent Link |
"Erik" | 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 > |
Wed, May 2 2007 11:57 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, May 2 2007 7:31 PM | Permanent Link |
Kerry Neighbour | >
> 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. |
Thu, May 3 2007 6:13 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Mon, May 7 2007 8:06 PM | Permanent Link |
Kerry Neighbour | 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 > |
Wed, May 9 2007 5:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, May 9 2007 7:15 PM | Permanent Link |
Charalabos Michael | 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 |
Thu, May 10 2007 1:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< Is like using a index ? >> Not in DBISAM. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |