Gridview taking much time to load

Question

I am having a Gridview which takes much time to load its data, I tried to make the loading faster by implementing paging in gridview and reducing the rows to render in the grid and it got worked to an extent but still it lags in processing to bind data. Please help to overcome this problem, and i would appreciate if you can give me tips that i should keep in mind while playing with Grid. The database i am using is SQL Server 2005 and the code is as follows

DataTable dtProducts = objProducts.GetProductDetails(iCat.ToString(), userId); dgrdProductList.DataSource = dtProducts; dgrdProductList.DataBind();

public DataTable GetProductDetails(String CatID, String UserID) { DataAccess DAB = new DataAccess(); DataTable dtbProducts = new DataTable(); try { string SQL = "select *,CAST(ShortDescription AS varchar(200)) AS ShortDescriptionList from vw_products where IsActive=1 AND CategoryID = " + CatID + " AND LanguageID in( SELECT PW_UserLangRel.LanguageID FROM PW_Language INNER JOIN " + " PW_UserLangRel ON PW_Language.ID = PW_UserLangRel.LanguageID WHERE PW_Language.IsActive=1 AND PW_UserLangRel.CompanyID=" + UserID + " ) Order By ID Desc,LanguageID"; dtbProducts = DAB.Fill(SQL, CommandType.Text).Tables[0]; } catch (Exception ex) { throw new Exception(ex.Message + "WebShopPaolo.CProducts.AddProductImages"); }

        return dtbProducts;

}

Answer 1

From my experience poor performance with this control is normally because paging is occurring in memory as opposed to at the database. With large data sets this kills performance.

The SQL you have posted suggests this is most likely the issue.

I think you need to read this http://www.nerdymusings.com/LPMArticle.asp?ID=23 (Improve GridView Performance by Delegating Paging to SQL Server)