Have you run the queries in VFP to check out the Rushmore Optimization ShowPlan using SYS(3054)?
Rick White Light Computing, Inc. www.whitelightcomputing.com www.swfox.net www.rickschummer.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nick Causton Sent: Saturday, April 05, 2008 04:20 PM To: [EMAIL PROTECTED] Subject: SQL UNION for finding next/prev records Anyone got an idea how to optimise this SQL, I am trying to use it to find the next / previous records via ADO but it is way too slow at the moment. Perhaps there may be a better way of doing this without a UNION? * previous record SELECT * FROM Master WHERE ; (Id < lcPreviousId AND LEFT(Id,1)=lcIdCheck) ; UNION ; SELECT * FROM Archive WHERE ; (Id < lcPreviousId AND LEFT(Id,1)=lcIdCheck) ; ORDER BY Id DESC * next record SELECT * FROM Master WHERE ; (Id > lcPreviousId AND LEFT(Id,1)=lcIdCheck) ; UNION ; SELECT * FROM Archive WHERE ; (Id > lcPreviousId AND LEFT(Id,1)=lcIdCheck) ; ORDER BY Id ASC There are already indexes on Id (both ascending and descending) and LEFT(Id,1). Any suggestions, other than you don't want to do that. Cheers, Nick [excessive quoting removed by server] _______________________________________________ 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.
