Hi John...
>>>>>>>>>>>>>>>>>>>>>>>>
1. When I need to use LOCATE FOR to search my data, do I need to SET ORDER TO myTag before issue LOACATE command? I heard that FOR need index to optimize the searching, is it? However, if Issue SET ORDER TO myTag before LOCATE, it is also time consuming for reorder the index. Any Idea?
>>>>>>>>>>>>>>>>>>>>>>>>

ANSWER:
LOCATE can indeed take advantage of an index. But, if speed is a concern (and it seems to be from the subject of your post!), a SEEK is even quicker. In fact, Seek() doesn't require that you reset the index -- see the help file -- since you can specify the index tag as a parameter. I use locates for very small tables, or very complex and infrequent search criteria.

Take a look also into the SET KEY syntax.... allows you to filter on a expression matching an index expression, and then, potentially, locate within this to narrow it down. Very very quick.

>>>>>>>>>>>>>>>>>>>>>>>>>
2. !DELETED() is not optimized. What should I do if I need to search for those undeleted record frequenty?
>>>>>>>>>>>>>>>>>>>>>>>>>
ANSWER:
Add a tag to all of your tables on Deleted(). I call this tag DelRec, rather than Deleted -- back in VFP3.0 I ran into some corruption that I have always suspected was do to naming an index tag after a key word..... remember, the tag name doesn't matter, the expresssion does.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3. Does SQL statement in VFP optimized? If I need to search user records from vfp table and add it into listbox, do you prefer SQL statement into array, then loop through array to add the list or use LOCATE FOR ... WHILE NOT EOF() ...CONTINUE command to do this? why?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ANSWER:
SQL Statements will be as optimized as they can be, given the indexes available, etc. The inner workings of the compilation of SQL statements are a little hard to figure out, but check out the sys(3054) command -- you can try changing the order of your where statements, fooling with the join conditions, adding further indexes, etc. to optimize the SQL statement further. Just remember that every index you add will slow doen Inserts, so it is a tradeoff... Also, list boxes can also be filled by a SELECT INTO CURSOR command -- personally, I haven't found speed differences between this and a select into array... they will both be quicker than LOCATE FOR... WHILE NOT.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4. If I have a table which save the list of setting for each company door (door access system), and I would like to let user to modify/delete more than one setting at the sometime and update batch. Since i am using ADO (for example), wat method should I use to update those deleted / updated record? Again, it should enforce the integraty.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ANSWER:
If you are using ADO, look into the UpdateBatch method, with client side cursors. But, ADO is slower than native VFP access... if speed matters so much and you are NOT using a client-server backend, go for native table access. The next release of VFP will include a native OLEDB provider which should make ADO access much quicker.