0

I'm getting the following email every time I update my Visual Force page.

Operation: /apex/ChurchHistoryDev

By user/organization: blah/blah

Caused the following Apex resource warnings:

Number of SOQL queries: 51 out of 100

(these emails can be disabled from the user detail page for this user)

My page displays 5 charts and has a total of 8 soql queries. I'm getting a warning about 51? /I looked at the salesforce governor help page but doesn't really give a lot of detail like most salesforce documentation. I am pretty new to salesforce development so might not be understanding this correctly. Can someone explain this?

Below is my code:

public with sharing class ChurchHistoryDevController {

public String ChurchId;     // Egiving church Id
public String ChurchName;
//public Double ContribAverage;
public Date StartAvgDate;
public Date StopAvgDate;
public Date ChurchSignupDate;

public List<ChurchHist__c> ChurchData;

public ChurchHistoryDevController() {
    ChurchId = ApexPages.currentPage().getParameters().get('id');
    setChurchName();
    // Get last 6 months of Church History
     ChurchData = [Select EndOfMonth__c, ContribForMonth__c, MonthName__c, UsersForMonth__c,
                AchContrib__c, CcContrib__c, ContribYTD__c
                 from ChurchHist__c where ChurchId__c = :ChurchId and 
                 EndOfMonth__c >= LAST_N_MONTHS:6
                 order by EndOfMonth__c];

    ChurchSignupDate = GetEgivingSignupDate();
    StartAvgDate = ChurchSignupDate.addDays(-90);
    StopAvgDate = ChurchSignupDate.addDays(90);

    System.debug('Hello');

}



//*****************************************************
// Church name get and set
//*****************************************************
public void setChurchName() {
    Account acct = [select Name from Account where Customer_ID__c = :ChurchId];
    ChurchName = acct.Name;
}

public String getChurchName() {
    return ChurchName;
}

//*****************************************************
// Egiving SignupDate 
//*****************************************************
public Date GetEgivingSignupDate()
{
    Account acct = [select EgSignupDate__c from Account where Customer_Id__c = :ChurchId];
    return acct.EgSignupDate__c;
}

//*****************************************************
// Active members
//*****************************************************
public Decimal GetActiveMembers()
{
    AggregateResult[] ch = [select max(ActiveMembers__c) Mems from ChurchHist__c where ChurchId__c = :ChurchId];
    return (Decimal)ch[0].get('Mems');
}
//*****************************************************
// Church Id get
//*****************************************************
public String getChurchId() {
    return ChurchId;
}


//*****************************************************
// Contrib / Month 
//*****************************************************
public List<ContribData> getContribForMonthData() {



    List<ContribData> cd = new List<ContribData>();
    for (ChurchHist__c c : ChurchData)
    {
        ContribData tmp = new ContribData();
        tmp.ContribForMonth = c.ContribForMonth__c;
        tmp.MonthName = c.MonthName__c;
        tmp.EndOfMonth = c.EndOfMonth__c;
        tmp.AvgContrib = getContribAvg();
        cd.add(tmp);
    }
    return cd;
}

public Double getContribAvg() {


    Decimal members = GetActiveMembers();

    AggregateResult[] ch = 
        [select avg(ContribForMonth__c) Average
        from ChurchHist__c
        where ActiveMembers__c >= :members - 50 and ActiveMembers__c <= :members + 50 and 
            CustomerId__r.EgSignupDate__c >= :StartAvgDate and 
            CustomerId__r.EgSignupDate__c <= :StopAvgDate];

    //ContribAverage = (Double)ch[0].get('Average');
    if (ch.size() > 0) {
        return (Double)ch[0].get('Average');
    } else {
        return 0.0;
    }

}

public class ContribData {
    Double ContribForMonth { get; set; }
    String MonthName { get; set; }
    Date EndOfMonth { get; set; }
    Double AvgContrib { get; set; }
}

//*****************************************************
// Users / Month 
//*****************************************************


public List<UserForMonthData> getUsersForMonthData() {
    List<UserForMonthData> UserData = new List<UserForMonthData>();
    for (ChurchHist__c c : ChurchData) {
        UserForMonthData tmp = new UserForMonthData();
        tmp.UsersForMonth = c.UsersForMonth__c;
        tmp.MonthName = c.MonthName__c;
        tmp.EndOfMonth = c.EndOfMonth__c;
        tmp.AvgUsersForMonth = getUserForMonthAvg();
        UserData.add(tmp);  
    }
    return UserData;
}

public Double getUserForMonthAvg() {


    Decimal members = GetActiveMembers();

    AggregateResult[] ch = 
        [select avg(UsersForMonth__c) Average
        from ChurchHist__c
        where ActiveMembers__c >= :members - 50 and ActiveMembers__c <= :members + 50 and 
            CustomerId__r.EgSignupDate__c >= :StartAvgDate and 
            CustomerId__r.EgSignupDate__c <= :StopAvgDate];

    return (Double)ch[0].get('Average');
}


public class UserForMonthData {
    Double UsersForMonth { get; set; }
    String MonthName { get; set; }
    Date EndOfMonth { get; set; }
    Double AvgUsersForMonth { get; set; }
}


//*****************************************************
// Ach / Month 
//*****************************************************


public List<AchForMonthData> getAchsForMonthData() {   // achsformonthdata
    List<AchForMonthData> AchData = new List<AchForMonthData>();
    for (ChurchHist__c c : ChurchData) {
        AchForMonthData tmp = new AchForMonthData();
        tmp.AchsForMonth = c.AchContrib__c;
        tmp.MonthName = c.MonthName__c;
        tmp.EndOfMonth = c.EndOfMonth__c;
        tmp.AvgAchsForMonth = getAchForMonthAvg();
        AchData.add(tmp);   
    }
    return AchData;
}

public Double getAchForMonthAvg() {


    Decimal members = GetActiveMembers();

    AggregateResult[] ch = 
        [select avg(AchContrib__c) Average
        from ChurchHist__c
        where ActiveMembers__c >= :members - 50 and ActiveMembers__c <= :members + 50 and 
            CustomerId__r.EgSignupDate__c >= :StartAvgDate and 
            CustomerId__r.EgSignupDate__c <= :StopAvgDate];

    return (Double)ch[0].get('Average');
}


public class AchForMonthData {
    Double AchsForMonth { get; set; }
    String MonthName { get; set; }
    Date EndOfMonth { get; set; }
    Double AvgAchsForMonth { get; set; }
}


//*****************************************************
// CC / Month 
//*****************************************************


public List<CCForMonthData> getCCsForMonthData() {   // CCsformonthdata
    List<CCForMonthData> CCData = new List<CCForMonthData>();
    for (ChurchHist__c c : ChurchData) {
        CCForMonthData tmp = new CCForMonthData();
        tmp.CCsForMonth = c.CCContrib__c;
        tmp.MonthName = c.MonthName__c;
        tmp.EndOfMonth = c.EndOfMonth__c;
        tmp.AvgCCsForMonth = getCCForMonthAvg();
        CCData.add(tmp);    
    }
    return CCData;
}

public Double getCCForMonthAvg() {


    Decimal members = GetActiveMembers();

    AggregateResult[] ch = 
        [select avg(CCContrib__c) Average
        from ChurchHist__c
        where ActiveMembers__c >= :members - 50 and ActiveMembers__c <= :members + 50 and 
            CustomerId__r.EgSignupDate__c >= :StartAvgDate and 
            CustomerId__r.EgSignupDate__c <= :StopAvgDate];

    return (Double)ch[0].get('Average');
}


public class CCForMonthData {
    Double CCsForMonth { get; set; }
    String MonthName { get; set; }
    Date EndOfMonth { get; set; }
    Double AvgCCsForMonth { get; set; }
}

}

  • The more I look at this yes, I see where I call the same function 4 times. Then the relation fields in the query. But still don't see 51 there. – Randy R Feb 12 '14 at 6:17
