function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
carramrod81carramrod81 

Apex Execution Limits - a little insight please

Hi,
I successfully wrote my first apex class/trigger AND got it into production with Eclipse after all was working on my dev account.
It works, but i started getting email exceptions that the class threw an exception because of too many SOQL queries (21).
This happened when i was doing a mass import of previous_user_codes__c. I know which part of the code is calling too many queries (in bold) but can't figure out how to move the the query outside the loop so i don't hit the limits. It works fine when the trigger is only one record, but as soon as it goes through with multiple Previous_User_Code__c (in bulk) it fails. FWIW i was loading 12000 Previous_User_Code__c.
Code:
public class cPreviousUserCodeRecordSave{
 public static void checkForPrevious(Previous_User_Code__c[] pucs) {
  for (Previous_User_Code__c p:pucs) {
   if (p.Previous_User_Code__c != null)  {
   p.Name = p.Previous_User_Code__c;
   String code = p.Previous_User_Code__c;
   //load all accounts usercodes for this Previous User Code
   Account[] Matches = [SELECT Name,ID,M_User_Code__c FROM Account
      WHERE Account.M_User_Code__c =: code];
    
    if(Matches.size() >= 1 && Matches[0].ID != p.Account__c) {
    //this ID is already in another account, throw a warning
    //do not allow saving in the Account.M_User_Code__c
                  p.Previous_User_Code__c.addError('This User Code is associated with another 
   account.<br> Account: ' +  '<a href=https://na2.salesforce.com/' + Matches[0].ID + ' target=newWindow1>' + 
   Matches[0].Name + '</a>');
    }
   }
  }
 }
}



Message Edited by carramrod81 on 05-29-2008 07:39 AM
jrotensteinjrotenstein
Umm, can you explain a little bit more about what you're trying to achieve with this code? How is it being called? Why are you only looking at Matches[0]? What is a previous_user_code?
carramrod81carramrod81
The purpose of this code is to track all the Previous user codes that this account has had in the past.
On the account level there is a field called "user code", which is an external ID and the current User Code.
There is a custom object named Previous_User_Code__c, which contains previous user codes for the associated account. It has the fields Name, Account (reference), Previous_User_Code__c.

There are two checks that are made (by 2 different classes) when an Account Record is inserted/updated and account.user code isn't null.

There are two steps:
Step1 - Account Record: (Class not shown, very similar to code in initial post)When the account record is saved and Account.User Code isn't null, it checks the Previous_User_Code__c custom object and looks to see if  Account.user code exists in any Previous_User_Code__c.
If it does, and is tied to this account, nothing happens.
If it exists and is tied to a different account, an error is thrown.
Finally, if it doesn't exist, the account record is saved and it creates a new Previous_User_code__c object tied to this account.

Step2 - Previous_User_Code__c record: (Class is shown in intial post, and activated by trigger on update/insert) Because a new Previous_User_Code__c can be created seperately of the above process, we check to see if the new/updated user code exists any any account.user code.
If this new/updated Previous_User_Code__c exists on this account.user code record, nothing happens/record is updated.
If this new/updated Previous_User_code__c exists on a different account.user code record, an error is thrown.
Finally, if the new/updated Previous_User_Code__c doesn't exist on any account.user code then it is inserted.

So what is happening, is that it's failing on the assignment of Matches to the query of objects because it is in the loop. I know i need to move this assignment out of the loop because when this trigger fires and calls this class with more than one Previous_UseR_Code__c, it's hitting it's limits. Right now, it's assuming that this class fires one object at a time, not in bulk. It fails when in bulk. I also know that since my class for Step1 is very similar (same structure) as the class shown for Step2, it will fail at this point as well. I know that if i can get the class shown in my initial post set for bulk processing, i shouldn't have a problem adapting it to the class for Step1.





Message Edited by carramrod81 on 05-29-2008 07:24 AM
jrotensteinjrotenstein
Wouldn't you write something like:

Code:
trigger AccountCodeTrigger on Account (before insert) {

  // Loop through each Account that is new/changed
  for (Account a : Trigger.new) {
    if (a.M_User_Code__c != null) {

      // Find any Previous_User_Code__c entries that match this account
      Previous_User_Code__c[] pucs =
           [SELECT Id, Account__c WHERE Previous_User_Code__c = :a.M_User_Code__c];

      // If none found, create a new pucs record
      if (pucs.size() == 0) {
        <Code to create new pucs record>
      }
      else {
        // Any for a different account?
        for (Previous_User_Code__c p : pucs) {
          if (p.Account__c != a.id) {
            a.addError('Attached to another account');
          }
        }
      }
    }
  }
}

Admittedly, this might also run against limits, if you have too many Accounts being created. To get around that, you could do one SELECT to retrieve all Previous_User_Code__c records that match all of the Accounts being created (done before the loop), and then iterate through the list to find the ones relevant for the Account of interest (done within the loop).



Message Edited by jrotenstein on 05-30-2008 10:30 AM