In the system I'm using the below query in order to get a portion of my table's data in order to implement pagination. The table contains around 100 records only right now but it will grow into 1 million+ records later on.
SELECT
Id AS ActualMapping_Id,
BudgetPhase AS ActualMapping_BudgetPhase,
FromBH AS ActualMapping_FromBH,
ToBH AS ActualMapping_ToBH,
FromBI AS ActualMapping_FromBI,
ToBI AS ActualMapping_ToBI,
FromSI1 AS ActualMapping_FromSI1,
ToSI1 AS ActualMapping_ToSI2,
FromSI2 AS ActualMapping_FromSI2,
ToSI2 AS ActualMapping_ToSI2,
DataType AS ActualMapping_DataType,
Status AS ActualMapping_Status,
MappingType AS ActualMapping_MappingType,
LastMappedBy AS ActualMapping_LastMappedBy,
LastMappedDate AS ActualMapping_LastMappedDate
FROM
(
SELECT
Id,
BudgetPhase,
FromBH,
ToBH,
FromBI,
ToBI,
FromSI1,
ToSI1,
FromSI2,
ToSI2,
DataType,
Status,
MappingType,
LastMappedBy,
LastMappedDate,
ROW_NUMBER() OVER (ORDER BY FromBH) AS RowNumber
FROM
ActualMapping
WHERE
DataType = 'Cost' and
Status = 'Active' and
MappingType = 'Static' and
BudgetPhase like '%some_text%' and
ToBH like '%some_text%' and
ToBI like '%some_text%' and
ToSI1 like '%some_text%' and
ToSI2 like '%some_text%' and
FromBH like '%some_text%' and
FROMBI like '%some_text%' and
FROMSI1 like '%some_text%' and
FROMSI2 like '%$some_text%'
) AS NumberedTable
WHERE
RowNumber BETWEEN 1 AND 50
The above query which works pretty fine (At least on ~100 records) but after a while it starts getting blocked. I can't understand the possible reason for it getting blocked but when that happens I won't be able to do a simple select query from the table unless I kill all those blocked queries which are piled up.
So my questions are:
- Why does the query gets blocked after a while?
- Is this a good/ok approach to implement pagination on sql side for a large data set? (possibly more than 1 million+ records)
