2

I am currently looking into a query that retrieves lots of records, and it's used for reporting different balances during each month of the year(periods). The original query joins a few extra tables but the main culprit is this one:

SELECT [GLHistory].[BranchID] AS [BranchID]
      ,[GLHistory].[LedgerID] AS [LedgerID]
      ,[GLHistory].[AccountID] AS [AccountID]
      ,[GLHistory].[SubID] AS [SubID]
      ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
      ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
  FROM [GLHistory] [GLHistory]
         INNER JOIN [FinPeriod] [FinPeriod]
                    ON [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
 WHERE [GLHistory].CompanyID = 7
   AND (   [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13)
 GROUP BY
       [GLHistory].[BranchID]
      ,[GLHistory].[LedgerID]
      ,[GLHistory].[AccountID]
      ,[GLHistory].[SubID]
      ,[FinPeriod].[FinPeriodID]
      ,[GLHistory].CompanyID 

I need to get the information from last financial period and any other entry that exists in my table until the last financial period for an account. Not sure what more, or how to structure the query in order to improve it.

Here is a script with the 2 tables schema and some data https://www.dropbox.com/s/swbqqzdey2tnkxz/script.sql?dl=0

And the full query can be found here: https://www.dropbox.com/s/scnav8flqpcrstd/SQLQueryGLB.sql?dl=0

Query plan presented here:queryplan

The output I am looking for is to get all the information for an account from the beginning period where I have an entry in GLHistory to the last period. And FinPeriodId might have 3 rows because its a row for each company

So it would be something like this, hope this time is right. You have an account for which there are values stored in GLHistory. For that account I need to get the information from GLHistory in a period, but also all other values until that period, so the values in GLHistory that are related to that account for each period until last activity period. That is why I have FinPeriodId >= GLHistory.FinPeriodId.

  • What is primary key / unique constraints exist on GLHistory? Does this fare any better? WITH T AS ( SELECT G.BranchID AS BranchID ,G.LedgerID AS LedgerID ,G.AccountID AS AccountID ,G.SubID AS SubID ,MAX(G.FinPeriodID) AS LastActivityPeriod FROM GLHistory GLHistory WHERE G.CompanyID = 7 AND ( G.BranchID IS NULL OR G.BranchID = 13) GROUP BY G.BranchID ,G.LedgerID ,G.AccountID ,G.SubID ,G.CompanyID ) SELECT T.*, F.FinPeriodID FROM T INNER JOIN FinPeriod F ON F.FinPeriodID >= T.FinPeriodID – Martin Smith Feb 19 '18 at 19:47
1

[FinPeriod] [FinPeriod] : 180 rows

[GLHistory] [GLHistory] :75334 rows after applying those filter at the end of index spool

At Nested loop :Estimated rows 23483000 which is 75334*180 rows.

Final output Estimated rows 2987930 which is way above 251114. This prove your query is wrong.you have to improve cardianility estimate.

In Findperiod table finperiodid is char(6) and in GLHistory table it is int. Which is one of reason for poor cardianility estimate.

Make FinPeriodID to INT in both the table SAFELY.In million of record it does matter.

Alter table [FinPeriod]
Alter column [FinPeriodID] int not null

Experiment,

Declare @i int=201706
Declare @j char(6)='201706'
select DATALENGTH(@i),DATALENGTH(@j)

In FinPeriod table change the order of Clustered Index like this CompanyID (ASC), FinPeriodID (ASC)

Now in GLHistory your choice of Clustered index is wrong.

  1. Firstly It will cause lot of index fragmentation.
  2. Secondly your index is not being use in sample record of 700.

So i hv change index in GLHistory table in this manner,

CREATE  CLUSTERED INDEX RIIX ON GLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX RIIIX ON GLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

I use your query without changing any logic in old index way .I got all index scan .

No need of any covering index.WHY ?Because CLUSTERED Index use in join is able to select those columns.

Now I drop the index and created index like above.

In your query you will notice I have only change the order of column in join so that it utilise the index. After Creating index don't forgot to update statistics.

UPDATE STATISTICS [GLHistory] ; 
GO
UPDATE STATISTICS [FinPeriod] ; 
GO

SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].[FinPtdDebit]
    ,[GLHistory].[CuryFinPtdCredit]
    ,[GLHistory].[CuryFinPtdDebit]
    ,[GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    ,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    ,[GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    ,[GLHistoryLast].[CuryFinPtdDebit]
    ,[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
    ,[GLHistoryLast].[YearClosed]
FROM (
    SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM [GLHistory] [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [GLHistory].CompanyID = 7
        AND ([GLHistory].BranchID = 13)
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN [GLHistory] [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN [GLHistory] [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

Result : I am getting all index Seek,minor Nested loop .Thats it.

Don't know how it will perform in million of records.

Alternative Solution,

Put [GLHistory] filtered records in temp table because you are joining same table 3 times.Add all require column in temp table.I have added few only.

    CREATE TABLE #tmpGLHistory (
    id INT identity(1, 1)
    ,BranchID INT
    ,LedgerID INT
    ,AccountID INT
    ,SubID INT
    ,FinPeriodID INT
    ,CompanyID INT
    ,CuryID NVARCHAR(10)
    ,FinPtdCredit DECIMAL(19, 4)
    ,CuryFinPtdCredit DECIMAL(19, 4)
    ,FinBegBalance DECIMAL(19, 4)
    ,CuryFinYtdBalance DECIMAL(19, 4)
    ,CuryFinBegBalance DECIMAL(19, 4)
    )

CREATE  CLUSTERED INDEX TIIX ON #tmpGLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX TIIIX ON #tmpGLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

INSERT INTO #tmpGLHistory
SELECT [BranchID]
    ,LedgerID
    ,AccountID
    ,SubID
    ,FinPeriodID
    ,CompanyID
    ,CuryID
    ,FinPtdCredit
    ,CuryFinPtdCredit
    ,FinBegBalance
    ,CuryFinYtdBalance
    ,CuryFinBegBalance
FROM [GLHistory] [GLHistory] WITH(NOLOCK)
WHERE [GLHistory].CompanyID = 7
    AND (
        [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13
        )




    SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].FinPtdCredit
    ,[GLHistory].[CuryFinPtdCredit]
    --,[GLHistory].[CuryFinPtdDebit]
    --, [GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    --,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    --, [GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    --, [GLHistoryLast].[CuryFinPtdDebit],
    --[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
--, [GLHistoryLast].[YearClosed]     
FROM (
   SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM #tmpGLHistory [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [FinPeriod].CompanyID = 7
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN #tmpGLHistory [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN #tmpGLHistory [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

--ORDER BY [GLHistoryByPeriod].AccountID asc oFFSET 0 ROWS FETCH NEXT 10000000 ROWS ONLY


DROP TABLE #tmpGLHistory

Introduce Filter inside Inner sub query

when you can write WHERE [GLHistoryByPeriod].[finPeriodID] = 201706 Outside inner query then why not introduce same filter inside inner subquery so that inner subquery already give you less resultset imprving the query dramatically.

Like , [FinPeriod].[FinPeriodID] = 201706

  • @KumarHarsh your first point was very good to move the filter for period in the subselect, I ended up also creating a temp table and that showed better result. I will look in your last answer to see if I can make this even better. I currently can't provide any logic that creates millions of rows on GLHistory. – DVM Feb 23 '18 at 10:07
  • @MischiuDanielValentin , i wanted to hear only any :improve thing" with existing query or temp table idea with change in index.Read my answer carefully.If there is any improvement then we come back "move the filter for period in the subselect".Even your own query is performaing ok with suggested change. – KumarHarsh Feb 23 '18 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.