3

All,

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???

Improve this question
1
  • 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
    CommentedDec 7, 2010 at 20:51

1 Answer 1

Reset to default
2

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

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.