11

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!

Improve this question
6
  • 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, 2011 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, 2011 at 15:03
  • 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?)
    – JohnJ
    Jul 2, 2011 at 18:54
  • I'm not trying to return more than 5000 results. I am setting a value under 5000 in the RowLimit.
    – Efil4zaggin
    Jul 2, 2011 at 19: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, 2012 at 23:53

6 Answers 6

Reset to default
2

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?

Improve this answer
2
  • 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, 2011 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.
    – LFurness
    Jul 31, 2013 at 19:03
2

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.

Improve this answer
1

I know this is an old post but I found it while looking for help so someone else may.

I've done testing with SP 2013 and this is what you need to know...

Hitting 5,000 does not effect your ability to search..it only effects your ability to 'browse' using views.

Any view based on a list that have 5,000+ records, MUST have an INDEXED column as the FIRST field in the "Show items only when the following is true"

Even though you may have a view using an Indexed column as it's 1st filter, If that Filter returns more than 5,000 it will still crash. This is regardless of any "Show limits" or additional Field Filters you may place.

The only clear and solid solution (IMO) is that every Library has at least 1 index based on MODIFIED date. You then used the MODIFIED (Indexed) column as your Primary Filter field and set it's value to "Greater than or equal" [Today]-N .... where N represents the number of days it takes you to get more than 5,000 returns.

So say you have a list that adds 5k records in about 6 months, you would want to pad that a little and set you criteria to [Today]-120 (about 4 months).

If you think about it, users should not really be "browsing" items that contain more than 5,000 records. Views are just that..they are browsing. Instead they should search on keywords and then refine their results with search refiners or more keywords.

I mean seriously..would you 'browse' a My documents folder with over 5,000 files in it? Hopefully not.

Improve this answer
0

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.

Improve this answer
0

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).

Improve this answer
0

I know that this may be akin to the "have you tried turning it off, and then on again" approach, but just want make sure that the proper setup is in place.

When you have indexed the columns, did you also change the views to have the preferred indexed column as the first row of the SP table?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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