Question

KV_P on Fri, 19 Feb 2016 04:54:31


Hi

I have updated query several times to match with the List View Threshold limit.

Used CAML Query to update the existing query including some where condition parameters. This List has 12000 records. This will grow in the future. I have set up limit to 8000 and updated query.

Query works perfect in Test Server including different permission access but when I deployed to production server same query gets an error 'Threshold query limit exceeds...'. It doesn't even work for the Site Administrator.

Currently I have only option to increase limit as users can't access the site.

I looked for the batch processing query but didn't work.

Is there any alternate solution to solve this?

Thanks

KP


Sponsored



Replies

AbdulAzizFarooqi on Fri, 19 Feb 2016 05:00:52


First Index your query column

Check threshold limit in your prod env

You can first take the count of list. divide then to 4000 count and then fetch data in loop from list.

i.e. you will have 3 count in loop and for each iteration it wil being 4000 record and append the array or list.

user should be site administrator to query the list larger than threshold otherwise will through threshold error.

KV_P on Fri, 19 Feb 2016 10:19:30


I have applied similar query still doesn't work on prod server. 

No Idea what's wrong. Same db restored on Test and deployed exact same code all works good.

Any suggestion?

KV_P on Fri, 19 Feb 2016 10:53:52


Found When I count items for that List I get threshold error.

Applied different options as below. doesn't work.

int totalcount = objList.ItemCount;//Doesn't work
int totalcount = objList.GetItems(new SPQuery { ViewFields = "ID" }).Count;//Doesn't work

Any other way to get count items. If I manually give that number All works.

AbdulAzizFarooqi on Sat, 20 Feb 2016 15:03:32


You can use rest api, if you want to get count.

KV_P on Sun, 21 Feb 2016 08:47:13


Thanks for the suggestion 

How can I use REST API in .Net Web application C# Code.

Could you give me some idea or any reference of blog.

Rizwan.Ansari on Sun, 21 Feb 2016 12:06:12


Here are few suggestions while working with ListView Threshold limit - http://www.abelsolutions.com/2013-blog/working-with-list-view-thresholds-in-sharepoint/

RajendraPratap Singh on Mon, 22 Feb 2016 07:47:28


Hi

did you try using SPQueryThrottleOption.Override in caml query?

you can also disable throttling at list level using powershell.

you need to set the EnableThrottling property for the list.

$web = Get-SPWeb http://url/to/web/with/list
$list = $web.Lists[“BIG_LIST_NAME”]
$list.EnableThrottling = $true


https://samlman.wordpress.com/2015/02/28/working-with-large-lists-in-sharepoint-2010-list-throttling/

http://blogs.msdn.com/b/dinaayoub/archive/2010/04/27/programmatically-using-the-object-model-override-for-the-list-view-threshold.aspx

http://adicodes.com/sharepoint-2010-list-throtelling/


KV_P on Tue, 01 Mar 2016 01:16:22


Hi 

I have read to disabled EnableThrottling is not good on the list. Also I think Sharepoint 2013 doesn't allow to do that.

I have large list and index on Status Only.

I am trying to get all records by Status. Its doesn't even allow by batch like 500 and then...

Not sure what are the other option to deal with this in Sahrepoint

KV_P on Sun, 06 Mar 2016 02:20:14


Hi I have tried pulling data using REST API.

Still the same problem.

Any suggestion what could be an issue.

Sachin Choube on Sun, 06 Mar 2016 12:53:33


SharePoint List Item limit is set on permission as well. May be your are admin so it work on dev however it will not work.

I suggest increase the limit to 20000 twenty thousand which is ok for near future.

KV_P on Sun, 06 Mar 2016 23:02:08


Thanks for the suggestion but this is not the solution.

Eventually it will stop. I have tested this using other user account. It does work in Dev but not in Production.

Any idea?

AbdulAzizFarooqi on Wed, 09 Mar 2016 14:25:26


Check the below JS code snippt to call data from sharePoint list in loop.

This below code will give u a final array from you list ,column which you select in rest url. Maincounter is 5, mean it will give u array of 50 thousand records. but fetching to much records is wastage or resource and what user will do :S. Each time JS will send query to rest api to give me 10000 records.

var TempARray = [];
var Listdatas = []
var StartCounter = 0;
var EndCounter = 10000;
//this count will to call methods recursively
var MainCounter = 5;
var LooCounter = 0;
//Call this methods first
function GetdataFirstCallme() {
    var serviceURL = "";
    for (var i = 0; i < 1; i++) {
        serviceURL = serverUrl + "_api/lists/getbytitle('ListName')/items?$top=10000&$select=ID,column1,Created &$filter=(ID ge " + StartCounter + ")and(ID le " + EndCounter + ")";
        getlistdata(serviceURL);
    }
}
///this method will call it recusively time
function getlistdata(URL) {
    $.ajax({
        url: URL,
        method: "GET",
        headers: { "Accept": "application/json;odata=verbose" },

        cache: false
    })
 .done(function (html) {
     var Temp = html.d.results;
     if (Temp != '' || Temp != undefined) {
         TempARray.push(Temp);
         var flatArray = [].concat.apply([], TempARray); //this line will merge all coming array to Main Arraylist
         Listdatas = flatArray;
     }
     for (x = LooCounter; x < MainCounter; x++) {
         //this will add 10000 each time to get from sharpeoint
              //thus this will overcome the threshold issue
         StartCounter += 10000;
         EndCounter += 10000;
         serviceURL = serverUrl + "_api/lists/getbytitle('ListName')/items?$top=10000&$select=ID,Column1,Created &$filter=(ID ge " + StartCounter + ")and(ID le " + EndCounter + ")";
         getlistdata(serviceURL);
     }
 });
}