SQL pagination very slow with multiple Left Joins

Question

I have a stored procedure that I have to use pagination on in order to get records for a grid. The query works fine for a single table however once I start adding in left join things get slower and slower. A simple query to page 10 records takes 1 second with a single table but 3 minutes with 2 left joins. All tables have indexes on them. Is there a better more effective way to write this with the left joins? A sub query perhaps?

DECLARE 
    @Declare PersonNumber XML,
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH TempResult AS
(
    SELECT ID, Name
    FROM Table T
    LEFT JOIN Table A ON a.Id = T.Id
    LEFT JOIN Table B ON B.Id = A.Id 
                      AND B.Date = A.DATE 
                      AND B.IsActive = 1
), TempCount AS 
(
    SELECT COUNT(*) AS MaxRows 
    FROM TempResult
)
SELECT *
FROM TempResult, TempCount
ORDER BY TempResult.Name
    OFFSET (@PageNum - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

The columns I need are indexed.

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Oct 06 '19 at 18:31
I agree this was a query written by my predecessor. However if I remove the And in the joins the query is still slow. The left joins are the issue — Terrance Jackson, Oct 06 '19 at 19:17

score 0 · Answer 1 · edited Oct 06 '19 at 18:28

0

In order to be efficient, and OFFSET ... FETCH query needs to follow these rules:

The ORDER BY columns need to be unique, and have an index
No global row count. That's always expensive.
The query needs to find the target rows before performing lots of other logic like joins.

Without table and index DDL and actual query plans it's hard to be more precise.

edited Oct 06 '19 at 18:28

Shekar Kola

1,287
9
15

answered Oct 06 '19 at 14:08

David Browne - Microsoft

80,331
6
39
67

score 0 · Answer 2 · answered Oct 06 '19 at 19:59

Sometimes queries are too complex for the engine to be executed in the most optimal way. You can try to give the engine simpler queries and better tips in order to work smoothly.

So, in this case:

Simplify the query:

CREATE TABLE #TempResult
(
    [ID] INT
   ,[Name] NVARCHAR(128)
);

INSERT INTO #TempResult ([ID], Name])
SELECT ID, Name
FROM Table T
LEFT JOIN Table A ON a.Id = T.Id
LEFT JOIN Table B ON B.Id = A.Id 
                  AND B.Date = A.DATE 
                  AND B.IsActive = 1;

DECLARE @MaxRows INT;

SELECT @MaxRows = COUNT(*)
FROM #TempResult;

SELECT *
      ,@MaxRows AS [MaxRows]
FROM #TempResult
ORDER BY Name
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Then if the initial query with the LEFT JOIN is slow try to optimize it. For example, you can add check what indexes are used for extracting the data (if such exists) - you can add filter index in order to optimize the query.
If other columns are extracted and return - for example name, mail, address or other stuff, don't extract them. Get only the IDs and the column on which you are ordering. The perform the paging and with the result IDs get the other details.

SQL pagination very slow with multiple Left Joins

2 Answers2