Wednesday, November 23, 2016

Throttled:Query exceeds lookup column threshold

One of my apps gets its data via lists.asmx. In a particular environment, no data was being returned and I found these entries in ULS:

Throttled:Query exceeds lookup column threshold. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070093 List internal name, flags, and URL: {list-guid}, flags=0x0008000000c4108c, URL="web-url/_vti_bin/lists.asmx" Current User: {some-id} Query XML: "<Query><OrderBy><FieldRef Name="ContentType"/></OrderBy></Query>" SQL Query: "N/A"

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.

Query exceeds lookup column threshold. List: {list-guid}, View: , ViewXml: <View Scope="RecursiveAll" IncludeRootFolder="True"><Query><OrderBy><FieldRef Name="ContentType" /></OrderBy></Query><ViewFields>... and a whole ton of <FieldRef> entries.


SOAP exception: Microsoft.SharePoint.SPQueryThrottledException: The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator. ---> System.Runtime.InteropServices.COMException (0x80070093): The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.    


I found a few references to this, but it is not the more common 5000 item List View Threshold throttling. Instead this is the List View Lookup Threshold setting of 8 (12 in SharePoint 2013).

It turns out that my app is using the default view and not specifying which fields to return in the query. In fact, there is a Level=Medium ULS log entry that has my exact query. Putting this into powershell as follows, I was able to confirm the behaviour:

$webUrl = 'some-web-url'
$listName = '{list-guid}' # note: keep the curly braces

$credential = Get-Credential -Message "Enter your login for $($webUrl)"

$uri = [uri]"$($communityRoot)/_vti_bin/lists.asmx"

$contentType = 'text/xml'
$bodyString = @"
<?xml version="1.0" encoding="utf-8"?>
  <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
    <soap12:Body>
      <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
        <listName>$($listName)</listName>
        <viewName></viewName>
        <rowLimit>5000</rowLimit>
        <query>
          <Query xmlns="">
            <OrderBy> <FieldRef Name="ContentType" /> </OrderBy>
          </Query>
        </query>
      <viewFields><ViewFields xmlns="" /></viewFields>
      <queryOptions>
        <QueryOptions xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>
          <IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>
          <ViewAttributes Scope='RecursiveAll' IncludeRootFolder='True' />
          <DateInUtc>TRUE</DateInUtc>
        </QueryOptions>
      </queryOptions>
    </GetListItems>
  </soap12:Body>
</soap12:Envelope>
"@

$result = Invoke-WebRequest -Credential $credential -Method Post -Uri $uri -Body $bodyString -ContentType $contentType
$result.RawContent


To confirm the view and the number of lookup columns, I used this powershell:

$webUrl = 'some-web-url'

$web = Get-SPWeb $webUrl
$list = $web.Lists['Shared Documents']
$fields = $list.Fields
$defaultViewXml = [xml]$list.DefaultView.GetViewXml()
Write-Output "Default View: $($list.DefaultView.Title)"
$defaultViewXml.View.ViewFields.FieldRef | Format-Table @{l="Type"; e={$fields.GetField($_.Name).TypeDisplayName}}, Name -AutoSize -Wrap


Reference: This is a very well written article on GetListItems - https://msdn.microsoft.com/en-us/library/websvclists.lists.getlistitems(v=office.14).aspx





No comments:

Post a Comment