I have been struggling with large lists for some time now, and the need to look at performance in queries where early an issue within a particular project of mine. It all started out with a small list of a couple of thousand items but soon grew to 60k and still growing. The work to fine tune the queries to this list have been going on over a couple of months since everything gradually slowed down. I will try and share some of the things I tried to implement during the developing phase. The queries now run 3 times faster than my previous release and this is due to a number of reasons.

The code

It’s a straight forward query to a list in Sharepoint which contain price information which again is used to validate invoices on external files.

        public class PrisDef
        {
            public string Place { get; set; }
            public string Contract { get; set; }
            public decimal MaxPris { get; set; }
            public decimal MinPris { get; set; }
            public decimal Pris { get; set; }
            public string SKU {get; set;}
            public string InUse { get; set; }
        }

        public static List<PrisDef> GetPris(string SKU)
        {
            List<PrisDef> priser = new List<PrisDef>();

            XmlDocument camlDocument = new XmlDocument();
            camlDocument.LoadXml(@"<Where>
                                <Eq>
                                    <FieldRef Name='SKU' />
                                    <Value Type='Integer'>[SKU]</Value>
                                </Eq>
                            </Where>".Replace("[SKU]", SKU));

            using (SPSite site = new SPSite("https://site"))
            {
                using (SPWeb web = site.OpenWeb())
                {

                   SPQuery query = new SPQuery();
                   query.Query = camlDocument.InnerXml;
                   query.RowLimit = 1000;
                   SPListItemCollection items = web.Lists["Price"].GetItems(query);

                   IEnumerable<PrisDef> sortedItems =
                   from item in items.OfType<SPListItem>()
                   select new PrisDef
                   {
                        Contract = arseString(item["Contract"]),
                        Place = parseString(item["Place"]),
                        Pris = parseDecimal(item["Pris"]),
                        SKU = parseString(item["SKU"]),
                        MaxPris = parseDecimal(item["MaxPris"]),
                        MinPris = parseDecimal(item["MinPris"]),
                        InUse = parseString(item["Use"])
                    };

                        priser.AddRange(sortedItems);

                        return priser;
                    }
                }
            }

                    

A couple of points to notice within the code.

Dispose both spsite and spweb, this is to avoid memory leaks, look at article:

                http://msdn.microsoft.com/en-us/library/aa973248.aspx

Set rowlimit, this is to avoid lock on a table article

                 An SPQuery object without a value for RowLimit will perform poorly and fail on large lists. Specify a RowLimit between 1 and 2000 and, if necessary, page through the list.

                  http://msdn.microsoft.com/en-us/library/bb687949.aspx

the more intresting part is to look at what not todo.:

never use Items, SPList.Items returns all the items in a list and performs badly especially on a large list. I found this especially usefull in tweaking an writing to a list. Notice that I start with a query which return 0 rows, this to avoid items which would have returned the whole list.

            using (SPSite site = new SPSite("https://site"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list = web.Lists["Log"];
                    SPQuery query = new SPQuery { RowLimit = 0 };
                    SPListItem listItem = list.GetItems(query).Add();

                    listItem.Web.AllowUnsafeUpdates = true;
                    listItem["Errorlog"] = feilMelding;

                    listItem.Update();
                    list.Update();
                    web.Update();
                }
            }

Iwould also like to refer to the list of alternatives for SPList.Items on msdn http://msdn.microsoft.com/en-us/library/bb687949.aspx

Alternatives to SPList.Items

Poor Performing Methods and Properties Better Performing Alternatives
SPList.Items.Count SPList.ItemCount
SPList.Items.XmlDataSchema Create an SPQuery object to retrieve only the items you want.
SPList.Items.NumberOfFields Create an SPQuery object (specifying the ViewFields) to retrieve only the items you want.
SPList.Items[System.Guid] SPList.GetItemByUniqueId(System.Guid)
SPList.Items[System.Int32] SPList.GetItemById(System.Int32)
SPList.Items.GetItemById(System.Int32) SPList.GetItemById(System.Int32)
SPList.Items.ReorderItems(System.Boolean[],System.Int32[],System.Int32) Perform a paged query by using SPQuery and reorder the items within each page.
SPFolder.Files.Count  

This is a start, I will try to cover a couple of other topics within performance on large Lists. This is my first blogpost enjoy.

Advertisement