Nick Causton wrote: > Hi all, > > I have a problem with an app which retrieves from a table of over 100k > records, this is happening via ASP with ADO so it can only be done using > SQL. > > There is an OrderNo field which is indexed and to move through the records I > am using: > > * Next > SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo > (clastOrderNumber) ORDER > BY OrderNo DESC > > * Prev > SELECT TOP 1 * FROM Orders WHERE Orders.OrderNo < (clastOrderNumber) ORDER > BY OrderNo ASC > > Problem is VFP does not use the index for this and retrieving the > next/previous record takes nearly a minute when it is running over the > network. > > Any suggestions how to get around this?
1) Maintain a cursor of valid order numbers on the VFP side. For example, to start with: sqlexecute(handle, "select OrderNo from orders order by OrderNo ASC", "order_nums") select order_nums index on OrderNo tag OrderNo 2) When it comes time to view the next/previous order, use the cursor you created in 1 to find the next or previous order number, and then execute sql like: * Next SELECT * FROM Orders WHERE Orders.OrderNo == (clastOrderNumber) The way you have it, you are forcing VFP to download all 100K records to the local side, in order to be able to find the record you want. HTH Paul _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
