On web 1000 of records are in Database.We need only small amount
of data i.e 10,15 etc. default paging all records are load and
it take more time. Performance of our Application decreasing.
we create custom Optimize Paging.
In this paging we retrieve only our page size data only from database
for that we create store procedure.
Customize or Manual Paging Store procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,ASPdotNet-Example.blogspot.com>
-- Create date: <Create Date,,May 20 2011>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_Paging]
@PageIndex int,
@PageSize int,
@TotalRecord int output,
AS
BEGIN
SET NOCOUNT ON;
select @TotalRecord=count(id) from tblemp
select * from
(
select row_number() over (order by [id] asc )as RowNumber,
Name,depid,Salary from tblemp
)as a
where a.rownumber between (@PageIndex-1)*@PageSize+1 AND
(((@PageIndex-1)*@PageSize+1)+@PageSize)-1
END
