snippetsqlModerate

How can I eliminate this costly index seek operation from my query?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thiscanseekqueryoperationeliminatehowindexfromcostly

Problem

I have two tables in Sql Server 2008 that look something like this:

Transaction_Details:
    Dataset,
    Department,
    Charge_ID,
    Transaction_Type,
    dos_month,
    post_month,
    amount

Charge_Summary
    Dataset,
    Department,
    Charge_ID,
    dos_month,
    Ins1_Category

The Output I'm looking for would look something like this:
Dataset,
Department,
Ins1_Category,
dos_month,
Post_Month,
Total_Payments,
Total_Charges,
Previous_Balance -- Defined as total amount for all matching transactions with a post_month previous to the current month

The SQL I'm using below gives the expected output, but there is one index seek in the execution plan taking up over 90% of the cost. This is in a datawarehouse operation, so there is no other activity on the server aside from this query, no other reads or writes to worry about. The execution time of ~30 minutes is acceptable for what it's being used for, but I'd still like some advice if there's a better way to do this.

```
SELECT
t1.Dataset,
t1.Department,
c1.Ins1_Category,
t1.dos_month,
t1.post_month,
SUM(case
when t1.transaction_type = 'payment' THEN t1.amount
ELSE 0
END
) AS total_payments,
SUM(case
when t1.transaction_type = 'adjustment' THEN t1.amount
ELSE 0
END
) AS total_adjustments,
SUM(case
when t1.transaction_type = 'charge' THEN t1.amount
ELSE 0
END
) AS total_charges,
(
SELECT
SUM(t2.amount)
FROM
Transaction_Details t2
LEFT JOIN
Charge_Summary c2
ON
t2.Dataset = c2.Dataset AND
t2.Charge_ID = c2.Charge_ID
WHERE
t1.Dataset = t2.Dataset AND
t1.Department = t2.Department AND
t1.dos_month = t2.dos_month AND
t1.post_month > t2.post_month AND
t2.Charge_ID IS NOT NULL AND
c2.

Solution

In addition to the T-SQL techniques discussed in Aaron Bertrands article, "Best approaches for grouped running totals", you might want to look at a SQLCLR procedure (if you are running less than SQL Server 2012).

The main advantages are that a SQLCLR procedure requires only a single scan of the source records, and benefits from the increased execution speed of compiled code. The sample below is taken from Aaron's post:

DDL

CREATE TABLE dbo.SpeedingTickets
(
  IncidentID    INT IDENTITY(1,1) PRIMARY KEY,
  LicenseNumber INT          NOT NULL,
  IncidentDate  DATE         NOT NULL,
  TicketAmount  DECIMAL(7,2) NOT NULL
);

CREATE UNIQUE INDEX x 
ON dbo.SpeedingTickets(LicenseNumber, IncidentDate) 
INCLUDE(TicketAmount);

Sample data

;WITH TicketAmounts(ID,Value) AS 
(
  -- 10 arbitrary ticket amounts
  SELECT i,p FROM 
  (
    VALUES(1,32.75),(2,75), (3,109),(4,175),(5,295),
          (6,68.50),(7,125),(8,145),(9,199),(10,250)
  ) AS v(i,p)
),
LicenseNumbers(LicenseNumber,[newid]) AS 
(
  -- 1000 random license numbers
  SELECT TOP (1000) 7000000 + number, n = NEWID()
    FROM [master].dbo.spt_values 
    WHERE number BETWEEN 1 AND 999999
    ORDER BY n
),
JanuaryDates([day]) AS 
(
  -- every day in January 2014
  SELECT TOP (31) DATEADD(DAY, number, '20140101') 
    FROM [master].dbo.spt_values 
    WHERE [type] = N'P' 
    ORDER BY number
),
Tickets(LicenseNumber,[day],s) AS
(
  -- match *some* licenses to days they got tickets
  SELECT DISTINCT l.LicenseNumber, d.[day], s = RTRIM(l.LicenseNumber) 
    FROM LicenseNumbers AS l CROSS JOIN JanuaryDates AS d
    WHERE CHECKSUM(NEWID()) % 100 = l.LicenseNumber % 100
    AND (RTRIM(l.LicenseNumber) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
    OR (RTRIM(l.LicenseNumber+1) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
)
INSERT dbo.SpeedingTickets(LicenseNumber,IncidentDate,TicketAmount)
SELECT t.LicenseNumber, t.[day], ta.Value 
  FROM Tickets AS t 
  INNER JOIN TicketAmounts AS ta
  ON ta.ID = CONVERT(INT,RIGHT(t.s,1))-CONVERT(INT,LEFT(RIGHT(t.s,2),1))
  ORDER BY t.[day], t.LicenseNumber;

SQL CLR procedure DDL

```
CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EC0861530000000000000000E00002210B010B00000E00000006000000000000DE2C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000882C00005300000000400000A802000000000000000000000000000000000000006000000C000000502B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C02C0000000000004800000002000500E02100007009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30060046010000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A0772350000706F0900000A1A8D0B000001130A110A1672E40200701E730A00000AA2110A1772000300701F1F730A00000AA2110A18721A0300701B1D18730B00000AA2110A1972340300701B1F0918730B00000AA2110A0C16730C00000A130516730C00000A1306280D00000A130707176F0E00000A130808730F00000A1309110711096F1000000A11086F1100000A2C761108166F1200000A13041108166F1200000A0D1108186F1300000A13050911042E0B16730C00000A1306091304110916096F1400000A1109171108176F1500000A6F1600000A11091811056F1700000A11091911061105281800000A2513066F1700000A110711096F1900000A11086F1100000A2D9411076F1A00000ADE0A072C06076F1B00000ADCDE0A062C06066F1B00000ADC2A0000413400000200000017000000180100002F0100000A00000000000000020000000B000000300100003B0100000A000000000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000000020000237E00006C020000D002000023537472696E6773000000003C05000050030000235553008C0800001000000023475549440000009C080000D400000023426C6F620000000000000002000001471402000900000000FA253300160000010000001500000002000000020000001C0000000400000001000000010000000200000000000A0001000000000006003900320006006D005A000B00810000000600B00090000600D00090000A001F0104010A004B0135010A006C0159010A007E0135010A00980159010A00B20104010A00BE01F8000600C80132000A00D00104010A00DB0104010A00EC0135010A00FA01F8000A00180204010A00370259010600660232000600BC02320000000000010000000000010001000100100018000000050001000100502000000000960040000A000100D82100000000861854000E000100110054001200210054001800290054000E00310054000E00390054002200410079010E00490054000E00490089012700

