Yep, that shows that all are optimised but the speed suggests that they aren't.
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Rick Schummer > Sent: 06 April 2008 00:05 > To: [email protected] > Subject: RE: SQL UNION for finding next/prev records > > 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/!&!AAAAAAAAAAAYAAAAAAAAAOO2KhH5xw1Oq9ETMzh8UaqijQAAEAAAABzl6/[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.
