public List<phonesterDat> getCompanyData() {
List<phonesterDat> var1 = new List<phonesterDat>();
for (Keyword__c keyword : [select kw__c from Keyword__c]) {
   phonesterDat var = new phonesterDat();
   String str = keyword.kw__c;
   var.setType(str);
   var.setTotal([select count() from Account where DD_Segment__c includes (:str)]);
   var1.add(var);
  }
return var1;
}

The problem in the above code are SOQL queries. Using query in for loop hits governer limits and probably is not best practice.

Here is what I am trying to do..

Trying to retrieve number of accounts which has given keyword in custom field. How can rewrite the above code?

Attribution to: Bhushan Lodha

Possible Suggestion/Solution #1

Due to the way you are currently storing your keywords in a single text field I don't think your going to be able to make your current code any more efficient.

I think your first change should be to create a link object between Accounts and Keyword__c that contains a lookup to each. You should be able to use a quick script like the one above to populate this link table.

Second you will probably have to create a new interface / method to link and unlink the keywords.

Finally you can replace your code above with a query similar to this:

LIST<Keyword__c> keywordids = [select id from Keyword__c];

LIST<AggregateResult> results = [SELECT Keyword_Link__c, count(Account_Link__c) FROM Account_Keyword_Link__c WHERE Keyword_Link__c = :keywordids GROUP BY Keyword_Link__c];

UPDATE

To create a new object follow this guide: https://login.salesforce.com/help/doc/en/dev_objectcreate.htm

You then need to add two new lookup fields, one pointing to the account object and the other to your keywords object.

To actually populate this you will have to write some code that loops through your accounts and looks at which keywords are set on that account then create a record in the new object.

Attribution to: Jon Hazan

Possible Suggestion/Solution #2

Depending on how many keywords there were in your table you could do something with dynamic SOQL perhaps. This example uses the Winter13 String function join, which is pretty cool. This example naively assumes that there is at least one keyword present - so you'd need to test the validity of this assumption. Also there is a limit on the complexity/length of a single query so this won't work with hundreds of keywords.

Any thoughts?

List<String> keywordsOfInterest = new List<String>();
for (keyword__c k : [select kw__c from Keyword__c]) {
    keywordsOfInterest.add(k.kw__c);
}
// Prepare dynamic soql based on keywords
String dsoql = 'select DD_Segment__c, count(id) from Account where DD_Segment__c includes';
dsoql += String.join(keywordsOfInterest, ' or DD_Segment__c includes ');  //Ooooh! new string function
dsoql += ' group by dd_segment__c';
// Note - query will be too complex if number of keywords is large

List<AggregateResult> ars = Database.query(dsoql);

//Do the parsing of AggregateResults to get the count per keyword`enter preformatted text here

Attribution to: Doug B

Possible Suggestion/Solution #3

1. Collect the keywords first
2. Run a select from Account
3. Manually parse the results and pray for not hitting script statements

---

// 1
Map<String, Integer> keywordHits = new Map<String,Integer>();
for (Keyword__c keyword : [select kw__c from Keyword__c]) {
    keywordHits.put(keyword.kw__c, 0);
}

// 2
for(Account a : [SELECT DD_Segment__c FROM Account
    WHERE DD_Segment__c INCLUDES (:keywordHits.keyset())]){
    // 3
    for(String key : a.DD_Segment__c.split(';')){
        keywordHits.put(key, ++keywordHits.get(key));
    }
}

// Final loop to display them.
for(String key : keywordHits.keySet()){
    System.debug(key + ' occurs on picklists in ' + keywordHits.get(key) + ' Account(s)');
}

Something like that should use up only 2 queries.

I'm actually surprised that INCLUDES ('a','b') on multi selects returns rows that have only 'a' and not 'b' :)

Attribution to: eyescream
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4196