Dan, Cannot change tables at all easily, they are accessed by some thirty year old applications. So unfortunately no I cannot add a C(1) field for dept.
In this case SET ANSI is ON, this will be running via ADO and that is the default setting in ADO. Nick > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Dan Olsson > Sent: 09 September 2008 22:30 > To: [email protected] > Subject: RE: SQL > not optimised > > At 2008-09-09 21:59, NickC wrote: > >SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo > cLastOrdNo ; > > AND Left(OrdNo,1) = cDept ORDER BY OrdNo > >* takes 50 seconds > > IF cDept is (or you could make it) C(1) you should not need an "INDEX > ON LEFT(OrdNo,1) TAG Dept" since the shorter value to the right of > "=" will match anyway (depending on settings of SET EXACT and so on) > so I would do the select: > > SELECT TOP 1 OrdNo FROM Orders WHERE OrdNo = cDept AND OrdNo > cLastOrdNo > > The ORDER BY would not be necessary since you only want one record, right? > > >SELECT OrdNo FROM Orders INTO TABLE OrdersNoOnly > >INDEX ON OrdNo TAG OrdNo > >INDEX ON LEFT(OrdNo,1) TAG Dept > > > >SELECT TOP 1 OrdNo FROM OrdersNoOnly WHERE OrdNo > cLastOrdNo ; > > AND Left(OrdNo,1) = cDept ORDER BY OrdNo > >* takes 0.7 seconds > > ****************************** > * Dan Olsson > * <mailto:[EMAIL PROTECTED]> > * <http://www.dolittle.se> > > > [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.
