How to handle large amount of data with gridview in asp.net

Question

Hello,

 

I need to display and update large amount of data with Gridview and MS SQL server 2008.

Can any one help me to do the same.

Getting very slow response from database while fetching records.

 

 

ALTER proc [dbo].[getData_2]
(
@startDate datetime,
@endDate datetime,
@id int,
@searchLetter nvarchar(10),
@searchNumber nvarchar(10),
@firstName nvarchar(100),
@email nvarchar(100),
@mobie nvarchar(50),
@city nvarchar(100),
@state nvarchar(100),
@status nvarchar(1),
@center nvarchar(4000),
@course int,
@assignTo int,
@unAssigned int,
@isFollowed int,
@isByCrm int,
@isconverted int,
@userId nvarchar(100),
@label int,
@PageIndex INT = 1
,@PageSize INT = 50
,@RecordCount INT OUTPUT
)
as
if(@id = 0)
begin
--DECLARE @index int;
--SET @index = @PageSize*(@PageIndex - 1);
SELECT ROW_NUMBER() OVER( ORDER BY M.ID desc) AS RowNumber, M.*
INTO #Results2
from dms_data M where 1 = 1 and M.STATUS = @status
and (CAST(M.C_DATE as date) between CAST(@startDate as date) and CAST(@endDate as date) )
and ((M.NAME like '%'+@firstName+'%' or @firstName = ''))
and (M.EMAIL like '%'+@email+'%' or @email = '')
and (M.MOBILE like '%'+@mobie+'%' or @mobie = '')
and (M.CITY like '%'+@city+'%' or @city = '')
and (M.STATE like '%'+@state+'%' or @state = '')
and (M.COURSE = @course or @course = 0)
and (M.CENTER in (select items from dbo.Split(@center,',')) or @center = '0')
and (M.ASSIGNED_TO = @assignTo or @assignTo = -1)
and (M.ASSIGNED_TO != @unAssigned or @unAssigned = -1)
and (M.IS_FOLLOW = @isFollowed or @isFollowed = -1)
and (M.IS_BY_CRM = @isByCrm or @isByCrm = -1)
and (M.IS_CONVERTED = @isconverted or @isconverted = -1)
and (M.ID = @id or @id = 0)
and (M.LABEL = @label or @label = 0)
and (M.NAME like ''+@searchLetter+'%' or @searchLetter = '')
and (((M.NAME like '%0') or (M.NAME like '%1') or (M.NAME like '%2') or (M.NAME like '%3') or
(M.NAME like '%4') or (M.NAME like '%5') or (M.NAME like '%6') or (M.NAME like '%7') or
(M.NAME like '%8') or (M.NAME like '%9')) or @searchNumber = '')

order by M.C_DATE desc;

SELECT @RecordCount = COUNT(*) FROM #Results2

SELECT * FROM #Results2
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
order by #Results2.RowNumber asc

DROP TABLE #Results2;
end