+ Start a Discussion
Guyver118Guyver118 

Help Too many script statements: 50001

I have trigger that basically updates the account owner to the corresponding user.

 

//trigger trigger AccountBeforeUpdateEvents on Account (before update) { AccountOpeningUtils_v1.InitUserList(); AccountOpeningUtils_v1.UpdateAccountOfficer(trigger.new); } public class AccountOpeningUtils_v1 { public static void InitUserList() { Boolean inLock = (OpportunityHelper_v1.IsUpdatingAccount() == true || CaseHelper_v1.IsUpdatingAccount() == true); if(!inLock)UserTriggerUtils_v1.InitUserList(); } public static void UpdateAccountOfficer(List<Account> accList) { for(Account a : accList) { if(!UserTriggerUtils_v1.GetUserList().isEmpty() && !UserTriggerUtils_v1.ValueIsNull(a.Account_Officer__c) && a.Account_Officer__c.length() >= 3) { String oCode = a.Account_Officer__c.substring(0, 3).trim(); User officer = UserTriggerUtils_v1.FindAccountOfficer(oCode); if(!UserTriggerUtils_v1.SObjectIsNull(officer))a.OwnerId = officer.Id; } } } } global class UserTriggerUtils_v1 { private static List<User> userList = new List<User>(); private static Map<String, Schema.SObjectField> userFields = Schema.sObjectType.User.fields.getMap(); public static void InitUserList() { String sQuery = 'Select '; Integer i = 0; for(String f : userFields.keySet()) { i ++; sQuery += (i == userFields.keySet().size() ? f : f + ', '); } sQuery += ' FROM User WHERE IsActive = TRUE'; System.debug('### QUERY ###: ' + sQuery); try { userList = Database.query(sQuery); System.debug('### USER LIST ###: ' + userList); } catch(System.QueryException e) { System.debug('### Exception found in userList for' + ' InitUserList() ###:' + e.getMessage()); } } }

when i use data loader i get too many script statements

Best Answer chosen by Admin (Salesforce Developers) 
ThomasTTThomasTT
And use a map to find a match rather than a loop. It's O(1) rather than O(n).
ThomasTT

All Answers

WesNolte__cWesNolte__c

Hey

 

You've got one of two situations(or a combination):

 

1. Your data set is massive so the number of lines of apex that is being run in a session is massive, and/or

2. Your loops are not exiting, or running too many times.

 

The second point is probably tightly coupled with the first point. You could potentially rework your logic and try to loop through a  more specific dataset, or, if that's not possible, you could chop your data into chunks and load it in those chunks(or batches as most people call them, but chunk is just a bit more fun to say).

 

You might also want to look up 'governor limits' in the apex language reference, there's some pretty sweet info in there.

 

Wes

Guyver118Guyver118

this is the main function that finds the account officer

 

public static User FindAccountOfficer(String officerCode) { if(!userList.isEmpty()) { for(User a : userList) { if(!ValueIsNull(a.Account_Officer_Code__c) && a.Account_Officer_Code__c.equals(officerCode)) { return a; } } } return null; }

 

 

 

In theory what is happening that each account goes through this loop and sees if it can assign an account officer as the owner. I am wondering maybe the data set is too large and it hits too many script statements becasue it has to be for each account that goes through that method.

 

the method does end or do i have to add break i.e

 

return a;
break;

ThomasTTThomasTT
This is a beautiful code! I can learn from it many things, but I'd like to make sure that this issue is not about the inner class so that I can use the same approach!
The code you posted seems fine. Max batch size is 200, so it can't be more than 50k steps. However, there seems more functions in Userxxxxx classs. Those may be the problem.

Or SFDC may be counting inner classs step wrongly... I don't know. We just eliminate all possibility from our side first.

As a temporary solution, you can reduce the batch size. It may sliw it down, but better than nothing, right?

ThomasTT
ThomasTTThomasTT
Posting at the same time...
That's it. 200 x 1000 > 50k even if 1 step per record!
ThomasTT
WesNolte__cWesNolte__c

The 'return' will 'break' your loop, but if it doesn't find anything it will run the length of the list. You could cut down your dataset, or you could get tricky and try to optimise the 'find'. Assuming officercodes are unique, one way to make it search more efficiently would be,

 

1. Get all officer codes into a list.

2. Sort the list.

3. Binary search through the list.

 

Just in case you've never heard of a binary search it works more or less like this,

 

I have an ordered list I want to search eg.

 

'a','b','c','d','e','f'

 

