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.
