SQL pagination very slow with multiple Left Joins

  • Thread starter Terrance Jackson
  • Start date
T

Terrance Jackson

Guest
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.

Continue reading...
 
Top