Search Optimization on SPListItemCollection

Question

I have an SPListItemCollection (Thread Discussion Collection) that consists of almost 20,000 items. Each SPListItem (Thread Discussion) on the SPListItemCollection contains a sub SPListItem(Thread Discussion Replies).

I wanted to optimized the speed of the search on the replies. On my current setup, I loop on each discussion and use search for replies that contains the word that I am looking for. Is there a way the I could speed this up? The for each loop on the main discussion thread itself would already consume a lot of memory.

Here is my code snippet

List<string> lstResult = new List<string>();
string strSearch = "programming";
SPList spList = spWeb.List["DiscussionList"];
/// list of discussions
SPListItemCollection DiscussionList = spList.Items;
foreach(SPListItem Discussion in DiscussionList){
    string searchQuery = string.Concat("<Where><Contains><FieldRef Name='Body' /><Value Type='Note'>", strSearch, "</Value></Contains></Where>");
    SPQuery spQuery = new SPQuery();
    spQuery.Query = searchQuery;
    SPListItemCollection Replies = spList.GetItems(spQuery);
    foreach(SPListItem reply in Replies){
        lstResult.Add(lstResults.reply["Body"].ToString());
    }
}

/// return result
return lstResult;

Note: I already use RowLimit so it's a given scenario for a search. I did not include it here because it's not the solution that I'm looking for. I'm looking for a better way of querying items. Somewhat like

Select AllDiscussion From DiscussionList Where Discussion.Replies contains 'search'

I just don't know if it is possible. A miracle might help :D

Answer 1

Okay straight of why did you include both for loop and spquery remove the for loop for the list items , i.e on foreach(SPListItem Discussion in DiscussionList) , use only spquery since you are searching on the discussion list anyways. Also , if it helps you can use rowlimit on Spquery class f you dont want all the items.

Modified code would look like :

List<string> lstResult = new List<string>();
string strSearch = "programming";
SPList spList = spWeb.List["DiscussionList"];
/// list of discussions
SPListItemCollection DiscussionList = spList.Items;

string searchQuery = string.Concat("<Where><Contains><FieldRef Name='Body' /><Value     Type='Note'>", strSearch, "</Value></Contains></Where>");
SPQuery spQuery = new SPQuery();
spQuery.Query = searchQuery;
SPListItemCollection Replies = spList.GetItems(spQuery);
foreach(SPListItem reply in Replies){
    lstResult.Add(lstResults.reply["Body"].ToString());
}


return lstResult;