会计软件发票列表SQL跨年度查询超时问题求助

算力阿峰

2026-6-27

跨年度发票查询超时问题排查与解决

问题背景

数据库中所有发票相关表均包含FinancialPeriodFK（财务期间）字段，查询2024年发票列表正常，但切换至2023年查询时出现超时错误。2023年有6600张发票，2018至2024年总计106000条发票记录。

表结构

Sales.InvoiceHeader（发票头表）

CREATE TABLE [Sales].[InvoiceHeader](
    [InvoiceID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceNumber] [int] NOT NULL,
    [InvoiceNumber1] [nvarchar](50) NULL,
    [OnlineInvoiceFlag] [bit] NULL,
    [RecordType] [smallint] NULL,
    [InvoiceKindFK] [int] NOT NULL,
    [StoreFK] [int] NULL,
    [IsOther] [bit] NULL,
    [OtherName] [nvarchar](100) NULL,
    [OtherNationalNo] [nvarchar](50) NULL,
    [AccountGroupFK] [int] NULL,
    [AccountFK] [int] NULL,
    [PaymentTermFK] [int] NULL,
    [DeliverAddress] [nvarchar](256) NULL,
    [Date] [char](10) NULL,
    [Time] [datetime] NULL,
    [Description] [nvarchar](256) NULL,
    [SubTotal] [decimal](18, 0) NULL,
    [Reduction] [decimal](18, 0) NULL,
    [Extra] [decimal](18, 0) NULL,
    [Discount] [decimal](18, 0) NULL,
    [ProjectFK] [int] NULL,
    [CostCenterFK] [nvarchar](10) NULL,
    [MarketerAccountFK] [nvarchar](50) NULL,
    [MarketingCost] [decimal](18, 0) NULL,
    [DriverAccountFK] [nvarchar](50) NULL,
    [DriverWages] [decimal](18, 0) NULL,
    [SettelmentDate] [char](10) NULL,
    [DueDate] [char](10) NULL,
    [PrintCount] [tinyint] NULL,
    [InvoiceFK] [int] NULL,
    [LetterFK] [int] NULL,
    [FinancialPeriodFK] [tinyint] NOT NULL,
    [CompanyInfoFK] [tinyint] NULL,
    [OldSysDoc] [nvarchar](50) NULL,
 CONSTRAINT [PK_InvoiceHeader] PRIMARY KEY CLUSTERED 
(
    [FinancialPeriodFK] ASC,
    [InvoiceKindFK] ASC,
    [InvoiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [Sales].[InvoiceHeader] ADD  CONSTRAINT [DF_InvoiceHeader_Time]  DEFAULT (getdate()) FOR [Time]
GO

Sales.InvoiceDetail（发票明细表）

CREATE TABLE [Sales].[InvoiceDetail](
    [InvoiceFK] [int] NOT NULL,
    [InvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceNumberFK] [int] NOT NULL,
    [InvoiceKindFK] [int] NOT NULL,
    [RecordType] [smallint] NULL,
    [ItemDescription] [nvarchar](256) NULL,
    [Date] [char](10) NULL,
    [Time] [datetime] NULL,
    [StoreFK] [int] NOT NULL,
    [ProductFK] [int] NULL,
    [OrderQty] [float] NULL,
    [UnitPrice] [decimal](18, 0) NULL,
    [BackPrice] [decimal](18, 0) NULL,
    [UnitPriceDiscountPercent] [decimal](18, 0) NULL,
    [DiscountAmount] [decimal](18, 0) NULL,
    [UnitPriceVatPercent] [decimal](18, 0) NULL,
    [VatAmount] [decimal](18, 0) NULL,
    [UnitPriceTaxPercent] [decimal](18, 0) NULL,
    [TaxAmount] [decimal](18, 0) NULL,
    [TransportCost] [decimal](18, 0) NULL,
    [LineTotal] [decimal](18, 0) NULL,
    [WayBillNumber] [nvarchar](20) NULL,
    [ContractNumber] [nvarchar](20) NULL,
    [VehicleNo] [nvarchar](20) NULL,
    [DeliverFK] [int] NULL,
    [NTSW] [nvarchar](512) NULL,
    [FinancialPeriodFK] [tinyint] NOT NULL,
    [CompanyInfoFK] [tinyint] NULL,
 CONSTRAINT [PK_InvoiceDetail] PRIMARY KEY CLUSTERED 
(
    [FinancialPeriodFK] ASC,
    [InvoiceKindFK] ASC,
    [InvoiceDetailID] ASC,
    [InvoiceFK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [Sales].[InvoiceDetail] ADD  CONSTRAINT [DF_InvoiceDetail_Time]  DEFAULT (getdate()) FOR [Time]
GO

ALTER TABLE [Sales].[InvoiceDetail]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceDetail_InvoiceHeader] FOREIGN KEY([FinancialPeriodFK], [InvoiceKindFK], [InvoiceFK])
REFERENCES [Sales].[InvoiceHeader] ([FinancialPeriodFK], [InvoiceKindFK], [InvoiceID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [Sales].[InvoiceDetail] CHECK CONSTRAINT [FK_InvoiceDetail_InvoiceHeader]
GO

超时的查询语句

SELECT M.InvoiceID,
       M.InvoiceNumber,
       M.InvoiceNumber1,
       M.IsOther,
       M.OtherName,
       M.OtherNationalNo,
       M.Date,
       (SUM(ISNULL(D.LineTotal, 0)) + SUM(ISNULL(M.Extra, 0)) - SUM(ISNULL(M.Reduction, 0)) + SUM(ISNULL(D.DiscountAmount, 0))) AS SumTotal,
       (SUM(ISNULL(D.DiscountAmount, 0))) AS Discount,
       (SUM(ISNULL(D.VatAmount, 0))) AS Vat,
       (SUM(ISNULL(D.TaxAmount, 0))) AS Tax,
       (SUM(ISNULL(D.LineTotal, 0)) - SUM(ISNULL(D.VatAmount, 0)) - SUM(ISNULL(D.TaxAmount, 0)) - SUM(ISNULL(M.Extra, 0)) - SUM(ISNULL(M.Reduction, 0))) AS TotalNet,
       M.OnlineInvoiceFlag,
       M.RecordType,
       M.InvoiceKindFK,
       M.StoreFK,
       M.AccountFK,
       M.PaymentTermFK,
       M.DeliverAddress,
       (SELECT MAX(DocumentFK)
        FROM Accounting.DocumentDetail
        WHERE ItemFK = @Item + CAST(M.InvoiceNumber AS nvarchar(10))
          AND documenttypeid = @DocumentTypeFK
          AND financialPeriodFK = @FinancialPeriodFK) AS DocumentNumber,
       M.Time,
       M.Description,
       M.SubTotal,
       M.Reduction,
       M.Extra,
       M.ProjectFK,
       M.CostCenterFK,
       M.MarketerAccountFK,
       M.MarketingCost,
       M.DriverAccountFK,
       M.DriverWages,
       M.SettelmentDate,
       M.DueDate,
       M.FinancialPeriodFK,
       M.CompanyInfoFK,
       M.PrintCount,
       M.LetterFK,
       M.InvoiceFK,
       dbo.getname(M.AccountFK, M.AccountGroupFK, M.FinancialPeriodFK) AS AccountTopic,
       AccountGroupFK,
       SUM(Banking.ReceivedCash.Price) AS ReceivedCash,
       SUM(Banking.ReceivedCheque.Price) AS ReceivedCheque
FROM Sales.InvoiceHeader M
    LEFT JOIN Sales.InvoiceDetail D ON M.InvoiceID = D.InvoiceFK
                                   AND M.InvoiceKindFK = D.InvoiceKindFK
                                   AND D.FinancialPeriodFK = M.FinancialPeriodFK
    LEFT JOIN Banking.ReceivedCash ON M.InvoiceNumber = Banking.ReceivedCash.SalesInvoiceHeaderFK
                                  AND Banking.ReceivedCash.FinancialPeriodFK = M.FinancialPeriodFK
    LEFT JOIN Banking.ReceivedCheque ON M.InvoiceNumber = Banking.ReceivedCheque.SalesInvoiceHeaderFK
                                    AND Banking.ReceivedCheque.FinancialPeriodFK = M.FinancialPeriodFK
WHERE ( (M.InvoiceKindFK = @InvoiceKindFK)
    AND (M.FinancialPeriodFK = @FinancialPeriodFK))
GROUP BY M.InvoiceID,
         M.InvoiceNumber,
         M.InvoiceNumber1,
         M.IsOther,
         M.OtherName,
         M.OtherNationalNo,
         M.Date,
         M.OnlineInvoiceFlag,
         M.RecordType,
         M.InvoiceKindFK,
         M.InvoiceNumber,
         M.StoreFK,
         M.AccountFK,
         M.PaymentTermFK,
         M.DeliverAddress,
         M.Time,
         M.Description,
         M.SubTotal,
         M.Reduction,
         M.Extra,
         M.ProjectFK,
         M.CostCenterFK,
         M.MarketerAccountFK,
         M.MarketingCost,
         M.DriverAccountFK,
         M.DriverWages,
         M.SettelmentDate,
         M.DueDate,
         M.FinancialPeriodFK,
         M.OldSysDoc,
         M.CompanyInfoFK,
         M.PrintCount,
         M.LetterFK,
         M.InvoiceFK,
         M.AccountGroupFK
ORDER BY M.StoreFK,
         M.InvoiceNumber;

排查与优化方案

1. 替换关联子查询为预聚合JOIN

原查询中的关联子查询会对每条发票头记录单独执行一次查询，6600条记录会触发6600次额外查询，严重拖慢速度。改为预聚合后JOIN：

-- 预聚合DocumentDetail数据
WITH DocDetailAgg AS (
    SELECT 
        ItemFK,
        MAX(DocumentFK) AS DocumentFK
    FROM Accounting.DocumentDetail
    WHERE documenttypeid = @DocumentTypeFK
      AND financialPeriodFK = @FinancialPeriodFK
    GROUP BY ItemFK
)

主查询中用LEFT JOIN DocDetailAgg DD ON DD.ItemFK = @Item + CAST(M.InvoiceNumber AS nvarchar(10))替换子查询，SELECT列表中用DD.DocumentFK获取结果。

2. 提前聚合收款表数据，避免JOIN后数据膨胀

直接LEFT JOIN收款表会导致数据重复：一张发票有多笔收款时，会生成多条重复的发票记录，后续GROUP BY需要处理大量冗余数据。提前聚合收款表：

-- 预聚合ReceivedCash数据
WITH ReceivedCashAgg AS (
    SELECT 
        SalesInvoiceHeaderFK,
        FinancialPeriodFK,
        SUM(Price) AS TotalReceivedCash
    FROM Banking.ReceivedCash
    GROUP BY SalesInvoiceHeaderFK, FinancialPeriodFK
),
-- 预聚合ReceivedCheque数据
ReceivedChequeAgg AS (
    SELECT 
        SalesInvoiceHeaderFK,
        FinancialPeriodFK,
        SUM(Price) AS TotalReceivedCheque
    FROM Banking.ReceivedCheque
    GROUP BY SalesInvoiceHeaderFK, FinancialPeriodFK
)

主查询中JOIN这两个聚合后的CTE，SELECT列表用ISNULL(RC.TotalReceivedCash, 0)代替SUM(Banking.ReceivedCash.Price)。

3. 添加针对性非聚集索引

为以下表创建复合索引，加速查询过滤与聚合：

Accounting.DocumentDetail：

CREATE NONCLUSTERED INDEX IX_DocumentDetail_ItemTypePeriod 
ON Accounting.DocumentDetail (ItemFK, documenttypeid, financialPeriodFK)
INCLUDE (DocumentFK);

Banking.ReceivedCash：

CREATE NONCLUSTERED INDEX IX_ReceivedCash_InvoicePeriod 
ON Banking.ReceivedCash (SalesInvoiceHeaderFK, FinancialPeriodFK)
INCLUDE (Price);

Banking.ReceivedCheque：

CREATE NONCLUSTERED INDEX IX_ReceivedCheque_InvoicePeriod 
ON Banking.ReceivedCheque (SalesInvoiceHeaderFK, FinancialPeriodFK)
INCLUDE (Price);

4. 更新统计信息

旧年度数据的统计信息可能过期，导致查询优化器生成低效执行计划：

UPDATE STATISTICS Sales.InvoiceHeader;
UPDATE STATISTICS Sales.InvoiceDetail;
UPDATE STATISTICS Accounting.DocumentDetail;
UPDATE STATISTICS Banking.ReceivedCash;
UPDATE STATISTICS Banking.ReceivedCheque;

5. 优化后的完整查询示例

WITH DocDetailAgg AS (
    SELECT 
        ItemFK,
        MAX(DocumentFK) AS DocumentFK
    FROM Accounting.DocumentDetail
    WHERE documenttypeid = @DocumentTypeFK
      AND financialPeriodFK = @FinancialPeriodFK
    GROUP BY ItemFK
),
ReceivedCashAgg AS (
    SELECT 
        SalesInvoiceHeaderFK,
        FinancialPeriodFK,
        SUM(Price) AS TotalReceivedCash
    FROM Banking.ReceivedCash
    GROUP BY SalesInvoiceHeaderFK, FinancialPeriodFK
),
ReceivedChequeAgg AS (
    SELECT 
        SalesInvoiceHeaderFK,
        FinancialPeriodFK,
        SUM(Price) AS TotalReceivedCheque
    FROM Banking.ReceivedCheque
    GROUP BY SalesInvoiceHeaderFK, FinancialPeriodFK
),
InvoiceDetailAgg AS (
    SELECT 
        InvoiceFK,
        InvoiceKindFK,
        FinancialPeriodFK,
        SUM(ISNULL(LineTotal, 0)) AS SumLineTotal,
        SUM(ISNULL(DiscountAmount, 0)) AS SumDiscountAmount,
        SUM(ISNULL(VatAmount, 0)) AS SumVatAmount,
        SUM(ISNULL(TaxAmount, 0)) AS SumTaxAmount
    FROM Sales.InvoiceDetail
    GROUP BY InvoiceFK, InvoiceKindFK, FinancialPeriodFK
)
SELECT 
    M.InvoiceID,
    M.InvoiceNumber,
    M.InvoiceNumber1,
    M.IsOther,
    M.OtherName,
    M.OtherNationalNo,
    M.Date,
    (ISNULL(IDA.SumLineTotal, 0) + ISNULL(M.Extra, 0) - ISNULL(M.Reduction, 0) + ISNULL(IDA.SumDiscountAmount, 0)) AS SumTotal,
    ISNULL(IDA.SumDiscountAmount, 0) AS Discount,
    ISNULL(IDA.SumVatAmount, 0) AS Vat,
    ISNULL(IDA.SumTaxAmount, 0) AS Tax,
    (ISNULL(IDA.SumLineTotal, 0) - ISNULL(IDA.SumVatAmount, 0) - ISNULL(IDA.SumTaxAmount, 0) - ISNULL(M.Extra, 0) - ISNULL(M.Reduction, 0)) AS TotalNet,
    M.OnlineInvoiceFlag,
    M.RecordType,
    M.InvoiceKindFK,
    M.StoreFK,
    M.AccountFK,
    M.PaymentTermFK,
    M.DeliverAddress,
    DD.DocumentFK AS DocumentNumber,
    M.Time,
    M.Description,
    M.SubTotal,
    M.Reduction,
    M.Extra,
    M.ProjectFK,
    M.CostCenterFK,
    M.MarketerAccountFK,
    M.MarketingCost,
    M.DriverAccountFK,
    M.DriverWages,
    M.SettelmentDate,
    M.DueDate,
    M.FinancialPeriodFK,
    M.CompanyInfoFK,
    M.PrintCount,
    M.LetterFK,
    M.InvoiceFK,
    dbo.getname(M.AccountFK, M.AccountGroupFK, M.FinancialPeriodFK) AS AccountTopic,
    M.AccountGroupFK,
    ISNULL(RC.TotalReceivedCash, 0) AS ReceivedCash,
    ISNULL(RQ.TotalReceivedCheque, 0) AS ReceivedCheque
FROM Sales.InvoiceHeader M
LEFT JOIN InvoiceDetailAgg IDA ON M.InvoiceID = IDA.InvoiceFK
                              AND M.InvoiceKindFK = IDA.InvoiceKindFK
                              AND M.FinancialPeriodFK = IDA.FinancialPeriodFK
LEFT JOIN DocDetailAgg DD ON DD.ItemFK = @Item + CAST(M.InvoiceNumber AS nvarchar(10))
LEFT JOIN ReceivedCashAgg RC ON M.InvoiceNumber = RC.SalesInvoiceHeaderFK
                            AND M.FinancialPeriodFK = RC.FinancialPeriodFK
LEFT JOIN ReceivedChequeAgg RQ ON M.InvoiceNumber = RQ.SalesInvoiceHeaderFK
                              AND M.Financial

会计软件发票列表SQL跨年度查询超时问题求助

问题背景 #

表结构 #

Sales.InvoiceHeader（发票头表） #

Sales.InvoiceDetail（发票明细表） #

超时的查询语句 #

排查与优化方案 #

1. 替换关联子查询为预聚合JOIN #

2. 提前聚合收款表数据，避免JOIN后数据膨胀 #

3. 添加针对性非聚集索引 #

4. 更新统计信息 #

5. 优化后的完整查询示例 #