On Thu, Sep 4, 2008 at 3:42 PM, Nick Causton <[EMAIL PROTECTED]> wrote: > > * 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? >
How is it you know that VFP is not using the index? Have you examined the Rushmore results of the query using the SYS() function? It's more likely that Fox is processing the entire query, sorting the entire dataset result, then truncating ito the TOP 1 record, which is what you asked it to do, a pretty intensive intermediate step. Why not try SELECT MAX(OrderNo) from Orders where OrderNo < lastOrderNo ... or.. SELECT MIN(OrderNo) from Orders where OrderNo > lastOrderNo and use that single record result as the key for getting the Next/Previous record? SELECT * FROM Orders where OrderNo IN (your subselect for Max() or Min()) -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ 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.
