Subject Re: running SQL LIKE % on DBF table From Mervyn Bick <invalid@invalid.invalid> Date Sat, 1 Aug 2015 10:02:35 +0200 Newsgroups dbase.getting-started On 2015/07/31 06:29 PM, eric wu wrote: > > "select Myame from myTable where name like 'ABC%'" > > myTable.dbf is a 80MB file > it takes 20 mins.  to get the result. > > is it normal ? > > or I should index the filed "Myame"  to improve the performance ? > > eric Unless you are using an old XT computer something is very wrong. :-) I set up a little test form using a 49MB table with over 407000 records using the following query.     this.MU1 = new QUERY()     this.MU1.parent = this     with (this.MU1)        left = 4.1429        top = 0.6818        sql = 'select * from "mu.DBF" where linea like :a '        params["a"] = "%"        active = true     endwith The form opens virtually instantaneously with all the records displayed in a grid. Without an index, changing the parameter and requerying to select 1, 6667 or 40676 rows gives the following times          11.22  seconds         1  rows          0.00  seconds       6667  rows          0.11  seconds      40676  rows When run a second time the times are          3.44  seconds          1  rows          0.00  seconds       6667  rows          0.09  seconds      40676  rows With an index          10.38  seconds         1  rows          0.00  seconds       6667  rows          0.09  seconds      40676  rows and the second time          2.39  seconds          1  rows          0.00  seconds       6667  rows          0.09  seconds      40676  rows Adding an ORDER BY clause to the SQL statement slowed things down tremendously.  I have no idea how long it would have taken to open the form as I killed dBASE after 2 minutes. :-( Mervyn.