Code Snippets

CREATE TABLE dbo.SpeedingTickets
(
  IncidentID    INT IDENTITY(1,1) PRIMARY KEY,
  LicenseNumber INT          NOT NULL,
  IncidentDate  DATE         NOT NULL,
  TicketAmount  DECIMAL(7,2) NOT NULL
);

CREATE UNIQUE INDEX x 
ON dbo.SpeedingTickets(LicenseNumber, IncidentDate) 
INCLUDE(TicketAmount);

;WITH TicketAmounts(ID,Value) AS 
(
  -- 10 arbitrary ticket amounts
  SELECT i,p FROM 
  (
    VALUES(1,32.75),(2,75), (3,109),(4,175),(5,295),
          (6,68.50),(7,125),(8,145),(9,199),(10,250)
  ) AS v(i,p)
),
LicenseNumbers(LicenseNumber,[newid]) AS 
(
  -- 1000 random license numbers
  SELECT TOP (1000) 7000000 + number, n = NEWID()
    FROM [master].dbo.spt_values 
    WHERE number BETWEEN 1 AND 999999
    ORDER BY n
),
JanuaryDates([day]) AS 
(
  -- every day in January 2014
  SELECT TOP (31) DATEADD(DAY, number, '20140101') 
    FROM [master].dbo.spt_values 
    WHERE [type] = N'P' 
    ORDER BY number
),
Tickets(LicenseNumber,[day],s) AS
(
  -- match *some* licenses to days they got tickets
  SELECT DISTINCT l.LicenseNumber, d.[day], s = RTRIM(l.LicenseNumber) 
    FROM LicenseNumbers AS l CROSS JOIN JanuaryDates AS d
    WHERE CHECKSUM(NEWID()) % 100 = l.LicenseNumber % 100
    AND (RTRIM(l.LicenseNumber) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
    OR (RTRIM(l.LicenseNumber+1) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
)
INSERT dbo.SpeedingTickets(LicenseNumber,IncidentDate,TicketAmount)
SELECT t.LicenseNumber, t.[day], ta.Value 
  FROM Tickets AS t 
  INNER JOIN TicketAmounts AS ta
  ON ta.ID = CONVERT(INT,RIGHT(t.s,1))-CONVERT(INT,LEFT(RIGHT(t.s,2),1))
  ORDER BY t.[day], t.LicenseNumber;

CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EC0861530000000000000000E00002210B010B00000E00000006000000000000DE2C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000882C00005300000000400000A802000000000000000000000000000000000000006000000C000000502B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C02C0000000000004800000002000500E02100007009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30060046010000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A0772350000706F0900000A1A8D0B000001130A110A1672E40200701E730A00000AA2110A1772000300701F1F730A00000AA2110A18721A0300701B1D18730B00000AA2110A1972340300701B1F0918730B00000AA2110A0C16730C00000A130516730C00000A1306280D00000A130707176F0E00000A130808730F00000A1309110711096F1000000A11086F1100000A2C761108166F1200000A13041108166F1200000A0D1108186F1300000A13050911042E0B16730C00000A1306091304110916096F1400000A1109171108176F1500000A6F1600000A11091811056F1700000A11091911061105281800000A2513066F1700000A110711096F1900000A11086F1100000A2D9411076F1A00000ADE0A072C06076F1B00000ADCDE0A062C06066F1B00000ADC2A0000413400000200000017000000180100002F0100000A00000000000000020000000B000000300100003B0100000A000000000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000000020000237E00006C020000D002000023537472696E6773000000003C05000050030000235553008C0800001000000023475549440000009C080000D400000023426C6F620000000000000002000001471402000900000000FA253300160000010000001500000002000000020000001C0000000400000001000000010000000200000000000A0001000000000006003900320006006D005A000B00810000000600B00090000600D00090000A001F0104010A004B0135010A006C0159010A007E0135010A00980159010A00B20104010A00BE01F8000600C80132000A00D00104010A00DB0104010A00EC0135010A00FA01F8000A00180204010A00370259010600660232000600BC02320000000000010000000000010001000100100018000000050001000100502000000000960040000A000100D82100000000861854000E000100110054001200210054001800290054000E00310054000E00390054002200410079010E00490054000E004900890127005100A2012200590054002D005900540034006900540018007100E3013D0049000A024200910054004900790026025000990044025600990049025A00990052025F0091005D02650099006F

EXECUTE dbo.TicketRunningTotals;

Context

StackExchange Database Administrators Q#64196, answer score: 12

Revisions (0)

No revisions yet.