>We need to process information on a parent record in table A, 1 or more children in table B, and 5-20 records from a couple of other child tables. We need all the children, the whole set of records, for a given A record in order to process. And we often need to process 1000 or more sets.
>
>In our current app, in foxpro 2.6, retrieving each set individually takes too long (Table B has over a million records, for example). So we get a list of key fields for A, and pull down all the information to temporary dbfs at once, index them, and get each A-record's set of information from the temp dbfs. We then process the set and update the A and B tables with the results, repeating for each set.
>
>We are recreating our app in VFP, and adding a sql-server or sybase back end. Since retrieving each set of records individually will still kill our performance [are we wrong to assume this???], we want to retrieve all the records for a list of A-table keys into local dbfs (views, cursors, whatever) and work with them there, just as we do now in 2.6.
>
>Is there any other way to do this than 1) uploading the list of A-table keys to the back end as a table (with a unique name so we can do this multiple times at once) 2) using back-end sql to create local views that join the A table with the keylist , then the B-table, etc. 3) indexing the local views 4) getting and processing each a-record's set of information 5) somehow updating certain fields in remote tables A and B with the process results (maybe updating the local views and then using tableupdate() to hit the remote data, maybe using back-end sql to update the remote tables directly).
1)How do you detere which "A" table records to process?
2)Have you thought to use the SQL parameter to limit the number of records returned at one time?