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 2000th 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.
Thanks in advance for your comments.