Why does this Advantage Database query take so long?

Question

First, there are indices on Orders.Order_Number, OrderDet.Order_Number, and OrderDet.PatID. There are other indices, but these are the ones that look pertinent here to me.

This query takes anywhere from 20 to 114 seconds to perform, in the testing that I've done.

Update O
   SET O.BenefitID = 1,
       O.LastChangedBy = 'RH Test' 
   FROM Orders O
   JOIN OrderDet od ON od.Order_Number = O.Order_Number
   WHERE
         Od.PatID = 703007
         and Od.Status IN ('2', '7', '50', '51', '52', '78', '82');

If I do this instead, I get times of under 60ms:

SELECT ODetailID, Order_Number INTO #OrdNum FROM OrderDet 
   WHERE PatID = 703007
   AND Status IN ('2', '7', '50', '51', '52', '78', '82');  
Update Orders
   SET BenefitID = 1,
       LastChangedBy = 'RH Test' 
   WHERE Order_Number in (SELECT Order_Number from #OrdNum);        

   DROP TABLE #OrdNum;

Can someone tell me why my query takes so long when joining the OrderDet table to Orders? It isn't making sense to me that the join takes so long. If I select on either table based on order_number, I get under 200ms response. If I select on OrderDet using PatID, I get response of under 40ms. Selecting on Orders by PatId takes longer - 1-2 seconds, but there's no index on that column. I don't understand why it would be taking up to 114 seconds with the join, since the join is on a column that is indexed in both tables. Any help in understanding this is greatly appreciated.

Answer 1

The reason for this behavior is because when performing an UPDATE operation using join, the table being updated is fixed as the driving table of the nested loop join. Since there is no direct condition on the Orders table, table scan is the only option.

In a SELECT query with inner join of two tables, the tables may be switched to place the table with more restrictive result as the driver. This results in more optimized performance.

An alternative to using the temporary table will be using subquery which is more standard anyway:

Update Orders
   SET BenefitID = 1,
       LastChangedBy = 'RH Test' 
   WHERE Order_Number in 
       (SELECT Order_Number 
        FROM OrderDet 
        WHERE PatID = 703007
        AND Status IN ('2', '7', '50', '51', '52', '78', '82'));