sql performance in accounting software invoice list

Question

I have database that include FinancialYears in each table for Invoices, the problem is that when I call query to show the Invoice list for example for 2024 and then change the app to 2023 to get query on last year it gives me a time out error this is my invoice header table schema

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

and following is my detail invoice table schema

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

Note: in 2023 I have 6600 invoice inserted in my table. and in total from 2018 to 2024 I have 106000 record inserted for my invoices. following is my query :

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;

Answer 1

It appears that your GROUP BY is grouping the entirety of each InvoiceHeader row, while the other table references are only used to calculate sums for details and payments. In that case, I believe that is would be simpler to select directly from InvoiceHeader only at the top level and use CROSS APPLY subqueries to calculate the various sums.

The other thing I see, and this is a big red flag, is that your posted query appears to have multiple independent one-to-many joins. This will almost never yield the correct result. If an invoice had three details, two cash payments and two cheque payments, the details would be overstated by a factor of 4 and payments would each be overstated by a factor of 6. The fix is to isolate each one-to-many relationship into a separate CROSS APPLY and calculate the totals of each independently.

The DocumentNumber can also (optionally) be moved to a CROSS APPLY, solely for the purpose of reducing clutter in the main select list.

These CROSS APPLY results can then be referenced in the top level select list.

The updated query would be something like:

SELECT M.InvoiceID,
       M.InvoiceNumber,
       M.InvoiceNumber1,
       M.IsOther,
       M.OtherName,
       M.OtherNationalNo,
       M.Date,
       DSUM.LineTotal + DSUM.Extra - DSUM.Reduction + DSUM.Discount AS SumTotal,
       DSUM.Discount,
       DSUM.Vat,
       DSUM.Tax,
       DSUM.LineTotal - DSUM.Vat - DSUM.Tax - DSUM.Extra - DSUM.Reduction AS TotalNet,
       M.OnlineInvoiceFlag,
       M.RecordType,
       M.InvoiceKindFK,
       M.StoreFK,
       M.AccountFK,
       M.PaymentTermFK,
       M.DeliverAddress,
       DN.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,
       RCSUM.ReceivedCash,
       RQSUM.ReceivedCheque
FROM Sales.InvoiceHeader M
CROSS APPLY (
    SELECT
        ISNULL(SUM(ISNULL(D.LineTotal, 0)), 0) AS LineTotal,
        ISNULL(SUM(ISNULL(M.Extra, 0)), 0) AS Extra,
        ISNULL(SUM(ISNULL(M.Reduction, 0)), 0) AS Reduction,
        ISNULL((SUM(ISNULL(D.DiscountAmount, 0)), 0) AS Discount,
        ISNULL((SUM(ISNULL(D.VatAmount, 0)), 0) AS Vat,
        ISNULL((SUM(ISNULL(D.TaxAmount, 0)), 0) AS Tax
    FROM Sales.InvoiceDetail D
    WHERE D.InvoiceFK = M.InvoiceID
    AND D.InvoiceKindFK = M.InvoiceKindFK
    AND D.FinancialPeriodFK = M.FinancialPeriodFK
) DSUM
CROSS APPLY (
    SELECT ISNULL(SUM(RC.Price), 0) AS ReceivedCash
    FROM Banking.ReceivedCash RC
    WHERE RC.SalesInvoiceHeaderFK = M.InvoiceNumber
    AND RC.FinancialPeriodFK = M.FinancialPeriodFK
) RCSUM
CROSS APPLY (
    SELECT ISNULL(SUM(Banking.ReceivedCheque.Price), 0) AS ReceivedCheque
    FROM Banking.ReceivedCheque RQ
    WHERE RQ.SalesInvoiceHeaderFK = M.InvoiceNumber
    AND RQ.FinancialPeriodFK = M.FinancialPeriodFK
) RQSUM
CROSS APPLY (
    SELECT MAX(DocumentFK) AS DocumentNumber
    FROM Accounting.DocumentDetail DD
    WHERE DD.ItemFK = @Item + CAST(M.InvoiceNumber AS nvarchar(10))
    AND DD.documenttypeid = @DocumentTypeFK
    AND DD.financialPeriodFK = @FinancialPeriodFK
) DN
WHERE ( (M.InvoiceKindFK = @InvoiceKindFK)
    AND (M.FinancialPeriodFK = @FinancialPeriodFK))
ORDER BY M.StoreFK,
         M.InvoiceNumber;

A CROSS APPLY is like an INNER JOIN to a subselect. For each usage above, the aggregate functions will always produce a single scalar result, so each should produce exactly one row. (If that was not the case, an OUTER APPLY would have been appropriate - equivalent to a LEFT JOIN to a subselect.)

I wrapped the SUM()s up in additional ISNULL() functions to ensure a zero result if no matching rows were found. The inner ISNULL() function references could be eliminated if you don't mind the "Null value is eliminated by an aggregate or other SET operation" warnings.

As siggemannen already pointed out in an earlier comment, the dbo.getname() function call may be significantly impacting performance. I suggest testing your query both with and without this function call. If significantly different, review the function to see if its performance can be improved or if its logic can be merged into the above query. Another option might be to add AccountTopic as a persistent computed column in the InvoiceHeader table.

I presume that:

• Every invoice should have at least one and perhaps multiple detail rows.
• Every invoice may have zero, one, or multiple cash payment rows.
• Every invoice may have zero, one, or multiple cheque payment rows.

Be sure to test the final query using all combinations of the above conditions, carefully checking that the calculated sums are correct.

Also, check your InvoiceDetail join conditions. The referenced columns are not the same as defined in your FK_InvoiceDetail_InvoiceHeader foreign key constraint. (The other three table definitions were not posted, but might also be worth a review.)