3

Generally, using queries inside of getters or setters can cause problems; they can be called in arbitrary order and/or multiple times. Leave queries and so on strictly inside constructors and action functions so that you have better control over your code's flow.

You can tell which query is the primary offender by opening the Developer Console, setting Profiling to Finest, then loading your page. The cumulative usage area will include which queries were called, from which line of code, and how many times it was called.

Since all of your queries are called from getters or setters, it's safe to assume that many of the extra queries are simply side effects, and moving your logic to action functions will reduce your query count significantly.

| improve this answer | |
  • Funny - I was about to say pretty much the same thing. Sometimes it's much easier to figure out what's going on from the debug logs than staring at code :-) – kibitzer Feb 12 '14 at 6:43
  • Thanks guys. sfdcfox, I understand what you are saying and will try to move queries outside setters and getters. Maybe when the chart calls a getter it gets queried EACH time the field name is in it? Salesforce in big learning curve me. Trying to figure out how everything works together is confusing for now. – Randy R Feb 12 '14 at 6:59
  • 1
    @RandyR Any language has a learning curve, and here's no exception. The problem comes down to the Visualforce execution engine, which uses an undisclosed algorithm for how setters and getters are evaluated (in terms of order, number of times called, etc). As long as you learn that getters and setters should never attempt to perform DML or execute queries, you'll already be far ahead of the curve. – sfdcfox Feb 12 '14 at 7:03
  • @sfdcfox This page is pretty much done I'm just getting the emails now on every little change. I will rewrite it at a later time (I got more projects on my lap). But the next one I start tomorrow I'll follow your advice. – Randy R Feb 12 '14 at 7:13
0

This is number of queries per transaction. So if your class call some other methods which use query - it will count them. Or - I hope that you don't - if you use some query inside the loop it will count each as one query.

Also it would be nice if you could provide us with some code, then we could could stop guessing and start thinking why :)

| improve this answer | |
  • Definitely didn't put a query in a loop :) I pretty much got one chart working and copied and pasted code then changed it for other charts. My code's probably not the most efficient since like I said I'm a newbie at salesforce. I'm normally a C# guy but got "assigned" to salesforce at my company LOL :) – Randy R Feb 12 '14 at 6:06
  • Updated post to include my code. – Randy R Feb 12 '14 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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