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、付费专栏及课程。

余额充值