Need help in Simple select of Sql Server query with large volume of data in table

Question

Below is the simple query

SELECT ROW_NUMBER() OVER(PARTITION BY FKOrderKey ORDER BY PKOrderHistoryKey DESC) AS RowNumber, FkOrderKey, OH.FKOrderStatusKey,RO.CreatedBy,RO.RelativeEligibilityKey,
RO.IsProcessed,RO.FKOrderSourceKey,RO.CreatedDate,RO.ProcessedDate,RO.FKCenterKey, RO.PayerKey,RO.PKOrderKey, RO.FKOrderTypeKey
FROM OrderHistory OH
INNER JOIN ReferralOrder RO ON RO.PKOrderKey = OH.FKOrderKey --AND RO.CreatedDate >= ISNULL(@OrderDateFrom, RO.CreatedDate) AND RO.CreatedDate <= ISNULL(@OrderDateTo, RO.CreatedDate)
WHERE FKOrderKey IS NOT NULL AND OH.FKOrderStatusKey NOT IN (114, 115) 
AND RO.FKOrderTypeKey = 278

This query is taking 23 sec to run. In Referralorder table we have around 0.8 million record and Order history table has 2.2 million record. Query is running slow when large volume of data return.

https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan#7359705 — jarlh, Sep 12 '18 at 10:14

score 0 · Answer 1 · answered Sep 12 '18 at 10:14

For this query:

SELECT ROW_NUMBER() OVER (PARTITION BY FKOrderKey ORDER BY PKOrderHistoryKey DESC) AS RowNumber,
       FkOrderKey, OH.FKOrderStatusKey, RO.CreatedBy, RO.RelativeEligibilityKey,
       RO.IsProcessed, RO.FKOrderSourceKey, RO.CreatedDate, 
       RO.ProcessedDate, RO.FKCenterKey, RO.PayerKey, 
       RO.PKOrderKey, RO.FKOrderTypeKey
FROM OrderHistory OH INNER JOIN
     ReferralOrder RO
     ON RO.PKOrderKey = OH.FKOrderKey --AND RO.CreatedDate >= COALESCE(@OrderDateFrom, RO.CreatedDate) AND RO.CreatedDate <= COALESCE(@OrderDateTo, RO.CreatedDate)
WHERE FKOrderKey IS NOT NULL AND  -- this condition is unnecessary
      OH.FKOrderStatusKey NOT IN (114, 115) AND
      RO.FKOrderTypeKey = 278;

I would recommend indexes on ReferralOrder(FKOrderTypeKey, FKOrderStatusKey, PKOrderKey) and OrderHistory(FKOrderKey).

Need help in Simple select of Sql Server query with large volume of data in table

1 Answers1