Início / dba / Perguntas / 64196
Accepted
John
Asked: 2014-04-30 17:02:35 +0800 CST

Como posso eliminar essa operação de busca de índice dispendiosa da minha consulta?

  • 772

Eu tenho duas tabelas no Sql Server 2008 que se parecem com isto:

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


Charge_Summary
    Dataset,
    Department,
    Charge_ID,
    dos_month,
    Ins1_Category

A saída que estou procurando seria mais ou menos assim: Dataset, Department, Ins1_Category, dos_month, Post_Month, Total_Payments, Total_Charges, Previous_Balance -- Definido como valor total para todas as transações correspondentes com um post_month anterior ao mês atual

O SQL que estou usando abaixo fornece a saída esperada, mas há uma busca de índice no plano de execução que ocupa mais de 90% do custo. Isso está em uma operação de datawarehouse, portanto, não há nenhuma outra atividade no servidor além dessa consulta, nenhuma outra leitura ou gravação com que se preocupar. O tempo de execução de aproximadamente 30 minutos é aceitável para o que está sendo usado, mas ainda gostaria de alguns conselhos se houver uma maneira melhor de fazer isso.

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
sql-server performance

2 respostas

  1. Best Answer

    Além das técnicas de T-SQL discutidas no artigo de Aaron Bertrands, "Melhores abordagens para totais de execução agrupados" , talvez você queira examinar um SQLCLRprocedimento (se estiver executando menos do que o SQL Server 2012).

    As principais vantagens são que um SQLCLRprocedimento requer apenas uma única varredura dos registros de origem e se beneficia do aumento da velocidade de execução do código compilado. A amostra abaixo foi retirada da postagem de Aaron:

    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);

    Dados de amostra

    ;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;

    Procedimento SQL CLR DDL

    CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EC0861530000000000000000E00002210B010B00000E00000006000000000000DE2C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000882C00005300000000400000A802000000000000000000000000000000000000006000000C000000502B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C02C0000000000004800000002000500E02100007009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30060046010000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A0772350000706F0900000A1A8D0B000001130A110A1672E40200701E730A00000AA2110A1772000300701F1F730A00000AA2110A18721A0300701B1D18730B00000AA2110A1972340300701B1F0918730B00000AA2110A0C16730C00000A130516730C00000A1306280D00000A130707176F0E00000A130808730F00000A1309110711096F1000000A11086F1100000A2C761108166F1200000A13041108166F1200000A0D1108186F1300000A13050911042E0B16730C00000A1306091304110916096F1400000A1109171108176F1500000A6F1600000A11091811056F1700000A11091911061105281800000A2513066F1700000A110711096F1900000A11086F1100000A2D9411076F1A00000ADE0A072C06076F1B00000ADCDE0A062C06066F1B00000ADC2A0000413400000200000017000000180100002F0100000A00000000000000020000000B000000300100003B0100000A000000000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000000020000237E00006C020000D002000023537472696E6773000000003C05000050030000235553008C0800001000000023475549440000009C080000D400000023426C6F620000000000000002000001471402000900000000FA253300160000010000001500000002000000020000001C0000000400000001000000010000000200000000000A0001000000000006003900320006006D005A000B00810000000600B00090000600D00090000A001F0104010A004B0135010A006C0159010A007E0135010A00980159010A00B20104010A00BE01F8000600C80132000A00D00104010A00DB0104010A00EC0135010A00FA01F8000A00180204010A00370259010600660232000600BC02320000000000010000000000010001000100100018000000050001000100502000000000960040000A000100D82100000000861854000E000100110054001200210054001800290054000E00310054000E00390054002200410079010E00490054000E004900890127005100A2012200590054002D005900540034006900540018007100E3013D0049000A024200910054004900790026025000990044025600990049025A00990052025F0091005D02650099006F026B0091007B027100910087027800690092027F0079009E0250007900AD020E00A900C8020E00090054000E00200023001D002E000B00A1002E001300AA002E001B00B3008800048000000000000000000000000000000000EE00000002000000000000000000000001002900000000000200000000000000000000000100F8000000000000000000003C4D6F64756C653E004461746162617365312E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374005469636B657452756E6E696E67546F74616C73002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C4D657461446174610053716C44625479706500446563696D616C0053716C436F6E746578740053716C50697065006765745F506970650053716C4461746152656164657200436F6D6D616E644265686176696F7200457865637574655265616465720053716C446174615265636F72640053656E64526573756C7473537461727400446244617461526561646572005265616400476574496E74333200476574446563696D616C00536574496E743332004461746554696D65004765744461746554696D65005365744461746554696D6500536574446563696D616C006F705F4164646974696F6E0053656E64526573756C7473526F770053656E64526573756C7473456E640049446973706F7361626C6500446973706F736500003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000082AD0D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E005400690063006B006500740041006D006F0075006E0074000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E005300700065006500640069006E0067005400690063006B006500740073002000410053002000530054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004F0052004400450052002000420059000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000001B4C006900630065006E00730065004E0075006D00620065007200001949006E0063006900640065006E007400440061007400650000195400690063006B006500740041006D006F0075006E0074000019520075006E006E0069006E00670054006F00740061006C000000008361CA6040029B4991925394772E0D1F0008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E05200101121D062002010E1131082004010E11310505040000123D06200112411145062001011D122D052001011249032000020420010808052001113508052002010808052001115108062002010811510620020108113508000211351135113518070B121D12251D122D080811351135123D124112491D122D0801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000EC08615300000000020000001C0100006C2B00006C0D000052534453A775B484FE7A394C846393C91D55D95C01000000633A5C55736572735C5061756C2057686974655C536B7944726976655C446F63756D656E74735C56697375616C2053747564696F20323031325C50726F6A656374735C4461746162617365315C4461746162617365315C6F626A5C52656C656173655C4461746162617365312E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B02C00000000000000000000CE2C0000002000000000000000000000000000000000000000000000C02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE [dbo].[TicketRunningTotals] 
AS EXTERNAL NAME [Demo].[StoredProcedures].[TicketRunningTotals];

    Uso

    EXECUTE dbo.TicketRunningTotals;

    Resultado

    Exemplo de saída

    Plano de execução

    Plano de execução

    Código-fonte do SQL CLR

    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();

            }
        }
    }
}
    • 12

  2. Eu nem preciso ver o plano de execução neste.

    A subconsulta correlacionada que você usa para calcular o total em execução causará uma "autojunção triangular", para cada linha, você obtém todos os predecessores de sua tabela de fatos e, portanto, uma contagem de linhas impressionante (pense nas linhas de n²).

    Se você quiser ajustar essa consulta, precisará reescrever o total acumulado de maneira mais eficiente. No SQL 2012 isso seria muito fácil, mas em 2008 você precisa ser um pouco mais criativo.

    No topo da minha cabeça, 2 opções seriam pré-agregar agrupadas por mês como uma primeira etapa e, em uma tabela temporária, atualizar o total em execução de forma iterativa ou usando um CTE recursivo. É um problema bastante comum, então 5 minutos no Google devem encontrar toneladas de postagens de blog com muito mais detalhes do que eu posso aqui.

    • 1

relate perguntas