Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a custom list with more than 5000 items. This article: http://msdn.microsoft.com/en-us/library/ff798465.aspx (the last section, "How Does Indexing Affect Throttling?") states:

"For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation.

In this case, you could avoid the issue by indexing the Title field. This would enable SharePoint to determine the top 100 items sorted by title from the index without scanning all 10,000 list items in the database. The same concepts that apply to sort operations also apply to where clauses and join predicates in list queries. Careful use of column indexing can mitigate many large list performance issues and help you to avoid query throttling limits."

I have done exactly what this article says to do...I have indexed the Title field but I am not able to query against the Title field without hitting the throttle limit.

I examined the NameValuePair table in the content DB, and found that the values from the Title field are not there. I have reproduced the same results on another server as well.

Does anyone have an idea of what is going on here? I am not looking for alternative methods to access items from the list. I want to know how column indexes get created and why the example in the article is not working. Thanks!

share|improve this question
    
What is your query? Is it only using the Title field to select the items or is there another column that could be causing the scan of all the items? –  Laurie Jul 2 '11 at 4:57
    
I have stripped this down to the bare essentials. I've created a list with two fields...A text field and a number field. I have indexed the Title field along with the two fields mentioned above. I then loaded 5500 items into the list. Queries that filter or sort on the ID field return results. Queries against any of the other columns hit the throttle limit. Examining the NameValuePair table reveals that only the number column appears to be indexed. The other columns are not present there. –  Efil4zaggin Jul 2 '11 at 15:03
    
Has anyone figured out how to get the index columns to work or how long it takes to index? I'm running into the same thing where admins will only turn up the throttle limit for a few days and then will turn it back to the defaults. If i can make a case to say keep the limit up for x days while the site indexes, then we'll be ok?? i have 6500 items in a list is all. –  user6490 Jan 19 '12 at 23:53
    
This issue is quite old, but I couldn't find any solution for it over the Internet. Any chance that there is any update on the topic from MS side? –  Marek K Jun 21 '12 at 11:16

5 Answers 5

I have been experiencing the same issue. I believe it is because the index is not created. This is because the creation of the index itself violates the list view threshold. This is explained here:

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. http://technet.microsoft.com/en-us/library/cc262813.aspx#Throttling

In my case, the created index is displayed in the list settings, but I think it is blocked on the database level. It is too bad this isn't clearly reported while creating the index through the user interface.

A bit of speculation here, can anyone confirm?

share|improve this answer
    
I have also speculated that this might be what is happening. I have had a support case opened with Microsoft since early July and they are still "working" on the issue. Very disappointing. I will provide an update if I ever get a resolution. –  Efil4zaggin Jul 31 '11 at 10:37
    
The way I read the text you quoted is that if the throttle is set to 5,000 items, it will not be possible to create an index on a list that is over 5,000 items. This could be worked around by temporarily increasing the threshold. –  LCountee Jul 31 '13 at 19:03

You can disable the limit for the administrator, or for a certain time frame to create the index. It most likely is that the number of items created happened so quickly that by the time the indexer was ready to run, it was more than 5000 items so it couldn't run. In a normal case where under 5000 items are created in a day, the indexer will have no problem in picking those up. I noticed that if you try to create an index on an existing list of over 5000 items, it fails and lets you know it hit the throttle limit.

share|improve this answer

Is it possible that the number of results returned are more than 5000? I'm not sure what the behavior would be in that scenario. (what happens if the query of an indexed coulum returns more than 5000 results?)

share|improve this answer
    
I'm not trying to return more than 5000 results. I am setting a value under 5000 in the RowLimit. –  Efil4zaggin Jul 2 '11 at 19:03

We had a similar issue and ended up adapting the existing search facility to search through the data (we had 25,000 records) which worked perfect for us. The number of records do not cause a bottleneck in this way.

share|improve this answer

We experienced the same issue mentioned above, however, it was due to that Index being created on a Lookup column, not a standard text type of column (note: choice fields are treated like text).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.