I am trying to mimic SQL"IN" operation using CAML. Lets take a scenario where we need fetch all the ListItems with ID within a list of IDs. The target list is realy large ( 50k records)

Possible options are from the Working with large list Whitepaper

-> CAML (Object model) -> CAML (using Webservices) -> Portal Site Map Provider -> Search API

The Custom webpart that we are developing is on live data ( NO search API) and query is user dependent (No Portal Site provider)

As our code is deployed on to the server, we can use SPobject model SPQuery, but as CAML deosn't support "IN" Operator and not more than two logical operators in we have to nested query.

When the nested query hits the 478 nested ORs then the exception is raised

"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries"

Firstly the Row lmit 2000 is resulting in multiple queries and on top of it, spliting it further results in performance impact.

Is there any other better solution???

  • How many items are in your IN clause? If it is unbounded then you will always hit a limit and need to request the results in batches. If it is bounded perhaps the query can be written to avoid the nesting limit.
    – Steve P
    Commented Dec 7, 2010 at 20:51

1 Answer 1


How about breaking up your query into smaller batches and then aggregate the results. An approach is described here: http://rapidapplicationdevelopment.blogspot.com/2007/05/caml-nested-too-deep.html

  • @ Ashish : I have already mentioned this solution on the question, this has performance impact :(
    – Vamsi
    Commented Oct 26, 2010 at 4:35
  • Have you measured the performance impact? I'm sure its smaller than you might think.
    – Steve P
    Commented Dec 7, 2010 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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