When to use custom paging logic in SQL Server?
When you fetch the records from the stored procedure and display in the grid format on the front-end, prefer to have custom paging logic when the underlying results set contains many records. I hope, this helps in resolving the performance issues associated with the stored procedures.
How to improve the performance of stored procedures?
Based on the experience and the recent queries on improving the performance of the stored procedures, I thought to compile all the guidelines at one place, which pushed me to write this post. The guidelines given below will help to improve the performance of the stored procedures.
Is there a way to do pagination in SQL?
I’m adding an answer since so many of the other answers suggest dynamic SQL, which is not a best practice. You can add pagination using an OFFSET-FETCH clause, which provides you with an option to fetch only a window or page of results from a result set.
Do you use the prefix SP _ in stored procedure name?
Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database.
How is custom paging used in Microsoft Docs?
Given the Start Row Index and Maximum Rows variables, a custom paging implementation must only return the precise subset of records starting at the Start Row Index and up to Maximum Rows number of records after that. Custom paging provides two challenges:
How to efficiently paging through large amounts of data?
Efficiently Paging Through Large Amounts of Data (C#) The default paging option of a data presentation control is unsuitable when working with large amounts of data, as its underlying data source control retrieves all records, even though only a subset of data is displayed. In such circumstances, we must turn to custom paging.
How to optimize stored procedures in SQL Server?
Prefer table joins over the use of subqueries in the where conditions. Transfer the relevant records from the primary tables to the table variable and do further processing with the table variable, thereby disconnecting the primary tables reference. Try to avoid using NOT LIKE operator. Create Non-clustered indexes, wherever it is necessary.