How can i rewrite this query for faster execution

SELECT s1.ID FROM binventory_ostemp s1 JOIN 
( SELECT Cust_FkId, ProcessID, MAX(Service_Duration) AS duration 
 FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' AND Overall_Rank IN 
 (
  SELECT MIN(Overall_Rank) FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' GROUP BY Cust_FkId
 )
 GROUP BY Cust_FkId
) AS s2 ON s1.Cust_FkId = s2.Cust_FkId AND s1.ProcessID=s2.ProcessID 
AND s1.Service_Duration=s2.duration AND s1.ProcessID='4d2d6068678bc' 
GROUP BY s1.Cust_FkId

It just goes away if there are more than 10K rows in that table. What it does is find rows for each customer who has min. of ov开发者_如何转开发erall rank and in those max. of service duration for a given processid

Table Data

ID Cust_FkId  Overall_Rank Service_Duration  ProcessID
1     23       2            30             4d2d6068678bc
2     23       1            45             4d2d6068678bc
3     23       1            60             4d2d6068678bc
4     56       3            90             4d2d6068678bc
5     56       2            50             4d2d6068678bc
6     56       2            85             4d2d6068678bc

Result Data

Result ID values must be 3 and 6 only

Following select might be faster.

(Covering) Indexes on

• Cust_FkID, Overall_Rank
• Cust_FkID, Service_Duration
• Cust_FkID, Overall_Rank, Service_Duration
• Overall_Rank
• Service_Duration

Remove the indexes that don't get used by looking at the execution plan.

SQL Statement

SELECT  b.*
FROM    binventory_ostemp b
        INNER JOIN (
          SELECT  b.Cust_FkID
                  , ovr.Overall_Rank
                  , MAX(Service_Duration) AS Service_Duration 
          FROM    binventory_ostemp b
                  INNER JOIN (
                    SELECT  Cust_FkID
                            , MIN(Overall_Rank) AS Overall_Rank
                    FROM    binventory_ostemp        
                    GROUP BY
                            Cust_FkID
                  ) ovr ON  ovr.Cust_FkID = b.Cust_FKID
                            AND ovr.Overall_Rank = b.Overall_Rank        
          GROUP BY
                  b.Cust_FkID
                  , ovr.Overall_Rank
        ) ovrs ON ovrs.Cust_FkID = b.Cust_FkID
                  AND ovrs.Overall_Rank = b.Overall_Rank
                  AND ovrs.Service_Duration = b.Service_Duration