时间戳

首页 > 解决方案 > 内连接事务和帐户非常慢

sql-server - 内连接事务和帐户非常慢

问题描述

我有一个查询运行很慢：

select
    0                                              as totalAccountNew,
    0                                              as totalTransNew,
    0                                              as totalFlowNew,
    sum((t.OutAmount - t.InAmount))                as totalRevenueNew,
    dateadd(DAY, 0, datediff(day, 0, CreatedTime)) as datetime
from (
         select it.AccountID,
                it.CreatedTime,
                it.ServiceID,
                it.RoomID,
                it.SourceID,
                it.Amount as InAmount,
                0         as
                             OutAmount,
                s.InOut
         FROM InputTransactions it
                  inner join Services s on s.ServiceID = it.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID

         WHERE (s.InOut = 3 or s.InOut = 2)
           and st.CategoryId = 1
           and it.CreatedTime between :fromDate and :toDate
         union all
         select ot.AccountID,
                ot.CreatedTime,
                ot.ServiceID,
                ot.RoomID,
                ot.SourceID,
                0         as InAmount,
                ot.Amount as OutAmount,
                s.InOut
         FROM OutputTransactions ot
                  inner join Services s on s.ServiceID = ot.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID
         WHERE s.InOut = 1
           and st.CategoryId = 1
           and ot.CreatedTime between :fromDate and :toDate
     ) t
inner join Accounts a on a.AccountID = t.AccountID
where convert(varchar(10), a.CreateTime, 102) = convert(varchar(10), dateadd(DAY, 0, datediff(day, 0, t.CreatedTime)), 102)
GROUP BY dateadd(DAY, 0, datediff(day, 0, CreatedTime))

完成需要9s。当我使用 Accounts 和 Where 子句评论内部联接时，需要 900 毫秒。

我在 InputTransaction 和 OutputTransaction 中的 AccountID 上创建了一个非聚集索引，在 CreatedTime 和 CreateTime 中创建了一个非聚集索引

你能帮我优化查询吗？非常感谢。

解决了：

select
    0                                              as totalAccountNew,
    0                                              as totalTransNew,
    0                                              as totalFlowNew,
    sum((t.OutAmount - t.InAmount))                as totalRevenueNew,
    dateadd(DAY, 0, datediff(day, 0, CreatedTime)) as datetime
from (
         select it.AccountID,
                it.CreatedTime,
                it.ServiceID,
                it.RoomID,
                it.SourceID,
                it.Amount as InAmount,
                0         as
                             OutAmount,
                s.InOut
         FROM InputTransactions it
                  inner join Services s on s.ServiceID = it.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID
                  inner join
              (select AccountID, CreateTime from Accounts
               WHERE CreateTime between :fromDate and :toDate ) as a on (a.AccountID = it.AccountID)
         WHERE (s.InOut = 3 or s.InOut = 2)
           and st.CategoryId = 1
           and it.CreatedTime between :fromDate and :toDate
            and cast(a.CreateTime as date) = cast(it.CreatedTime as DATE)
         union all
         select ot.AccountID,
                ot.CreatedTime,
                ot.ServiceID,
                ot.RoomID,
                ot.SourceID,
                0         as InAmount,
                ot.Amount as OutAmount,
                s.InOut
         FROM OutputTransactions ot
                  inner join Services s on s.ServiceID = ot.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID
                  inner join
              (select AccountID, CreateTime from Accounts
               WHERE CreateTime between :fromDate and :toDate ) as a on (a.AccountID = ot.AccountID)
         WHERE s.InOut = 1
           and st.CategoryId = 1
           and ot.CreatedTime between :fromDate and :toDate
    and cast(a.CreateTime as date) = cast(ot.CreatedTime as DATE)
     ) t
GROUP BY dateadd(DAY, 0, datediff(day, 0, t.CreatedTime))

标签： sql-server

解决方案

如何先过滤帐户，以便在加入之前限制结果？

select
    0                                              as totalAccountNew,
    0                                              as totalTransNew,
    0                                              as totalFlowNew,
    sum((t.OutAmount - t.InAmount))                as totalRevenueNew,
    dateadd(DAY, 0, datediff(day, 0, CreatedTime)) as datetime
from (
         select it.AccountID,
                it.CreatedTime,
                it.ServiceID,
                it.RoomID,
                it.SourceID,
                it.Amount as InAmount,
                0         as
                             OutAmount,
                s.InOut
         FROM InputTransactions it
                  inner join Services s on s.ServiceID = it.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID

         WHERE (s.InOut = 3 or s.InOut = 2)
           and st.CategoryId = 1
           and it.CreatedTime between :fromDate and :toDate
         union all
         select ot.AccountID,
                ot.CreatedTime,
                ot.ServiceID,
                ot.RoomID,
                ot.SourceID,
                0         as InAmount,
                ot.Amount as OutAmount,
                s.InOut
         FROM OutputTransactions ot
                  inner join Services s on s.ServiceID = ot.ServiceID
                  inner join ServicesType st on s.ServiceType = st.ServicesTypeID
         WHERE s.InOut = 1
           and st.CategoryId = 1
           and ot.CreatedTime between :fromDate and :toDate
     ) t
inner join 
     (select AccountID from Accounts
            WHERE CreatedTime between :fromDate and :toDate) as a on a.AccountID = t.AccountID
WHERE convert(varchar(10), a.CreateTime, 102) = convert(varchar(10), dateadd(DAY, 0, datediff(day, 0, t.CreatedTime)), 102)
GROUP BY dateadd(DAY, 0, datediff(day, 0, CreatedTime))

推荐阅读