Let's say I'm look for 'e'.

 

1. Choose a pivot point somewhere in the middle of the list, let's say element 3.

2. Compare what we're looking for against what is at element 3.

3. If the searched element is less than the pivot element, then make a list out of the elements less than element 3, and you repeat steps 1 through 3. Similarly if the searched element is greater than the pivot element.. and if it is the pivot element you've found your man.

 

 Let's consider a list of 100 elements. using the simple go-through-everything approach you will, on average search through 50 elements. However, using the search above you would never go through more than 10.

 

More on binary search can be found here.

 

Wes

WesNolte__cWesNolte__c
I've just come across the quicksort doc which uses a very similar algorithm but is written a bit better. You can get it here here.
ThomasTTThomasTT
And use a map to find a match rather than a loop. It's O(1) rather than O(n).
ThomasTT
This was selected as the best answer
WesNolte__cWesNolte__c
That's a really good idea!
Guyver118Guyver118

i think i will put the users in a map but wouldn't that still go in a loop for at least the assign each user to a map etc..

 

 

for (User a : userList)

{

     userMap.put(a.officerCode, a);

}

 

wouldn't it hit the limit here?

WesNolte__cWesNolte__c
If you stored this list as a static var, you'd have to init it once and not loop through it again within that session. A very good solution for a simple search:)
Message Edited by weznolte on 10-21-2009 02:56 PM
Guyver118Guyver118
which it is :0, thanks guys will do some testing
ThomasTTThomasTT

Yes, that's the smart point of his code. He is using inner class as a utility, so it's easy to set such cache as a static variable. Even other trigger invoked in the same execution can use the cache.

Oh, to share the cache with other trigger, maybe it has to be a class.

ThomasTT

Guyver118Guyver118

Just done a test in data loader with the following changes no governor limits hit :0 test on 30k account updates

 

public class AccountOpeningUtils_v1 { public static void InitUserList() { Boolean inLock = (OpportunityHelper_v1.IsUpdatingAccount() == true || CaseHelper_v1.IsUpdatingAccount() == true); if(!inLock)UserTriggerUtils_v1.InitUserList(); } public static void UpdateAccountOfficer(List<Account> accList) { for(Account a : accList) { String oCode = a.Account_Officer__c; if(oCode == null)return; User officer = UserTriggerUtils_v1.FindAccountOfficer(oCode); if(!UserTriggerUtils_v1.SObjectIsNull(officer))a.OwnerId = officer.Id; } } } global class UserTriggerUtils_v1 { private static List<User> m_UserList = new List<User>(); private static Map<String, User> m_UserOfficerMap = new Map<String, User>(); private static Map<String, Schema.SObjectField> m_UserFields = Schema.sObjectType.User.fields.getMap(); public static void InitUserList() { String sQuery = 'Select '; Integer i = 0; for(String f : m_UserFields.keySet()) { i ++; sQuery += (i == m_UserFields.keySet().size() ? f : f + ', '); } sQuery += ' FROM User WHERE IsActive = TRUE'; System.debug('### QUERY ###: ' + sQuery); try { m_UserList = Database.query(sQuery); System.debug('### USER LIST ###: ' + m_UserList); } catch(System.QueryException e) { System.debug('### Exception found in userList for' + ' InitUserList() ###:' + e.getMessage()); } if(!m_UserList.isEmpty()) { for(User a : m_UserList) { String officerCode = a.Account_Officer_Code__c; if(!ValueIsNull(officerCode))m_UserOfficerMap.put(officerCode.toUpperCase(), a); } } } public static Map<String, User> GetUserOfficerMap() { return m_UserOfficerMap; } public static Map<String, Schema.SObjectField> GetUserFields() { return m_UserFields; } public static Boolean ValueIsNull(Object val) { if(val == null || String.valueOf(val).length() == 0) { return true; } return false; } public static Boolean SObjectIsNull(SObject val) { if(val == null)return true; return false; } public static User FindAccountOfficer(String officerCode) { String oCode = officerCode.substring(0, 3).trim(); if(!m_UserOfficerMap.isEmpty() && oCode != null) { return m_UserOfficerMap.get(oCode); } return null; } }

 

WesNolte__cWesNolte__c
High-5s all round :) Well done.
ThomasTTThomasTT

I finally realized that AccountOpeningUtils_v1 is not defined in the trigger... you just put them in the same code box. I've heard that there is a way to define private method in the trigger (it can be defined in the trigger{} scope) and I thought we can define pricate class too...

anyway, High 5s!

 

ThomasTT