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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值