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.
相关推荐
#ifndef __LIST_H__ #define __LIST_H__ struct list; struct list *list_init();...int list_lookup(struct list *list, int value); int list_indexof(struct list *list, int value); #endif /* __LIST_H__ */
【亲测可用】Revit 2020 Lookup Tables是Revit表格查找工具,是revit开发必不可少的一个组件,需要的朋友可以下载! 用法:将RevitLookup.addin和RevitLookup.dll放到C:\ProgramData\Autodesk\Revit\Addins\2019下...
Java Methods-Lookup Tables and Hashing.ppt
Using SAP PI Lookup API and Dynamic Configuration in SAP GRC NFE Outbound B2B Interface for Dynamic E-mail Determination
Easy DNS Lookup and Reverse Name lookup using qdns.dll (dll vb source is included in zip). For use see included sample ASP page.
The characterized lookup table can be utilized in open-loop torque control algorithms, and is verified to achieve high torque control accuracy and suitable for torque control of IPM in automotive ...
RevitLookup-2021.0.0.13,供各位做REVIT二次开发的同仁们下载、学习、研究。
vc错误查看工具lookup vc错误查看工具lookup
Revit二次开发必助插件revit lookup,辅助开发人员查看revit内部结构。方便开和学习内部结构
Revit2018的Lookup
requestUtils lookup方法简介
revit开发必备工具 本包中是已经编译过的文件,可以直接使用 ...编辑其中RevitLookup.dll 路径为编译路径 C:\RevitLookup-2016.0.0.6\CS\bin\Debug\RevitLookup.dll 重启revit: 即可在菜单栏看到工具
informatica lookup 组件使用案例
Revit 2018 Lookup Tables顾名思义就是Revit表格查找工具,revit二次开发是离不开这个按钮的,由于revit中管件和线管配件族的尺寸参数会随着公称直径的变化而变化,revit可以通过这个按钮来实现这个功能,是revit...
详解lookup滤镜原理及用法,Lookup是基于图片颜色映射的一种滤镜,它可以实现各种各样的效果。
excel 函数教程 lookup函数的经典查找使用方式
revit lookup 2020安装包,编译好的安装包msi文件,一键安装即可方便快捷,安装之后打开Revit选择总是加载即可永久使用此插件。
LOOKUP函数处理内存数组,LOOKUP高级应用.
解决docker报错dial tcp lookup registry-1.docker.io
用NetBeans8.0做的的一个lookup查询的例子