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