One of my SharePoint projects contains a (still growing) list with currently more than 230,000 items. These items represent positions of a quotation. So one “quotation-item” references multiple “position-items” (1:n relation).
In order to have a user-friendly user interface I developed a custom webpart which allows a quick way of editing the positions of a selected quotation.
How I got the items …
To get all related items of the selected quotation CAML comes in. The very large list (referred to as “myList”) is queried to match the given “quotation-item” (lookup-field).
SPList myList = web.Lists["myList"]; SPQuery query = new SPQuery (); query.query = "<Where><Eq><FieldRef Name='Quotation' />"; query.query += "<Value Type='Number'>1</Value></Eq></Where>"; // Query the list to get all items of the selected quotation SPListItemCollection items = myList.GetItems (query); int total = items.Count;
This takes some time. Too much time … I tried all possible SPQuery properties to get more performance. Nothing really worked.
I created a new view (list settings -> Create a new view) for the “position-list” which contains only the columns needed in my custom webpart. This view is named MyQuickView. After that I updated my webpart-code and passed this view as second parameter in the GetItems-Method.
SPList myList = web.Lists["myList"]; SPQuery query = new SPQuery (); query.query = "<Where><Eq><FieldRef Name='Quotation' />"; query.query += "<Value Type='Number'>1</Value></Eq></Where>"; // Query the list to get all items of the selected quotation // but now pass the newly created view as second paramter SPListItemCollection items = myList.GetItems (query, myList.Views["MyQuickView"].ID.ToString ("B").ToUpper ()); int total = items.Count;
I refreshed my browser and … Wow, this was extremly fast! To prove my feeling I build some “DateTime-Code” to measure the increase.
// ... web was declared before, query is the SPQuery instance from above // Measure the first method DateTime start1 = DateTime.Now; SPList listPositions = web.Lists["myList"]; SPListItemCollection positions = listPositions.GetItems (query); int testCount = positions.Count; DateTime end1 = DateTime.Now; // Measure the second method DateTime start2 = DateTime.Now; SPList listPositions2 = web.Lists["myList"]; SPListItemCollection positions2 = listPositions2.GetItems (query, listPositions2.Views["MyQuickView"].ID.ToString ("B").ToUpper ()); int testCount2 = positions2.Count; DateTime end2 = DateTime.Now; // Check the runtime double time2 = ((TimeSpan)(end2 - start2)).TotalMilliseconds; double time1 = ((TimeSpan)(end1 - start1)).TotalMilliseconds;
|Iteration||Time1 (ms)||Time2 (ms)|
I save about 97% of time (and resources) when creating a custom view which contains the required columns and pass this to GetItems. I call this just incredible! Again: Querying the list with more than 230,000 items is now ~97% faster!
I call this method the “QuickView-Method“. Note that the view you refer to has to contain the required items. So one view is not enough!
- January 10th