c# - SQL Server-DATEDIFF函数花费的时间太长

我已经进行了广泛的研究，并得出结论说DATEDIFF函数使我的查询运行非常缓慢。

下面是由Entity Framework生成的查询，它看起来确实希望可读。

这是生成T-SQL的Linq：

model.NewTotal1Week = ( from sdo in context.SubscriberDebitOrders
                        where
                        (
                           sdo.CampaignId == campaignId &&
                           ( sdo.Status == ( Int32 ) DebitOrderStatus.New_Faulty ) &&
                           ( SqlFunctions.DateDiff( "week", sdo.Collections.FirstOrDefault( c => c.TxnStatus == "U" ).ProcessDate, DateTime.Now ) <= 1 )
                        )
                        select sdo ).Count();

在下面的查询中，我希望获得所有集合的COUNT个，这些集合从处理日期到今天为止的1周内。

有没有人可以帮助我摆脱DATEDIFF函数？我在网上看到了一些示例，但是我无法使其适应我的情况，请原谅我还不是很天才。

exec sp_executesql N'SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM  [dbo].[SubscriberDebitOrder] AS [Extent1]
        OUTER APPLY  (SELECT TOP (1)
            [Extent2].[ProcessDate] AS [ProcessDate]
            FROM [dbo].[Collections] AS [Extent2]
            WHERE ([Extent1].[Id] = [Extent2].[DebitOrderId]) AND (''U'' = [Extent2].[TxnStatus]) ) AS [Limit1]
        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND (3 = [Extent1].[Status]) AND ((DATEDIFF(week, [Limit1].[ProcessDate], SysDateTime())) <= 1)
    )  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=3
go

提前致谢。

最佳答案

这是您的查询：

SELECT GroupBy1.A1 AS C1
FROM (SELECT COUNT(1) AS[A1
      FROM dbo.SubscriberDebitOrder AS Extent1 OUTER APPLY
           (SELECT TOP (1) Extent2.ProcessDate
            FROM [dbo].Collections Extent2
            WHERE (Extent1.Id = Extent2.DebitOrderId AND
                   'U' = Extent2.TxnStatus
           ) AS [Limit1]
      WHERE (Extent1.CampaignId = @p__linq__0) AND (3 = Extent1.Status) AND
            (DATEDIFF(week, Limit1.ProcessDate, SysDateTime()) <= 1)
     )  GroupBy1;

正如其他地方提到的，您应该更改日期逻辑并摆脱外部查询：

      SELECT COUNT(1) AS A1
      FROM dbo.SubscriberDebitOrder AS Extent1 OUTER APPLY
           (SELECT TOP (1) Extent2.ProcessDate
            FROM [dbo].Collections Extent2
            WHERE (Extent1.Id = Extent2.DebitOrderId AND
                   'U' = Extent2.TxnStatus
           ) AS limit1
      WHERE (Extent1.CampaignId = @p__linq__0) AND (3 = Extent1.Status) AND
            Limit1.ProcessDate <= DATEADD(-1, week, GETDATE())

非常重要的说明：这并不完全等同于您的查询。您的原始查询计算了两个日期之间的周边界数。这取决于datefirst，但是通常是星期六或星期日晚上的数量。

根据您的描述，以上更正确。

接下来，您要在Collections(DebitOrderId, TxnStatus, ProcessDate)和SubscriberDebitOrder(CampaignId, Status)上建立索引。

关于c# - SQL Server-DATEDIFF函数花费的时间太长，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37150434/