CODEWITHSUNDEEP
sqlsql-serversumsubqueryaggregate-functions
christodorov
christodorov

Reputation: 111

SQL Server - Aggregating a SUM for each row in a table with multiple conditions

I am trying to simulate something I do in excel fairly often within SQL however I must be doing something wrong because it pinned the server CPU to 100.

What I am specifically trying to do is aggregate a sum for each row within a table based on specific conditions. The query is below

  SELECT  custno,count(custno) as countUse into ##AllTransferMembers
  FROM [OLBRET_COPY].[dbo].[ActivityLogs]
  where ActivityCode in ('11020','11045','11053') 
    and DateTime >= '2018-12-20' and DateTime < '2018-12-26' --and ErrorCode in ('')
  group by custno   

  select d.custno, ActivityCode, d.Amount, d.DateTime, a.countUse, MobileDeviceMACId,ErrorMessage
    ,( select SUM(activitylogs.amount) from [OLBRET_COPY].[dbo].[ActivityLogs] where ActivityLogs.DateTime >= (d.DateTime-2) and ActivityLogs.DateTime <= d.DateTime and ActivityLogs.CustNo = d.CustNo and ActivityLogs.ErrorMessage = '')
    FROM [OLBRET_COPY].[dbo].[ActivityLogs] d
    inner join ##AllTransferMembers a on d.CustNo = a.CustNo
    where ActivityCode in ('11020','11045','11053') 
        and DateTime >= '2018-12-20' and DateTime < '2019-01-01' --and ErrorCode in ('') 
    order by CustNo,DateTime

    drop table ##AllTransferMembers

The problem should lie in the sub query sum aggregation because the other part of the query runs fairly often in a different setting without any issues. 

(select 
SUM(activitylogs.amount) 
from [OLBRET_COPY].[dbo].[ActivityLogs] 
where ActivityLogs.DateTime >= (d.DateTime-2) and ActivityLogs.DateTime <= d.DateTime 
and ActivityLogs.CustNo = d.CustNo 
and ActivityLogs.ErrorMessage = '')

I would appreciate someone explaining what is going on here causing the huge inefficiency, and if there is any way to approach the problem better. Unfortunately I do not have access to the execution plan.

Cheers,

Chris

Upvotes: 1

Views: 365

Answers (2)

christodorov
christodorov

Reputation: 111

Using everyone's help and some googling, this was my final query:

create index [ix_ActLogstmp] on [dbo].[allDataTodorov]
 (CustNo, ErrorMessage,DateTime) include (Amount);


 With tmp_AllTransferMembers as (
 Select 
 custno, 
 count(custno) as countUse
 from [SegmentationDatamart].[dbo].[allDataTodorov]
 where ActivityCode in ('11020','11045','11053') 
    and [DateTime] >= '2018-08-01' and DateTime < '2018-12-26'
group by custno)



select 
d.custno, 
ActivityCode, 
d.Amount, 
d.DateTime,
a.countUse, 
MobileDeviceMACId,
ErrorMessage, 
( select SUM([allDataTodorov].amount) 
      from [SegmentationDatamart].[dbo].[allDataTodorov]
      where [allDataTodorov].DateTime >= (d.DateTime-2) 
      and [allDataTodorov].DateTime <= d.DateTime 
      and [allDataTodorov].CustNo = d.CustNo 
      and [allDataTodorov].ErrorMessage is null) as [SUM_]

FROM [SegmentationDatamart].[dbo].[allDataTodorov] d
    inner join tmp_AllTransferMembers a on d.CustNo = a.CustNo
    where d.ActivityCode in ('11010','11011','11020','11045','11053') 
        and d.DateTime >= '2018-08-01' 
        and d.DateTime < '2018-12-26'
  order by d.CustNo,d.DateTime

  drop index [ix_ActLogstmp]

Some notes:

  1. I was able to get the execution plan through the 'Activity Monitor'=> 'Recent Expensive Queries'. Because the 'Amount' field was not in the index, SQL was doing a clustered key lookup and a sort which took up 97% of runtime for the query.

  2. Due to lack of permissions on the server, I had to moved the data to another server where I could create an index that had the 'Amount' field. The query ran in about 5 seconds with 600K lines.

Upvotes: 1

Angel M.
Angel M.

Reputation: 1358

First... I don't understand why you have a global temporary table for that purpose. It looks like you are storing too many duplicates. In any case, you should create an index like this:

CREATE INDEX IX_AllTransferMembers ON ##AllTransferMembers (CustNo)

In any case, you can do the same with a subquery using CTE like:

WITH tmp_AllTransferMembers AS (
  SELECT  custno,count(custno) as countUse 
  FROM [OLBRET_COPY].[dbo].[ActivityLogs]
  where ActivityCode in ('11020','11045','11053') 
    and DateTime >= '2018-12-20' and DateTime < '2018-12-26' 
  group by custno) 
SELECT d.custno, ActivityCode, d.Amount, d.DateTime, 
     a.countUse, MobileDeviceMACId,ErrorMessage
    ,( select SUM(activitylogs.amount) 
       from [OLBRET_COPY].[dbo].[ActivityLogs] 
       where ActivityLogs.DateTime >= (d.DateTime-2) 
       and ActivityLogs.DateTime <= d.DateTime 
       and ActivityLogs.CustNo = d.CustNo 
       and ActivityLogs.ErrorMessage = '')
    FROM [OLBRET_COPY].[dbo].[ActivityLogs] d
    inner join tmp_AllTransferMembers a on d.CustNo = a.CustNo
    where d.ActivityCode in ('11020','11045','11053') 
        and d.DateTime >= '2018-12-20' 
        and d.DateTime < '2019-01-01' --and ErrorCode in ('') 
  order by d.CustNo,d.DateTime

You have to ensure that you have an index on ActivityLogs(CustNo, ErrorMessage, DateTime) As you stated, cannot see the plan and reproduce it, so, it is not easy to ensure that will work better...

Additionally, previous to your code, you can use add the two lines below to show some details about where it takes the time. As it is a single table it will not be easy but may give you some clue (see details on the Message tab after running the query):

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

Upvotes: 1

Related Questions