SharePoint list lookup column indexing and CAML query relation

I’m developing the SharePoint site with so many lists. First I didn’t consider the indexing of the columns of lists. But as the lists are going to have large number of items the performance is decreasing. So I started to index the columns of lists to increase the performance. After indexing I get superb performance for searching. But I get stared one problem when I used lookup column of list as indexed column. My CAML query using the lookup column for searching stopped returning results, which out any error or exception.

                This problem stretched my hairs. After lot of time spending for searching any solution I found one blog (http:/blackninjasoftware.com/2009/05/12/why-should-indexing-a-sharepoint-field-break-your-caml-query/) where the person had the same problem like me. From his experiments he solved the problem. I’m mentioning the trick he used here also and any one can refer the blog also.

                Here I’m going to describe the whole thing using one example so; the new one in SharePoint can also understand the problem and use the CAML query for right way.

                So, let’s start.  I created on web site in SharePoint and created two lists 1) Users and 2) User Rights. I’ve on column “UserName” in “Users” list. In “User Rights” I’ve two columns a) “Right” as Simple text and b) “User” as Lookup to the “Users” list “UserName” column. I didn’t indexed any of the columns in “User Rights” list. I created 3000 items in “Users” list. For each item in “Users” list, I created 100 items in “User Rights” list. So I’ve 300000 items in “User Rights”. After that I created a test web part which used to different type of CAML queries to get the “User Rights” items based on the “UserName”.

                My first CAML query used the “UserName” field value as the parameter and the query is as follows.

                “<Where><Eq><FieldRef Name=’User’ /><Value Type=’Lookup’>UserName</Value></Eq></Whre>”.

                My second CAML query used the item ID of “Users” list as parameter and the query is as follows.

                “<Where><Eq><FieldRef Name=’User’ LookupId=’TRUE’ /><Value Type=’Lookup’>ID of “Users” list item</Value></Eq></Where>”.

                Both of the queries runs successfully when there is no index defined on the “User” columns of “User Rights” list. But when I indexed that column, the first query is working fine. But the second query stopped. Then as I wrote above, I tried with the solution, and changed the query as follows.

                “<Where><Eq><FieldRef Name=’User’ LookupId=’TRUE’ /><Value Type=’Integer’>ID of “Users” list item</Value></Eq></Where>”.

                And it surprised me. That query worked.

                So I’ve to change my entire CAML query in my project.

                After this I tried to find that after how many entries this CAML query does not work, the result surprised me. Up to 1999 entries my CAML query worked fine with indexed lookup column. As soon as the 2000^th entry is created it blows off. Then I tried it with updated one and it worked again.

                So, I’m advising the person who are using CAML query, consider this to use the following type of query to search the data.

                “<Where><Eq><FieldRef Name=’User’ LookupId=’TRUE’ /><Value Type=’Integer’>ID of “Users” list item</Value></Eq></Where>”.

Also consider the indexing of the columns for better performance.

                Now, I’ll try to find the cause of this query change that why one type is working and the other type is not working.