I want to make sure the following CAML is best way to get a single record out of 100,000 items from a list. I know there are other versions where CAML goes through each record thus takes really long to return a single record.

    <List ID="GUID-HERE" />
      <FieldRef Name="Salescode" />
      <Value Type="Text">74558-7458</Value>
    <ViewAttributes Scope="Recursive" />
  • Can't you split your content accross several lists? For exemple, one list per country/sell team/product family, ... and aggregate the result using either cross site query or search quey ? – Steve B Nov 6 '14 at 15:19

CAML is the best way to go for quering the list . But since you have 100,000 your list will have to overcome the list throttling . The list throttling can be change in central admin ,but its better to do in code as mentioned below:

var query = new SPQuery();  
query.Query = "<Where><Eq><FieldRef Name=\"Title\" /><Value Type='Text'>Very Special    Item That I Need</Value></Eq></Where>";  
query.QueryThrottleMode = SPQueryThrottleOption.Override;

Apply query.ViewFields and get only specific fields from the list,Avoid the Recursive attribute.

Use query.Rowlimit properties and follow best practices for CAML .

| improve this answer | |

Make sure field SalesCode is indexed.

If you are expecting only one record then apply RowLimit

Follow Microsoft's recommendation of maintaining large lists http://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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