Locate Vs seek

[dardapu]

In a I module I have fact a window of search of data. This search is made on a field and I make it with
LOCATE for m.var$detall_art
since should be been able to look for any contained character in that field, for example "V150" or "LAMP 12" etc. in "LAMP 12 V. MODEL V150". But the search is very slow.

The base indexed lower that field, can I carry out a search with the approach of the locate?
Thank you

 

[DSummZZZ]

'$' (substring) search is always slower than a SEEK or even an optimizable LOCATE. Since as the record pointer moves through the table it must compare the search string with the entire searched string, starting at the first character, then the second, etc. Say the field contains:
'LAMP 12 V. MODEL V150' and you're looking for 'MODEL V150'. $ starts at the beginning of the field and scans through it:

'LAMP 12 V. MODEL V150' 
'MODEL V150'
'LAMP 12 V. MODEL V150'
 'MODEL V150'
'LAMP 12 V. MODEL V150'
  'MODEL V150'
'LAMP 12 V. MODEL V150'
   'MODEL V150'
'LAMP 12 V. MODEL V150'
    'MODEL V150'
'LAMP 12 V. MODEL V150'
     'MODEL V150'
'LAMP 12 V. MODEL V150'
      'MODEL V150'
'LAMP 12 V. MODEL V150'
       'MODEL V150'
'LAMP 12 V. MODEL V150'
        'MODEL V150'
'LAMP 12 V. MODEL V150'
         'MODEL V150'
'LAMP 12 V. MODEL V150'
          'MODEL V150'
'LAMP 12 V. MODEL V150'
           'MODEL V150'  <--- match

INDEX will compare the first n characters of the field to see if it matches. So if 'MODEL V150' is at the beginning of the field and you have SET EXACT OFF, you would get a match. But if it isn't, you won't.
'LAMP 12 V. MODEL V150'
'LAMP 12' <---match
'LAMP 12 V. MODEL V150'
'LAMP 12 V.' <--- match


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[dbMark]

If you are trying to match a string of characters to text that might be anywhere within a field, LOCATE will do it. It will be faster if you do not have any indexes active so that the search can proceed sequentially through the table starting with records 1, 2, 3, 4, etc.

If your first match found with LOCATE is not the one you want, then you can CONTINUE for the next matching expression.

Another method is to SET FILTER TO mystring $ myfield, then GO TOP. That's especially helpful when you want to BROWSE a table.

Of course, SEEK which uses the index is much faster, but that's when you're trying to match a string at the beginning fo the field.

 

[WildSwan]

Would the SELECT SQL help here?

 

[DSummZZZ]

Would the SELECT SQL help here?

Rushmore optimization rules still apply to queries as well as SEEK and LOCATE.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[AnanthaP]

Of course, if the data to be fetched are all whole words AND it doesn't change often (say part number description by your example) you could write a program to add the whole words to another table that has the word list and record numbers (of the part list), seek on that ..


Or maybe give FILER in the menu if FPD (DOS version).

OK?
End

 

[dbMark]

From the Visual FoxPro forum: thread184-737754