Incredible performance increase with very large lists via “QuickView-Method”

Posted by Eric Bartels

Preface

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.

Heureka

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;

Result

Iteration Time1 (ms) Time2 (ms)
1 13,026.501 452.4174
2 12,995.299 390.0150
3 13,338.513 452.4174
4 15,538.197 390.0150
5 12,636.486 374.4144
Avg: 13,506.999 411.8558

Saving: ~97%

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!

15 Responses to “Incredible performance increase with very large lists via “QuickView-Method””

  1. SharePoint, SharePoint and stuff : SharePoint Kaffeetasse 38, on January 11th, 2008 at 3:09 pm Said:

    [...] Incredible performance increase with very large lists via “QuickView-Method” [...]

  2. chris, on March 14th, 2008 at 7:38 am Said:

    yeah
    did you try adding fields via the ‘viewfields’ property of the spquery object?

    Format is basic xml -

    to get it:
    SPQuery q = new SPQuery(list.Views["All Documents"]);
    console.writeLine(q.ViewFields)

  3. =8)-DX, on March 14th, 2008 at 2:17 pm Said:

    Also try indexing the Quotation column (List -> Settings -> Indexes), this should help for Where queries with the indexed column first. also it helps to have views which filter using an indexed column (must be first filter).

    Another thing which helps is using the SPWeb.ProcessBatchData method which allows ultra-fast inserts and updates (I inserted 60000 rows into a list this way with a similar 90% speed increase over SPList.Items.Add)..

  4. Eric Bartels, on March 14th, 2008 at 2:52 pm Said:

    @=8)-DX: Yes indexing helps a lot. But creating special views to retrieve only the items you need is the fastest way.

    ProcessBatchData is my very good friend too. Its the only way to handle such large amounts of ListItems!

    @chris: I will test if this helps even more

  5. Snap252, on April 18th, 2008 at 1:45 pm Said:

    Nice article,
    but I am a little bit :confused:
    For me time2 seems to be end2-end1 that is the block with Query and View. But in the table it seems that time2 is much slower than time1 that is without that view.
    But the statement was that with query AND view its much faster… am I wrong, or is there a mistake?

    Could someone of you please give an exemple of updating(!) (and maybe for creating new) specific items from a specific list with help of SPWeb.ProcessBatchData ? – I tried it but it did not work.

    Thanks in Advance

  6. Eric Bartels, on April 19th, 2008 at 3:31 pm Said:

    @Snap252
    time2 is the block “inside” start2 and end2. This “block” uses the GetItems-Method with the CAML-Query AND the given view. This causes the enormous performance improvement.

    For the “updating via SPWeb.ProcessBatchData” take a look at http://msdn2.microsoft.com/en-us/library/cc404818.aspx, which I found via Google ;-)

  7. Snap252, on April 21st, 2008 at 10:40 am Said:

    @Eric Bartels
    Okay, but Time2 seems to be much bigger than time1
    time2 = 411.9 (CAML AND view)
    time1 = 13.5 (CAML only)
    right?

    Thx 4 the link :)

  8. Eric Bartels, on April 24th, 2008 at 8:19 pm Said:

    @Snap252
    No, take care of the thousands separator (which is the comma here). Without separator it looks like this:
    time2 = 411ms (CAML AND view)
    time1 = 13506ms (CAML only)

  9. Snap252, on June 26th, 2008 at 7:39 am Said:

    @Eric Bartels
    Yes, it was the separator to confuse me.
    But now i have a performance issue too and i remembered your blog and your measurements. So I tried to build a View to improve performance by reducing the columns within that. But the result seems to be the same as when reducing the columns wihtin the Query (query.ViewFields)
    Do you aggree with that?

    Kind regards

  10. SharepointDev, on July 16th, 2008 at 5:47 pm Said:

    I tried this out and didn’t work at all. More, the perf worsened.

    Basically, my SPQuery.Query is like

    100031298

    and my SPQuery.ViewFields:

    so I created a view containing NumericID and Type. Didn’t help.

    What am I missing? Did I create the wrong View?

  11. SharepointDev, on July 16th, 2008 at 5:50 pm Said:

    (reposting with html codes :-) )

    I tried this out and didn’t work at all. More, the perf worsened.

    Basically, my SPQuery.Query is like

    <Where><Eq><FieldRef Name=’NumericId’/><Values><Value Type=’Integer’>100031298</Value></Values></Eq></Where>

    and my SPQuery.ViewFields:

    <FieldRef Name=’Type’/>

    so I created a view containing NumericID and Type. Didn’t help.

    What am I missing? Did I create the wrong View?

  12. Eric Bartels, on July 20th, 2008 at 8:16 pm Said:

    @SharePointDev
    Hard to tell what you did wrong. How is your View defined? How many list items will match that query? How many total items are inside the list?

    But the CAML is slightly wrong:

    <Where><Eq><FieldRef Name=’NumericId’/><Values><Value Type=’Integer’>100031298</Value></Values></Eq></Where>

    The Values-tag is wrong:

    <Where><Eq><FieldRef Name=’NumericId’/><Value Type=’Integer’>100031298</Value></Eq></Where>

  13. Arun, on August 13th, 2009 at 5:46 am Said:

    Hi,

    I would like to know how did you manage retrieve all items in the view?

    I used the same technique, i got incredible improvements but my SPQuery is returning only 100 items. Can you please help me?

    Thanks
    Arun

  14. Eric Bartels, on September 5th, 2009 at 11:01 am Said:

    Hi Arum,

    have you tried to set the RowLimit-property? Try setting it to a large value …

  15. Gene Vangampelaere, on March 21st, 2013 at 4:46 pm Said:

    Thank you so much for sharing this. We had a lot of problems with this and you blogpost solved this.

Leave a Reply

« »