Subject Re: SEARCHING VERY LARGE FILE From Mervyn Bick <invalid@invalid.invalid> Date Tue, 31 Aug 2021 15:41:11 +0200 Newsgroups dbase.getting-started On 2021/08/31 06: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 As Gaetano has pointed out, the way you are selecting records for display is inherently SLOW especially with bigger tables.  Selecting records based on matching a string somewhere in the contents of a field is probably the most onerous task one can ask a SQL engine to do.  And if you access the table over a network bear in mind that the records selected each time all have to travel across the network each time. Your network administrator may not be too happy. :-) If your query parameter was this.value+'%' you could speed things up significantly by creating a simple index on the 'descript' field.  The SQL engine only has to select those records which begin with this.value.   The index doesn't have to be active, it just has to be there. Because your query parameter is '%'+this.value+'%' the SQL engine can't use an index as the string to match can be anywhere in the field.  The contents of each record has to be scanned until a match is found.  As the search value is extended the BDE's SQL engine is probably "intelligent" enough to know that it only has to scan the records selected previously.  This reduces the response time each time the search value is extended. When no match is found and you use the backspace to remove the last character, the SQL engine has no previously selected records to examine so it starts from scratch by looking at every single one of the million plus records.  This actually happens every time you remove a character from the search value even if the previous search did return records. You can reduce the search time by not doing an incremental search as each character is added.  If you type in a groups of characters and then initiate the search by pressing the enter key you will reduce the number of times the SQL engine has to scan records.  This will, in turn, reduce the response time.  It does not, however, address the issue of the delay caused by having to start from scratch if the search string is shortened.   function ENTRYFIELD1_onKey1(nChar, nPosition,bShift,bControl)      if nChar = 13 //Enter key         form.verw_oud1.params['ag'] = '%'+this.value+'%'         form.verw_oud1.requery()      endif      return If you don't change your search method probably the only other way to improve response time is to keep the .dbf file on a SSD but this will, of course, cost and assumes that the computer can support SSD. Even better would be a NMVe SSD but this is even more expensive. I don't know what you are searching for but perhaps there's a way of cutting down the number of records to look at initially.  This may entail adding fields to your table.  Populating the new fields now could, however, prove to be a major problem.  If these extra fields had been provided from the beginning they would have been populated with the appropriate values as each record was added. As an example, this morning I used an online shopping application to buy goods from one of our large supermarkets.  One of the items I wanted was some lamb chops. If I had to search the entire table the 'c' may have returned 490000 records out of say 500000. The 'h'would cut the number down. By the time I got to 'chops' there may still be, say, 20 records to look at. If there was a "section" field (there was :-) ) that showed that an item was in the butcher section then by specifying "butcher" as one parameter this would immediately cut the initial number of records to be scanned from 500000 to perhaps 5000.  It would still leave me with 20 records at the end from which to choose.  If, however, there was a further "product" field to indicate 'lamb', 'beef', 'pork' and so on then the initial number of records to be scanned could be as low as, say, 100. By the time 'chops' had been entered for the third parameter there would perhaps be 3 records to choose from. Mervyn.