SQL Server Query Performance – Eliminate Costly Index Seek Operation

index-tuningperformancequery-performancesql server

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.

Link to execution plan on pastebin

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.Ins1_Category = c1.Ins1_Category
    ) as previous_balance
FROM
    Transaction_Details t1
LEFT JOIN
    Charge_Summary c1
ON
    t1.Dataset = c1.Dataset AND
    t1.Charge_ID = c1.Charge_ID
WHERE
    t1.Charge_ID is not null
GROUP BY
    t1.Dataset,
    t1.Department,
    c1.Ins1_Category,
    t1.dos_month,
    t1.post_month

Best Answer

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 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EC0861530000000000000000E00002210B010B00000E00000006000000000000DE2C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000882C00005300000000400000A802000000000000000000000000000000000000006000000C000000502B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C02C0000000000004800000002000500E02100007009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30060046010000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A0772350000706F0900000A1A8D0B000001130A110A1672E40200701E730A00000AA2110A1772000300701F1F730A00000AA2110A18721A0300701B1D18730B00000AA2110A1972340300701B1F0918730B00000AA2110A0C16730C00000A130516730C00000A1306280D00000A130707176F0E00000A130808730F00000A1309110711096F1000000A11086F1100000A2C761108166F1200000A13041108166F1200000A0D1108186F1300000A13050911042E0B16730C00000A1306091304110916096F1400000A1109171108176F1500000A6F1600000A11091811056F1700000A11091911061105281800000A2513066F1700000A110711096F1900000A11086F1100000A2D9411076F1A00000ADE0A072C06076F1B00000ADCDE0A062C06066F1B00000ADC2A0000413400000200000017000000180100002F0100000A00000000000000020000000B000000300100003B0100000A000000000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000000020000237E00006C020000D002000023537472696E6773000000003C05000050030000235553008C0800001000000023475549440000009C080000D400000023426C6F620000000000000002000001471402000900000000FA253300160000010000001500000002000000020000001C0000000400000001000000010000000200000000000A0001000000000006003900320006006D005A000B00810000000600B00090000600D00090000A001F0104010A004B0135010A006C0159010A007E0135010A00980159010A00B20104010A00BE01F8000600C80132000A00D00104010A00DB0104010A00EC0135010A00FA01F8000A00180204010A00370259010600660232000600BC02320000000000010000000000010001000100100018000000050001000100502000000000960040000A000100D82100000000861854000E000100110054001200210054001800290054000E00310054000E00390054002200410079010E00490054000E004900890127005100A2012200590054002D005900540034006900540018007100E3013D0049000A024200910054004900790026025000990044025600990049025A00990052025F0091005D02650099006F026B0091007B027100910087027800690092027F0079009E0250007900AD020E00A900C8020E00090054000E00200023001D002E000B00A1002E001300AA002E001B00B3008800048000000000000000000000000000000000EE00000002000000000000000000000001002900000000000200000000000000000000000100F8000000000000000000003C4D6F64756C653E004461746162617365312E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374005469636B657452756E6E696E67546F74616C73002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C4D657461446174610053716C44625479706500446563696D616C0053716C436F6E746578740053716C50697065006765745F506970650053716C4461746152656164657200436F6D6D616E644265686176696F7200457865637574655265616465720053716C446174615265636F72640053656E64526573756C7473537461727400446244617461526561646572005265616400476574496E74333200476574446563696D616C00536574496E743332004461746554696D65004765744461746554696D65005365744461746554696D6500536574446563696D616C006F705F4164646974696F6E0053656E64526573756C7473526F770053656E64526573756C7473456E640049446973706F7361626C6500446973706F736500003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000082AD0D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E005400690063006B006500740041006D006F0075006E0074000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E005300700065006500640069006E0067005400690063006B006500740073002000410053002000530054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004F0052004400450052002000420059000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000001B4C006900630065006E00730065004E0075006D00620065007200001949006E0063006900640065006E007400440061007400650000195400690063006B006500740041006D006F0075006E0074000019520075006E006E0069006E00670054006F00740061006C000000008361CA6040029B4991925394772E0D1F0008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E05200101121D062002010E1131082004010E11310505040000123D06200112411145062001011D122D052001011249032000020420010808052001113508052002010808052001115108062002010811510620020108113508000211351135113518070B121D12251D122D080811351135123D124112491D122D0801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000EC08615300000000020000001C0100006C2B00006C0D000052534453A775B484FE7A394C846393C91D55D95C01000000633A5C55736572735C5061756C2057686974655C536B7944726976655C446F63756D656E74735C56697375616C2053747564696F20323031325C50726F6A656374735C4461746162617365315C4461746162617365315C6F626A5C52656C656173655C4461746162617365312E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B02C00000000000000000000CE2C0000002000000000000000000000000000000000000000000000C02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE [dbo].[TicketRunningTotals] 
AS EXTERNAL NAME [Demo].[StoredProcedures].[TicketRunningTotals];

Usage

EXECUTE dbo.TicketRunningTotals;

Output

Output sample

Execution plan

Execution plan

SQL CLR source code

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void TicketRunningTotals()
    {
        using (var conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText =
                    @"
                    SELECT
                        ST.LicenseNumber,
                        ST.IncidentDate,
                        ST.TicketAmount
                    FROM dbo.SpeedingTickets AS ST
                    ORDER BY
                        ST.LicenseNumber,
                        ST.IncidentDate
                    ";

                var columns = new SqlMetaData[4]
                {
                    new SqlMetaData("LicenseNumber", SqlDbType.Int),
                    new SqlMetaData("IncidentDate", SqlDbType.Date),
                    new SqlMetaData("TicketAmount", SqlDbType.Decimal, 7, 2),
                    new SqlMetaData("RunningTotal", SqlDbType.Decimal, 9, 2)
                };

                int licenseNumber, lastLicenseNumber;
                var ticketAmount = 0m;
                var runningTotal = 0m;

                var pipe = SqlContext.Pipe;
                var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
                var record = new SqlDataRecord(columns);

                pipe.SendResultsStart(record);

                if (reader.Read())
                {
                    lastLicenseNumber = reader.GetInt32(0);

                    do
                    {
                        licenseNumber = reader.GetInt32(0);
                        ticketAmount = reader.GetDecimal(2);

                        if (licenseNumber != lastLicenseNumber)
                        {
                            runningTotal = 0m;
                            lastLicenseNumber = licenseNumber;
                        }

                        record.SetInt32(0, licenseNumber);
                        record.SetDateTime(1, reader.GetDateTime(1));
                        record.SetDecimal(2, ticketAmount);
                        record.SetDecimal(3, runningTotal += ticketAmount);

                        pipe.SendResultsRow(record);
                    }
                    while (reader.Read());
                }

                pipe.SendResultsEnd();

            }
        }
    }
}
Related Question