MOSS developers most of the time need to retrieve data from SharePoint Lists. In one of the MOSS project I worked there were several provisioned .ASPX Pages which lacked in performance.
The pages took more than usual time to render, on average a page displaying 1000+ List records (we are not discussing Pagination here) was taking approximately 2 minutes! Coming from ASP.NET/SQL background this was just unacceptable.
The code was nicely structured with N-Tier architecture, all the MOSS specific code was residing in Data Access Layer (DAL) and .ASPX pages were consumers.
DAL consisted of function which was returning a DataTable with all the relevant data to be displayed on the .ASPX page.
The problem code was in this function, below is an excerpt of code from this function.
//load datatable
DataTable dt = objDB.GetAllData();
//this happens for 1000+ rows
foreach (DataRowView dr in pages.DefaultView)
{
....
....
....
DataRow viewRow = null;
//problem code
viewRow["MyRow"] = objDB.GetNameByID(dr["ID"].ToString())["Name"].ToString();
....
....
....
}
//gets Name by ID
public SPListItem GetNameById(string id)
{
int itemId = -1;
if (Int32.TryParse(id, out itemId))
{
using (SPSite site = new SPSite(SiteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists["ListData"];
return list.GetItemById(itemId);
}
}
}
return null;
}
Code highlighted with orange shows the problem area, if the outer loop is executing 1000+ times then this lookup function is called 1000+ times along with expensive SPSite, SPWeb and individual lookup which works satisfactorily if you have 100-200 rows but the performance starts to deteriorate exponentially with the increase in number of calls.
Fix:
After the reviewing the code, I recognized the need to avoid calling lookup function 1000+ times and instead have an inline cache to do lookup.
I am not talking about .NET cache here or MOSS 2007 object cache but DataView which can be used as a temporary cache.
//gets Name by ID revised to return DataView
public DataView GetAllNameById()
{
using (SPSite site = new SPSite(SiteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPList oList = web.Lists["ListData"];
SPQuery oQuery = new SPQuery();
oQuery.Query = "";
oQuery.RowLimit = 0;
DataView dv = new DataView();
dv = oList.GetItems(oQuery).GetDataTable().DefaultView;
//indexes, helps in faster lookup
dv.Sort = "ID";
//return sorted dataview
return dv;
}
}
}
Let us see the revised code excerpt once again:
//cache all data to avoid query MOSS on every request
DataView dv = objDB.GetAllNameById();
//load datatable
DataTable dt = objDB.GetAllData();
//this happens for 1000+ rows
foreach (DataRowView dr in pages.DefaultView)
{
....
....
....
DataRow viewRow = null;
//revised code
DataRowView[] drv = dv.FindRows(dr["Name"].ToString());
if (null != drv)
{
viewRow["MyRow"] = drv[0]["Name"].ToString();
}
....
....
....
}
With this change we are not using expensive SPSite, SPWeb objects and lookup on every iteration but caching the entire lookup List in an indexed DataView, when we are doing the lookups we are not querying a List but querying a DataView which is much Faster!
With this code change the Page rendering time changed from 2 minutes to 7 seconds!
