sql - SQL 性能问题

Question

如果有任何其他方法可以编写下面的查询来派生日期参数，你们能帮忙吗？我正在尝试添加一个功能，如果需要，可以根据标志从表中派生日期参数。

这是我之前立即运行的内容：

select distinct
    baf.AccountEpicId       "encounterepiccsn"      --aliased because this is what everything references
from 
    billingaccountfact baf

Where 
    (1=1
    and   baf.accountdatekey > 0
    and   baf.accountdatekey not in (-1,-2,-3)
    and cast(convert(char(8), baf.accountdatekey,112) as date) >= DateAdd(Day, DateDiff(Day, 0, GetDate()-1), 0)--Account Key greater than the StartDate 
    and cast(convert(char(8), baf.accountdatekey,112) as date) <= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)      
    )

这是我所做的更改，但性能需要超过 4 分钟：

    select distinct
    baf.AccountEpicId       "encounterepiccsn"      

from 
    billingaccountfact baf
LEFT JOIN ADS_Config D ON D.ObjectName='ADSEncountersVW'            
                                               AND D.DateOverride =1
    Where 
        (1=1
        and   baf.accountdatekey > 0
        and   baf.accountdatekey not in (-1,-2,-3)
        and cast(convert(char(8), baf.accountdatekey,112) as date) >= ISNULL(D.StartDate ,DateAdd(Day, DateDiff(Day, 0, GetDate()-1), 0))--Account Key greater than the StartDate 
        and cast(convert(char(8), baf.accountdatekey,112) as date) <= ISNULL(D.EndDate ,DateAdd(Day, DateDiff(Day, 0, GetDate()), 0))       
        )

标签： sqlquery-optimization