>>>>Hello FoxProgrammers,
>>>>
>>>>I am working with large datasets, over 1000000 records. They are located in FoxPro 2.6 tables on Netware servers and used by 30 people.
>>>>Now I am porting to VFP 5.0 and I thought it would be faster through the use of SQL, remote views, fetching and so on. My plan was to build a select statement that could change interactively on user input (in 7 textboxes). Now I have some problems: it takes several minutes for any more-than-one-criterium search. I have indexes on all 7 criteria. I use remote views through an ODBC connection. The table is 350 Meg.
>>>>What is the best way to upgrade? Using the good old SEEK statement?
>>>>
>>>>Thanks,
>>>>
>>>>Bas
>>>
>>>Are you using a "GROUP BY" clause (bad for optimization)? Are your parameters something like FieldName="Value" ? If so, optimization is usually full. Equations like UPPER(FIELDNAME) = "VALUE" takes away from rushmore. Any kind of function on the left side of equation "kills" optimization unless the function is "indexable." Play with the SQL statement your form generates using the SYS(3054, 1) function (see VFP Help for explanation on its use).
>>
>> UPPER(FIELDNAME) = "VALUE" can work fine if you have an index on UPPER(FIELDNAME). Also check and make sure that you have the most current ODBC drivers. I have not used them yet, but I have heard that 1) they are slower than native VFP tables and 2) the newer ones are better/faster.
>>Also, do you have an index tag on DELETED()? I have found that you can improve response by 1)Having the DELETED() index tag 2)Setting DELETED OFF and 3) adding ...AND NOT DELETED() to my queries. (Once again this is what I have found for native VFP tables) I would like to know if you found that this helped in your case. Also if anyone else has found other and/or better ways to handle large tables using ODBC.
>>//:^)
>
>I did mention functions on the left are optimizable if they are indexable. I have not used ODBC against VFP tables. I do use ODBC to retrive data from DB2 tables via an Oracle gateway, TCP/IP and SQL*Net. I consider the response highly acceptable. I attribute this to exceptional indexes on the tables. The queries I submit are usually against 1- to 2-million record tables that are returning anywhere from 10 to a few hundred records in less than 5 seconds.
>
>Queries against large VFP tables are definintely faster with index on DELETED().

Do your DB2 tables have an index on DELETED()? Are (Can) DB2 indexes (be) built from within your VFP app? If so can you give me a code snippet that shows how you do it? (i.e. Using the DB2 table exclusive, delete tag all, pack and index on...) Thanx //:^)