是优化选择最大(日期)SQL查询

Question

此查询太慢。是否可以优化？

SELECT 
    CONVERT(CHAR(10), MAX(dat), 101) AS MaxDate 
FROM 
    X_Prices price      
INNER JOIN 
    RMetal cat ON cat.SubCat = price.Subcat AND cat.Category = price.Category     
WHERE
    cat.Country = 'India' 
    AND cat.Section = 1 
    AND cat.active = 1 
    AND cat.inactive = 0 
    AND price.Dat < GETDATE() - 60 

Answer 1

用于此查询：

SELECT CONVERT(CHAR(10), MAX(dat), 101) AS MaxDate
FROM X_Prices price INNER JOIN
     RMetal cat
     ON cat.SubCat = price.Subcat AND cat.Category = price.Category     
WHERE cat.Country = 'India' AND
      cat.Section = 1 AND
      cat.active = 1 AND
      cat.inactive = 0 AND
      price.Dat < GETDATE()- 60 ;

我将从 RMetal(Country, Section, Active, Inactive, Category, SubCat)和 X_Prices(Category, SubCat, date)上的索引开始。

Answer 2

如果您可以更改DB：用国家代码替换国家代码确保子猫＆amp;类别是数字，＆amp;具有索引（作为外键）

如果这是DB结构，您可能应该根据执行计划添加索引，可能在整个条款

Answer 3

在

上应用索引

 Country , Section , active , inactive column of RMetal 
table And Category, SubCat, date column of X_Prices table