asp.net - Performance Issues loading large data set into c# GridView -

ok,

been testing relatively small data sets gridview, , has worked fine. however, i've moved proper uat , have tried load 17,000 records grid, has brought web app grinding halt.

basically, user logs in, , upon validation data grids loaded, 1 of contains 17k records. until loads end user left handing on logon page. need fix it.

the code grids is:

datatable dtvaluedatecurrency = null;                sqlconnection conn = new sqlconnection(webconfigurationmanager.connectionstrings["reporting"].connectionstring); using (conn) {     conn.open();     //load other grid data     using (sqldataadapter sqladapter = new sqldataadapter(tsql1, conn))     {         dtvaluedatesummary = new datatable();         sqladapter.fill(dtvaluedatesummary);         grdvaluedatesummary.datasource = dtvaluedatesummary;         grdvaluedatesummary.databind();     }  } 

is there way increase load times? pagination isn't option, i'm taking care of jquery.

loading 17,000 records in 1 query what's killing you. highly suggest paging gridview.

first need alter stored procedure follows.

alter procedure [dbo].[sometable_getpagedresults]  (          @startrowindex      int,          @maximumrows        int  )    set nocount on   select      rownum,      [id],      [foo],     [bar]      (select          [id],          [foo],          [bar],          row_number() over(order [id] desc) rownum          dbo.[sometable] t)  derivedtablename  rownum between @startrowindex , (@startrowindex + @maximumrows)  

now have pageable query.

you want query complete row count.

alter procedure [dbo].[sometable_getrowcount]    set nocount on   return (select count(id) totalrecords sometable)  

you'll bind grid every time change page.

protected void gridview1_pageindexchanging(object sender, gridviewpageeventargs e) {   gridview1.pageindex = e.newpageindex;   bindgrid(); // whatever method call bind data , execute stored procedure. } 

and bindgrid() method call 2 stored procedures (one complete row count, , 1 results pertaining current page)

additional reading