If have more than 10 lac records in a table. Can any one tell how I can use custom paging in this scenario. Our motive to achieve records within 1-2 sec.

Client Side vs Server Side Pagination This is the time to compare both the scenarios and implementations. Client side pagination Consider when there is no possibility for server side pagination Requires State Management to reduce the DB calls. Means store the result sets in cache, sessions, viewstate and static objects. Memory consumption is very high Network speed and latency will be mattered On other hand we have advantages of Client Side Pagination is you can fetch all the results once and you perform operation on that data and need not to connect to DB again and again Everything will be available to your application so will be very useful to compare and match against the records with new ones Consider using only when the records sets are needed for application level Serve side pagination We can overcome the network issues Very fast compared to all the methods Requires minimum effort Very much important when you are working on very high traffic applications Can not implement cache or local storage for application Consider using when your application performance is very poor read less

Custom paging is suitable when working with large amount of data. It improves the performance by retrieving only required data need to be displayed in the current page requested by the user. You need to work with following variables: ? TotalRecords : The total number of records to be displayed in the UI. ? PageSize : Number of records in a page. ? CurrentPage : This is the page is being navigated. Initialize it with zero. ? StartFromIndex : Starting index of the first row displayed in the current page. It is calculated by CurrentPage * PageSize So the records are displayed in a page from StartFromIndex and limit to the PageSize. For example,take PageSize = 10 , TotalRecords = 100 if you are in page 2 then StartFromIndex = 1 * 10 and the index of the last record would be StartFromIndex + PageSize – 1 i.e 19. Now let us go step by step. 1. Get the total number of records being paged through. Following query can be used Selecte count(*) from table_name Add a method in the DAL and BAL that returns this information. Create a method TotalNumberOfRecords() that executes the above query. 2. Add a stored procedure for paging through the records to fetch the required records from database. It should accept two input parameters @ StartFromIndex and @ PageSize. SQL Server’s ROW_NUMBER() keyword can be used to fetch the required rows. It could be done easily if there is already a column in the database which serves as a row index. 3. Add a method in the DAL and BAL that returns this information. There are number of ways to implement the custom paging. Caching can be used also. It is very easy to implement custom paging if you are using AngularJs filter. Hope you have got some idea. read less