Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
    
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 Dec 7 '10 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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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