CSOM CAML query with ID row limit 1 returning list threshold error

Question

I'm using the following CAML query in my CSOM code to try to determine the highest list ID in a list. I'm getting the standard list threshold limit error (because the list contains over 23000 items).

<View Scope="RecursiveAll">
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="False" />
    </OrderBy>
  </Query>
  <RowLimit>1</RowLimit>
</View>

My understanding from the other question is that the ID field is already indexed by SharePoint automatically.

The query works during the Happy Hour that we setup for our farm.

Can anybody help me figure out why this isn't working for non-Happy Hour queries?

Update based on comments: This is on-premise SharePoint 2013. Happy Hour has the threshold set to unlimited. During the rest of the time we have the default 5000 item limit.

Furthermore, in the same webapp, there is another site collection that has a list with 27000+ items. The same CAML query above works irregardless of Happy Hour.\

Edit 2: I also tried this CAML without success during non-Happy Hour times:

<View Scope="RecursiveAll">
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

On a whim, I also tried:

<View>
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

and

<View>
  <Query>
    <Where>
      <Gt>
         <FieldRef Name="ID" />
         <Value Type="Integer">0</Value>
      </Gt>
    </Where>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

All work during Happy Hour, but not during non-Happy Hour.

Here are the "columns" in the list that is failing:

Title                Single line of text    
Market               Choice 
Vendor               Choice 
Year                 Choice 
Period               Choice 
Category             Choice 
Status               Choice 
Report Type          Choice 
Assigned To          Person or Group    
History              Multiple lines of text 
Created              Date and Time  
Modified             Date and Time  
Created By           Person or Group    
Modified By          Person or Group    
Checked Out To       Person or Group

I can't reveal the columns of the other lists which are succeeding, but it the same as above plus an addition 10 more, most of which are also Choice and Person or Group type fields.

Answer 1

Edit- Looking at updated details in question, I suspect your list have more than 8 look up columns. And in query we are not passing viewfields and hence it is trying to return all fields. Can you pass viewfield tag with ID column and see if it works.

I just tried below code and used your CAML query for a List which has 115080 items and threshold is set to 20000. I got expected Item's ID. Please refer below. You assumptions is correct is should return data irrespective of happy hours....

function retrieveListItems() {

    var clientContext = new SP.ClientContext(_spPageContextInfo.siteAbsoluteUrl);
    var oList = clientContext.get_web().get_lists().getByTitle('MYList');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View Scope="RecursiveAll"><Query><OrderBy><FieldRef Name="ID" Ascending="False" /></OrderBy></Query><RowLimit>1</RowLimit></View>');
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(collListItem);

    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        

}

function onQuerySucceeded(sender, args) {

    var listItemInfo = '';

    var listItemEnumerator = collListItem.getEnumerator();

    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id();


    }

    alert(listItemInfo.toString());
}

function onQueryFailed(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

Below is response I got, last item id shown in alert.

Hope this helps...Happy coding..!!!