Subject Re: SEARCHING VERY LARGE FILE
From Gaetano D. <gaetanoddRemoveThis@andThatHotmail.com>
Date Tue, 31 Aug 2021 17:20:17 +1000
Newsgroups dbase.getting-started

On 31/08/21 14:00, AGOSTINHO wrote:
> Dear group
 > I've a particular issue when searching this long file(more than 1 million records).
 > See form below, it searches perfect when typing anything(TEXT) that exists in the field.
 > The problem is when I type anything that don't exists than it hangs for a few second after I try to clear(BACKSPACE) the search-entryfield  on the form.
 > Any idea how to avoid this behavior?
 > Thanks
 > Agostinho
 >
 >
 >
 > parameter bModal
 > local f
 > f = new testingForm()
 > if (bModal)
 >     f.mdi = false // ensure not MDI
 >     f.readModal()
 > else
 >     f.open()
 > endif
 >
 > class testingForm of FORM
 >     with (this)
 >        height = 16.0
 >        left = 19.8889
 >        top = 3.68
 >        width = 146.5556
 >        text = ""
 >     endwith
 >
 >     this.VERW_OUD1 = new QUERY(this)
 >     with (this.VERW_OUD1)
 >        left = 31.0
 >        top = 13.0
 >        sql = 'select * from "C:\Users\HOME_PC\Desktop\dbasetutorial\verw_oud.dbf" where lower(descript) like lower(:ag)'
 >        params["ag"] = "%"
 >        active = true
 >     endwith
 >
 >     this.ENTRYFIELD1 = new ENTRYFIELD(this)
 >     with (this.ENTRYFIELD1)
 >        onKey = class::ENTRYFIELD1_ONKEY1
 >        height = 1.92
 >        left = 11.0
 >        top = 0.96
 >        width = 19.0
 >        value = ""
 >     endwith
 >
 >     this.GRID1 = new GRID(this)
 >     with (this.GRID1)
 >        dataLink = form.verw_oud1.rowset
 >        bgColor = "WindowText"
 >        height = 8.64
 >        left = 1.0
 >        top = 3.36
 >        width = 136.0
 >     endwith
 >
 >
 >         
 >     function ENTRYFIELD1_onKey1(nChar, nPosition,bShift,bControl)
 >         form.verw_oud1.params['ag'] = '%'+this.value+'%'
 >         form.verw_oud1.requery()
 >        return
 >
 > endclass
 >
 >
 Hi Agostinho,

This is a very slow search method: 1 million records searched for a
match anywhere in the field on every keystroke, it will take some time.
Are you sure there is no way to make a smaller pre-selection to make the
final search a bit faster? I tried on a table that has 1.5million
records and it does take 10-15 seconds to either return something "rare"
(like a city name including "aaa" - but there are actually two records)
or nothing if there is no match - I'm afraid that it is simply a
consequence of the very broad-scope search method.

--
Gaetano.