Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question

1 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;
share|improve this answer
    
If you just try to understand the situation. DiscussionList is a list of SPListItem that's why I looped through all the items inside the discussionList because each item contains a collection of replies. "Replies" is a subfolder inside the Discussion and DiscussionList is a collection of Discussion. I'm actually doing a search inside the folder Replies which is actually a subfolder of discussion. I hope I'm clear on that. :) –  Carls Jr. Oct 3 '11 at 8:00
    
Yup , now it makes sense. Then i would suggest RowLimit under SPQuery , in case you are displaying not all but something like the latest replies. 20,000 is a pretty big amount. Gotta love large lists in sp2010 –  Aabid Ali Oct 3 '11 at 8:03
    
Actually I already have the row limit applied. I am hoping to see an answer somewhat like this (select AllDiscussion from DiscussionList where DiscussionList.Replies contains 'programming') if I could only do this on CAML query. But I guess not :( Rowlimit is given already, I initially display max of 20 items per query. :) –  Carls Jr. Oct 3 '11 at 8:12
    
ouch , then id advice you to take a look at u2u.net/res/Tools/CamlQueryBuilder.aspx it helps build caml queries, feed in the relations and lets see . –  Aabid Ali Oct 3 '11 at 8:20
    
haha I'm using CamlQueryBuilder too but it only gives me simple queries it does not include complicated queries that could possible complicate my life lol –  Carls Jr. Oct 3 '11 at 8:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.