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> _______________________________________________ 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.
