SharePoint 2019 Optimize CAML Query performance for large lists and large number of users to query list

Question

Problem:

I have a production SharePoint farm that contains 10 web front ends with dedicated redundant distributed cache servers and BLOB cache configured, recently I developed an ASCX control to retrieve only 1 item from a SP list that contains more than 11K items, the retrieval of the item is based on a CAML query (server-side code), the query takes 2 parameters (ID and Mobile number).

on submit button click event, I am checking if the item is there or not with a boolean function as below:

if (CheckItems()) { lblSuccessMsg.Text = "Applicant Name " + ApplicantName + " " + "ID number " + fID + " " + "Mobile number " + fMobile + "<br/>" + "<br/>" + "<div class='mainDetail'>" + "<div class='details'>Subject: <span>" + Subject + "</span></div><br/>" + "<div class='details'>Stage: <span>" + Stage + "</span></div><br/>" + "<div class='details'>Job: <span>" + Job + "</span>" + "</div>"; ...etc } else { lblFailMsg.Text = "no data exists"; } 

Boolean function:

private bool CheckItems() { using (SPSite oSPSite = new SPSite(SPContext.Current.Site.Url)) { using (SPWeb oSPWeb = oSPSite.OpenWeb("relativeurl")) { SPListItemCollectionPosition position = null; do { SPList ApplicantsList = oSPWeb.Lists["listname"]; SPQuery spQuery = new SPQuery(); spQuery.Query = "<Where><And><Eq><FieldRef Name='NationalID' /><Value Type='Text'>" + txtNationalID.Text + "</Value></Eq><Eq><FieldRef Name='MobileNo' /><Value Type='Text'>" + txtMobileNo.Text + "</Value></Eq></And></Where><RowLimit>100</RowLimit>"; SPListItemCollection items = ApplicantsList.GetItems(spQuery); position = items.ListItemCollectionPosition; if (items.Count > 0) { for(int i = 0; i < 100 && i < items.Count; i++) { SPListItem item = items[i]; ApplicantName = item["applicantName"] != null ? item["applicantName"].ToString() : "no data"; NationalID = item["NationalID"] != null ? item["NationalID"].ToString() : "no data"; MobileNo = item["MobileNo"] != null ? item["MobileNo"].ToString() : "no data"; Subject = item["Subject"] != null ? item["Subject"].ToString() : "no data"; Stage = item["Stage"] != null ? item["Stage"].ToString() : "no data"; Job = item["Job"] != null ? item["Job"].ToString() : "no data"; } return true; } else { return false; } } while (position != null); } } } 

My questions is:

Is there any problem with the CAML query its self, will it make any bottlenecks/slowness in performance or causing the site to be unavailable?, where this query should be run by large number of users , let's say around 100K users, does the query in the boolean function needs optimization or any further enhancement?

Note: The fields being used in the query are indexed in the SharePoint list.

Your help is much appreciated.

Answer 1

Your code is fine, if you are expecting only one item (maybe) you can remove <rowlimit>. Or limit to 1.

But i personally use search query;

http://server/_api/search/query?query_parameter=value&query_parameter=value 

Search query is much more faster in a long term. And also SharePoint Search Query Tool is helpful for building search queries. You may need to decide to use continuous crawl or incremental. Depends on how instant you need to see the item after inserted to list.

Here is more information about Search Rest Api. You can use this on your ascx control.