On Thu, Sep 4, 2008 at 2:42 PM, Nick Causton <[EMAIL PROTECTED]> 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? ----------------------------------------------------------------------
Is OrderNo int or nchar() ? Hummm clastOrderNumber I am guessing that it is not a good index candidate for what your doing. have you checked the statistics? Here is a good overview with specific details on Index creation ins SQL Server: <http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx> I bet the idea of previous <-> next is a hold over from years gone by. why not get a record set back of 10-50 rows of the real key for the table Orders as well as the display of the Order Number. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ 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.
