Salesforce: System.LimitException: Too many SOQL queries: 101

Question

Everything worked fine a couple of days ago, no new changes have been made to the system except from the profiles.

Suddenly I got this error message:

Error: Invalid Data. Review all error messages below to correct your data.
Apex trigger generateUniqueBidId caused an unexpected exception, contact your administrator: generateUniqueBidId: System.LimitException: Too many SOQL queries: 101

 trigger generateUniqueBidId on Bids__c (before insert) {

    Map<String, String> profilePrefixMap = new Map<String, String>();
    profilePrefixMap.put('Sales User', 'rds');
    profilePrefixMap.put('Super User', 'rds');
    profilePrefixMap.put('Standard User','SU');
    profilePrefixMap.put('System Administrator','SysAdmin');

    String defaultPrefix = ' ';
    User user = [SELECT ProfileId FROM User Where Id = :Userinfo.getUserId() LIMIT 1];
    Profile profile = [SELECT Name FROM Profile Where Id = :user.ProfileId LIMIT 1];
    List<Bids__c> recentBids = [SELECT Id, CreatedById, Bid_unique_label__c From Bids__c WHERE Bid_unique_label__c = NULL];

    /**Logics for updating old Bids with correct Bid_id. May be refactored away when all bids are updated
       Could cause problems if trigger system becomes to complicated. 
    */
    for (Bids__c bid : recentBids){
        String recentProfileName = [SELECT Profile.Name FROM User Where Id = :bid.CreatedById LIMIT 1].Profile.Name;
        String bidId = profilePrefixMap.get(recentProfileName);
        bid.Bid_unique_label__c =  ((bidId == null) ? defaultPrefix : bidId);           
    }
    upsert recentBids;

    for (Bids__c bid : trigger.new){
        String bidId = profilePrefixMap.get(String.valueOf(profile.Name));
        bid.Bid_unique_label__c = ((bidId == null) ? defaultPrefix : bidId);
    }

}

Answer 1

You have a SOQL in a loop.

for (Bids__c bid : recentBids){
    String recentProfileName = [SELECT Profile.Name FROM User Where Id = :bid.CreatedById LIMIT 1].Profile.Name;
    String bidId = profilePrefixMap.get(recentProfileName);
    bid.Bid_unique_label__c =  ((bidId == null) ? defaultPrefix : bidId);           
}
upsert recentBids;

This is a performance killer and in fact I'm surprised the limit is 100 queries, used to be no more than 20 queries in a trigger context. A quick fix for your problem would be to use relationship queries with the "dot notation". This will eliminate the need to make the query in a loop.

List<Bids__c> recentBids = [SELECT Id, CreatedBy.Profile.Name, Bid_unique_label__c 
FROM Bids__c 
WHERE Bid_unique_label__c = NULL];

for (Bids__c bid : recentBids){
    String recentProfileName = bid.CreatedBy.Profile.Name;
    String bidId = profilePrefixMap.get(recentProfileName);
    bid.Bid_unique_label__c =  ((bidId == null) ? defaultPrefix : bidId);           
}
upsert recentBids;

This code still has some problems.

1. You might hit a limit of no more than 10K rows inserted/updated in single transaction (you should at least add LIMIT clause).
2. You're mixing work on data that's in the context of the trigger with data fixes on some unrelated stuff. You shouldn't "piggyback" because users won't understand why they see an error coming from data they didn't touch.
3. Not to mention the fact that "last modified by" is now worthless in your system.

If it's one time activity to clean up your bad data (fix unique labels) - consider running a data fix and removing this code. If it's really a recurring situation - write a batch job.

Or investigate if you can write a workflow rule to generate the unique keys, looks like it's be doable and config beats coding a solution.