What to do when List View throws Threshold Error, and same time you have not set indexing on columns.
What to do when List View throws Threshold Error, and same time you have not set indexing on columns.
Scenario
You have list which is having more than 300k records and threshold set is about 80K, now you tried to view the list, it will throw Thershold error. Now you are thinking to create views on that which will return list item count below Thershold 80K (Which We have set, default is 5000 though) but now you understand that , you have not crated indexes columns, now you try to create index columns it will again through Thershold error , "You cannot create Index, list is having threshold issue"
Now what to do, this is very tricky situation
Solution:
Brief Idea
1) Create different views, filter on ID (As ID column will have default indexing)
2) Create webpart page and add list view webpart and get the parameters from Query string.
3) Add Button called "Latest Items" and redirect to latest page with ID’s in query string.
Detailed
** **1) Create different views, filter on ID (As ID column will have default indexing)
Create view like below
1)0-80K Filter ID greater than 0 and less than 80K This view will show items, which Id fall in between 0 to 80K, here we will not get the threshold issue, because items written are less than threshold value (80K, in your case it may be 5000)
2)80-160K Filter ID greater than 80K and less than 160K
3)160-240K Filter ID greater than 160K and less than 240K
4)240-320K Filter ID greater than 240K and less than 320K
5)320-400K Filter ID greater than 320K and less than 400K
6)400-480K Filter ID greater than 400K and less than 480K
7)480-560K Filter ID greater than 480K and less than 560K
Now these view will show items, user can check any data. But what abput latest data , Suppose I want to show latest 80K or latest 5000 , above will show data based on ID’s range, but end user will not knowing ID, he may be searching his item in list expecting should come at top , for that we need second solution
2) Idea here is to show last 5000 or 10000 items.
Create webpart page and add list view webpart.Create Webpart page, and add List view webpart through UI Make sure you are adding Listview which should return atleast some items , it should not through threshold issue , because we want to make some changes through designer as well, So I crated view where ID=100 and set as default view. Now after adding the webpart, open the page in designer, and add Parameter like StartID and EndID and type as Querystring set any default value.
Now you have to filter ID based on parameter which we have set in previous steps like below
Order by ID desc, so this page will show ID range which we have passed from querysting
So now suppose you hit this page like
https://XXXXXXXXXX/sitePages/latest.aspx?StartID=393690&EndID=398690
This page will show items like below, now it is your hand how many items you want to show on this page , you can just adjust your query sting to show items .
However now suppose you want to show last 5000 only then how you will understand that which is last ID and -5000, this tricky right? For that read #3
3) Get the last ID from Rest service like below
https://XXXXXXXXX/sites/_vti_bin/ListData.svc/ListName?$top=1&$orderby=Id%20desc
this rest call will give you last Item which added in list , so you Jason object to get the ID
got to list All item page and edit that page and below HTML code for Button
Now on click on this Button we will call rest service to get Latest ID, and do some mat to get end ID which is less than 5000 and create HREF and redirect to page which we have created in step2
<Script>
var startID;
var EndID;
$('input:button[Title=Show Latest Ticket]').click(function(){ redirecttolatest()});
function redirecttolatest()
{
getListItem();
}
function getListItem() {
// Getting our list items
$.ajax({
url: "
https://XXXXXXXX/_vti_bin/ListData.svc/listName?$top=1&$orderby=Id%20desc",
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
// Returning the results
complete(data);
},
error: function (data) {
failure(data);
}
});
}
function complete(data)
{
startID= data.d[0].Id
EndID= startID- 5000;
window.location.href = "
https://XXXXXXXX/sitePages/latest.aspx?StartID="+EndID+"&EndID="+startID;
}</Script>
Little Explanation:
1) Below will add click event to button which will eventually call redirecttolatest() function
$('input:button[Title=Show Latest Ticket]').click(function(){ redirecttolatest()});
2) Function redirecttolatest() will call rest service to get the latest ID.
Replace URL with your site’s ID
URL: "
https://XXXXXXXX/_vti_bin/ListData.svc/listName?$top=1&$orderby=Id%20desc",
Function will do some Math and finally it will redirect to latest.aspx , with querystring in it
So that page will show latest Items.
Some Key pointns regarding Thershold.
1)By default Thershold is set to 5000
2)Thershold is nothing but maximum amount items can be writtren from the SQL and shown to SharePoint UI.
3)You can increase the Thershold to any no, however incresing thershold will hamper the Site permormance , It will take more time to retrieve more item from sql and show on UI, time will require for SQL to get itme , time will require SHarepoint C# code to process them and time will requite to show on UI through HTML, so becaus of this Thershold should not be increased .
4)5000 is magic no , whenever SQL wants to retrieve items count more than 5000 and then for better performace SQL Lock whole table , in our case whole LIST, because of this concurrent user's may not be able to update same list on same time.
5)Increasing thershold value , inversely proportianate to no of requets , means if you increse tehThershold you are decresing requests .
6)To retieve items faster you need to index columns , ID column is by defualt indexed.
7)Those columns you are usig for filter in VIew ,make sure those are indexed , otherwise you will face issues
8)You can set maximum 20 Indexed columns.