SPListItemCollection slow

Question

I have a list with over 150k records. I'm doing a filter to get only 1 record via CAML, only sort by a particular column, and this is it takes at least 15 seconds for me to have this return, which is a lot. Also because I want only 1 record

I made a simulation by native list, and left only one record and put the sorting by the same column also take long, because when I have many records, this happens in sharepoint? What can I do to improve this performance?

I also created index in my columns, but still did not solve the problem, here is a part of my code:

using (SPSite siteCollection = new SPSite(""))
{
    using (SPWeb web = siteCollection.OpenWeb())
    {
        SPListItemCollection items;
        SPQuery oQuery = new SPQuery();
        oQuery.ViewFields = "<FieldRef Name='ID' /><FieldRef Name='Title' /><FieldRef Name='Size' />";
        oQuery.ViewFieldsOnly = true;
        oQuery.RowLimit = 1;
        oQuery.Query = "<Eq><FieldRef Name='ParentItem' LookupId='TRUE' /><Value Type='Lookup'>1</Value></Eq>";
        items = oList.GetItems(oQuery);
    }
}

Note that I am using ViewFields, ViewFieldsOnly = true, and RowLimit = 1

Answer 1

To help improve the performance of a large list or library, you can index up to 20 columns. In general, an index on a column enables you to quickly find the rows you want based on the values in that column, even when working with millions of items. When you combine indexes with filtered views, you can quickly retrieve the items you want.

It is important to consider the following when you create and use indexed columns. Each additional column index consumes extra resources in the database and adds some overhead to every operation to maintain the index. Therefore, you should add indexes only to columns that will be used actively for filtering in views on the list or library. It's a good idea to look at all the views and see which columns are used most commonly across different views for filtering to help choose the right columns to be indexed. Note that any column that you define to have a unique value requires an index.

http://office.microsoft.com/en-gb/sharepoint-foundation-help/manage-lists-and-libraries-with-many-items-HA010377496.aspx#_Toc264017712

i know you have indexs some columns but what exactly have you indexed? is it only the one your querying? as noted above the more you index the more toll it takes on the sql server to be indexed within sql server!!

oQuery.QueryThrottleMode = SPQueryThrottleOption.Override;

the reason why iv set that is because its noted here! anything over 5000 items are throttled:

By default, the list view threshold prevents operations that will involve more than 5,000 items, such as queries that will return more than 5,000 items or adding a column to a list that contains more than 5,000 items. Although this is a configurable default, we strongly recommend that you keep it. If poorly performing queries are used on lists with more than 5,000 items, overall throughput can significantly decrease when you increase this limit.

Some operations, such as non-indexed queries or adding a column to a list, take time and resources that are proportional to the number of items in the list. On a small list this does not matter because there are so few items that the operation is fast. As the list size increases, these operations take longer and use more resources. Rather than let these operations run unbounded, the list view threshold blocks them. You can think of the list view threshold as a guard rail along a highway letting you know that you should change the query and how data is accessed, or you should perform the operation when farm usage is low.

The list view threshold is the maximum number of list or library items that a database operation, such as a query, can involve at one time. By default, this is set to 5,000 items. This limit has a major effect on large lists because, by the definition of this threshold, a large list is a list that has more items than this limit. Operations that exceed this limit are throttled. Operations, such as creating an index on a list that is over this limit, are prevented because the operation affects more than 5,000 items. This limit prevents the queries that have a selectivity (items that can be efficiently filtered by using filter criteria) of more than 5,000 items. This limit also prevents queries that filter on columns that are not indexed. This is because a query that filters (and in some cases sorts) on a column that is not indexed must perform the filter on all items in the list to retrieve the correct dataset, and it will operate on more items than the list view threshold. The default value for this limit is based on farm and list performance and on how SQL Server manages locks. We recommend that this limit not be changed.

http://technet.microsoft.com/en-us/library/cc262813.aspx#Throttling

EDIT

there is a good article on indexing ill post at the bottom! as for you the issue lies with the ammount of data within the list (columns and rows). Say you index 1 column for 170k items within a given list.... in sql server thats 170k rows aswell as 170 items stored. Thats a lot ;). As for your query its correct, the fact that you tried to optimize it aswell by using ViewFields is also a bonus returning id, name and size but its still a major issue with the ammount of items being quieried from the sql database.

when you query its still querying the whole 170k records regardless if you index or not.

so you have two options! 1 optimize your query even further by adding value type to each fieldref:

<Value Type="Text"></Value>

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

http://apmblog.compuware.com/2009/01/28/sharepoint-list-performance-how-list-column-indices-really-work-under-the-hood/

or directly call the table in sql as that is the fastest option available using a stored procedure ;), should be able to do it under a couple of seconds....reason being your skipping the middle man and going directly to the source! have a stored procedure taking in a lookup ID and returning the three values :)

you should follow this desgin when using massive lists:

http://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx

as a side note when microsoft state building list within 1million thats becasue its within folder or groups rather than put in one list. for each list its threshold is 5k but you could have 5k list of folders with 5k within each one ;). Having them within folders or groups makes search queries faster and lets not forgetting loading the page.