Load more than 90000 records data to gridview at form_load

Ye Htut

how to handle to load more than 90000 records data to gridview. I used datatable as datasource. now It takes almost 2 mins

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
DataSource do not take much time to bind it with gridview, it depends how you fetch the data. You can use Store Procedure to reduce the traffic or you can use pagination function so that data can be fetched 10 records (for eg) at a time.
sudipta sanyal9y ago
For this you need to use CTE query for better performance, why you load full table data at one time, load data page by page.
DECLARE
@Pagesort VARCHAR(20) = '',
@Pageorder VARCHAR(10) = 'ASC',
@PageNumber INT = 1,
@PageSize INT = 50
SELEct @PageNumber = 1
IF (@PageNumber <= 0)
SET @PageNumber = 1
CREATE TABLE #temp_table (ID INT IDENTITY(1,1), text NVARCHAR(200))
;WITH n(n) AS
(
SELECT 0
UNION ALL
SELECT n+1
FROM n WHERE n < 999
)
INSERT INTO #temp_table (text)
SELECT
[text] = 'text_'+CONVERT(VARCHAR(10),n)
FROM n ORDER BY n
OPTION (MAXRECURSION 1000);
--SELECT * FROM #temp_table
--****************************************
;
WITH Results_CTE
AS (SELECT
ID,
text
FROM #temp_table
)
SELECT
ROW_NUMBER() OVER (ORDER BY ID ) AS rownum,
COUNT(*) OVER () AS rowcnt,
*
FROM Results_CTE
ORDER BY rownum
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
DROP TABLE #temp_table
Ye Htut9y ago
I am developing the windows application.
ali tuncer9y ago
if you just need to display 90000 rows of data all at once, try binding to a repeater, it will be faster than gridview..
Ye Htut9y ago
Although the user doesn't use all data, I have to display all. in my code, datatable is used to bind data to grdview.
Nitin Sontakke9y ago
Can you please elaborate on what exactly you are going to do with all those records filled in GridView?

No user is going to ever look at all these records, which is the sole purpose of such a control.