Sure can - of course it took 20 min. to remember WHICH program I'd had this problem in. You need to use "ForcePlan". Here's the code I used, and it worked perfectly!

SQLEXEC(thisform.nhandle, "Set ForcePlan ON")
SQLEXEC(thisform.nHandle, "SELECT * from orheader, ordetail ;
   WHERE orheader.ohid = ordetail.od_ohid ;
   AND OHID = cVal  ;
   order by ohordnum, ohordsuf, odlinenum", 'NewOrders')
SQLEXEC(thisform.nhandle, "Set ForcePlan OFF")

In this case SQL-Server was doing a search on ORDetail (1 million records) first and then checking ORHeader (100,000 records). Didn't matter how I changed the code it insisted on doing it that way. Forceplan forces SQL-Server to use the order in which the tables appear in the JOIN clause, so it used ORHeader first, ORDetail second. I tested it by rewriting it in the reverse format and the time went back to several minutes for the query instead of the 1+ sec. it took with ForcePlan. Note that all the tables were properly indexed (Query Optimizer agreed).

I've only needed ForcePlan this once, but it saved a client an incredible amount of aggravation as they loaded a form with one ORHeader record and 5-100 ORDetail records hundreds of times per day using this SQL-Select.

HTH
Barbara


>Barbara,
>
>Can you tell me how you forced the order of the search for the slow query? TIA.
>