Sql-Server

如何從查詢中消除這種代價高昂的索引查找操作？

  • August 16, 2021

我在 Sql Server 2008 中有兩個表，如下所示：

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


Charge_Summary
   Dataset,
   Department,
   Charge_ID,
   dos_month,
   Ins1_Category

我正在尋找的輸出看起來像這樣：Dataset、Department、Ins1_Category、dos_month、Post_Month、Total_Payments、Total_Charges、Previous_Balance——定義為所有匹配交易的總金額，其中 post_month 在目前月份之前

我在下面使用的 SQL 給出了預期的輸出，但是執行計劃中有一個索引查找佔用了 90% 以上的成本。這是在數據倉庫操作中，因此除了此查詢之外，伺服器上沒有其他活動，無需擔心其他讀取或寫入。大約 30 分鐘的執行時間對於它的用途來說是可以接受的，但如果有更好的方法可以做到這一點，我仍然需要一些建議。

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

除了 Aaron Bertrands 文章“分組執行總計的最佳方法”中討論的 T-SQL 技術之外，您可能還想查看一個SQLCLR過程（如果您執行的不是 SQL Server 2012）。

主要優點是一個SQLCLR過程只需要對源記錄進行一次掃描，並受益於編譯程式碼的執行速度提高。下面的範例取自 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);

樣本數據

;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 過程 DDL

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

用法

EXECUTE dbo.TicketRunningTotals;

輸出

輸出樣本

執行計劃

執行計劃

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

           }
       }
   }
}

引用自：https://dba.stackexchange.com/questions/64196

相關問答

Sql-Server

如何提高查詢性能？

  • May 24, 2021
檢視問答
Sql-Server

為什麼我的 EXISTS 查詢執行索引掃描而不是索引查找？

  • May 12, 2020
檢視問答
Sql-Server

即使通過使其以並行模式執行來改進查詢，查詢性能也會更差

  • March 30, 2020
檢視問答
Sql-Server

當我在兩個索引視圖上通過聚集索引進行內部聯接時，為什麼查詢處理器不能生成帶有 FORCESEEK 提示的查詢計劃？

  • December 12, 2019
檢視問答
Sql-Server

包含列的非聚集索引覆蓋率

  • December 10, 2019
檢視問答
Sql-Server

分區掃描對大小數據的影響

  • December 9, 2019
檢視問答