OFFSET/FETCH Pagination:-
With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Storedprocedure that was at least twice as fast in return times as the other average ones found on the internet.
As an Example –
--[dbo].[usp_GetEmployeeCodes] 'INC', 3, 15, 'employee_code', 'ASC'
When to use Option (recompile)?
Use WITH RECOMPILE or OPTION (RECOMPILE)
Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans.
OPTION (RECOMPILE) is used in the queries with parameters to prevent parameter sniffing issue.
Impact of option (recompile) when joins involved: -
The estimated rows won’t be accounted for the table join. Instead, the recompile will only provide back the count of the rows in the table variable, without accounting for the possible 1 to many. This is important to remember as option (recompile) added to a table variable with a 1-many won’t fix the correct statistics estimation issue.
Disadvantages of Option (recompile) -
With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Storedprocedure that was at least twice as fast in return times as the other average ones found on the internet.
As an Example –
--[dbo].[usp_GetEmployeeCodes] 'INC', 3, 15, 'employee_code', 'ASC'
--[dbo].[usp_GetEmployeeCodes]
'', 1, 15, 'employee_code', 'ASC'
ALTER PROC [dbo].[usp_GetEmployeeCodes]
(
@SearchValue NVARCHAR(50) = NULL,
@PageNo INT = 1,
@PageSize INT = 10,
@SortColumn NVARCHAR(20) = 'employee_code',
@SortOrder NVARCHAR(20) = 'ASC'
)
AS BEGIN
SET NOCOUNT ON;
SET
@SearchValue = LTRIM(RTRIM(@SearchValue))
; WITH CTE_Results AS
(
SELECT
employee_code
,employee_comp_code
,employee_group_desc
,employee_name
,employee_addr1
,employee_addr2
,employee_city
,employee_state
,employee_zip
,Country
,employee_phone
FROM [dbo].[employee_codes]
WHERE (@SearchValue IS NULL OR employee_name
LIKE '%' + @SearchValue + '%')
ORDER BY
CASE WHEN (@SortColumn = 'employee_code' AND
@SortOrder='ASC')
THEN
employee_code
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_code'
AND @SortOrder='DESC')
THEN
employee_code
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_comp_code'
AND @SortOrder='ASC')
THEN
employee_comp_code
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_comp_code'
AND @SortOrder='DESC')
THEN employee_comp_code
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_group_desc'
AND @SortOrder='ASC')
THEN
employee_group_desc
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_group_desc'
AND @SortOrder='DESC')
THEN employee_group_desc
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_name'
AND @SortOrder='ASC')
THEN
employee_name
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_name'
AND @SortOrder='DESC')
THEN employee_name
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_addr1'
AND @SortOrder='ASC')
THEN
employee_addr1
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_addr1'
AND @SortOrder='DESC')
THEN employee_addr1
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_addr2'
AND @SortOrder='ASC')
THEN
employee_addr2
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_addr2'
AND @SortOrder='DESC')
THEN employee_addr2
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_city'
AND @SortOrder='ASC')
THEN
employee_city
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_city'
AND @SortOrder='DESC')
THEN employee_city
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_state'
AND @SortOrder='ASC')
THEN
employee_state
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_state'
AND @SortOrder='DESC')
THEN employee_state
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_zip'
AND @SortOrder='ASC')
THEN
employee_zip
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_zip'
AND @SortOrder='DESC')
THEN employee_zip
END
DESC,
CASE
WHEN (@SortColumn
= 'employee_phone'
AND @SortOrder='ASC')
THEN
employee_phone
END
ASC,
CASE
WHEN (@SortColumn
= 'employee_phone'
AND @SortOrder='DESC')
THEN employee_phone
END
DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS
ONLY
),
CTE_TotalRows AS
(
SELECT count(employee_code) as MaxRows
FROM [dbo].[employee_codes]
WHERE (@SearchValue IS NULL OR employee_name
LIKE '%' + @SearchValue + '%')
)
SELECT
MaxRows
,t.employee_code
,t.employee_comp_code
,t.employee_group_desc
,t.employee_name
,t.employee_addr1
,t.employee_addr2
,t.employee_city
,t.employee_state
,t.employee_zip
,t.Country
,t.employee_phone
FROM [dbo].[employee_codes] as
t, CTE_TotalRows
WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE
CTE_Results.employee_code = t.employee_code)
OPTION (RECOMPILE)
END
When to use Option (recompile)?
1.
Unpredictable amount of results returned
2.
Optimized query plan
Use WITH RECOMPILE or OPTION (RECOMPILE)
Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans.
OPTION (RECOMPILE) is used in the queries with parameters to prevent parameter sniffing issue.
Impact of option (recompile) when joins involved: -
The estimated rows won’t be accounted for the table join. Instead, the recompile will only provide back the count of the rows in the table variable, without accounting for the possible 1 to many. This is important to remember as option (recompile) added to a table variable with a 1-many won’t fix the correct statistics estimation issue.
Disadvantages of Option (recompile) -
1.
Lose DM Exec query stats.
2.
Can have tremendous impact on CPU
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|