You need to sign in to do that
Don't have an account?
Large SOQL help
Hi folks,
I've written a function that's called by a trigger upon creation of an opportunity that calculates the percentage chance of winning an opportunity based on historical performance given a number of "factors" pertaining to that opportunity, namely: who the opportunity owner is, which product it is we're selling, whether we've sold to this customer before, the total value of the proposed sale, and which account we're selling to (which is subtly different from repeat business). The function works out the previous percentages of successful sales based on each of these criteria and provides a kind of weighted average of the "probability" of winning the opportunity based on these, for forecasting purposes.
The trouble, however, is that I'm getting SOQL limit exceptions in my live environment, which, as you might expect, has a lot more data to draw upon to make these calculations. Having said that, thinking about the number of SOQL queries a single calculation should make, it should be nowehere near the 50,000 limit. The code (below), as I said, works out the percentage chance of winning an opportunity based upon the following, which I've tabulated, and given, what I think is a sum of the total number of SOQL queries induced by a single opportunity being created (to reiterate, the function code is called by a trigger when an opportunity is created, and this trigger does NOTHING else):
Opportunity Owner - nobody in my organisation owns more than about 500 opportunities, so that's 500 SOQL queries.
Account - no account has more than about 200 opportunities recorded against it, taking our total to 700.
Value - I've bucketed the value into three categories, none of which has more than 4,000 members, taking our total to 4,700.
Type - this pertains to whether or not this is repeat business and has three categories, none of which has more than about 4,000 members again, taking our total to 8,700 queries.
Product - again, this has a few categories, but none of these have more than about 2,000 recorded opportunities against them, taking the total to 10,700
So, after these very generous estimates, my code can call no more than 10,700 queries per invocation of the trigger (I understand that this would cause me problems if I want to bulk upload opportunities, but I'll cross that bridge when I come to it), in the meantime, I'm getting the SOQL limit error after only one invocation. My question then, is, given the code, can anybody point me in the direction of a solution to hitting this limit, which, I don't see how I'm hitting?
Apologies for being so verbose, but, hopefully, there shouldn't be anything I need to clarify :/
Thanks,
Lee
public class FCastProb { public static double getProbability(ID own, String opptype, String pipe, ID acc, decimal value){ double ownWins = 0; double ownSize=0; for(Opportunity a: [select Probability from Opportunity where OwnerID =:own and IsClosed=true]){ ownSize+=1; if(a.Probability==100){ ownWins+=1; } } if(ownWins == 0||ownSize==0){ ownSize=1; ownWins=1; } double typeWins = 0; double typeSize = 0; for(Opportunity b : [select Probability from Opportunity where Type =: oppType and IsClosed=true]){ typeSize+=1; if(b.Probability==100){ typeWins+=1; } } if(typeWins==0||typeSize==0){ typeSize = 1; typeWins = 1; } double pipeWins = 0; double pipeSize = 0; for(Opportunity c : [select Probability from Opportunity where Market_Pipeline__c =: pipe and IsClosed=true]){ pipeSize+=1; if(c.Probability==100){ pipeWins+=1; } } if(pipeSize==0||pipeWins==0){ pipeSize = 1; pipeWins = 1; } double accWins = 0; double accSize=0; for(Opportunity d : [select Probability from Opportunity where AccountID =: acc and IsClosed = true]){ accSize+=1; if(d.Probability==100){ accWins+=1; } } if(accSize==0||accWins==0){ accSize = 1; accWins = 1; } double valwins = 0; double valsize = 0; if(value<SOME_VALUE){ for(Opportunity a: [select Probability from Opportunity where Total_Oppty_Amount__c < SOME_VALUE and IsClosed = true]){ valsize+=1; if(a.Probability==0){ valwins+=1; } } } else if(value<SOME_VALUE&&value>=SOME_VALUE){ for(Opportunity a:[select Probability from Opportunity where Total_Oppty_Amount__c >=SOME_VALUE and Total_Oppty_Amount__c <SOME_VALUE and IsClosed =true]){ valsize+=1; if(a.Probability==100){ valwins+=1; } } } else for(Opportunity a:[select Probability from Opportunity where Total_Oppty_Amount__c >=SOME_VALUE and IsClosed = true]){ valsize+=1; if(a.Probability==100){ valwins+=1; } } if(valwins==0||valsize==0){ valwins=1; valsize=1; } double x = 100*(ownWins/ownSize)*(typeWins/typeSize)*(pipeWins/pipeSize)*(accWins/accSize)*(valwins/valsize); system.debug('X = ' +x); return x; } }
Dear Lee,
You can use Limit methods to get governers
example - myDMLLimit = Limits.getDMLStatements(); check page 413 - Force.com Apex Code Developer's Guide for details.
Thanks,
Rahul
Hi Lee,
50K is the total number of records retrieved by SOQL queries, not the number of SOQL queries.
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm
Btw, how do you call this class from your trigger?
And what's the trigger event that call this class (before/after etc)?
-Hengky-
My apologies, I wasn't completely clear. The "sum" I performed is a very generous estimate of the maximum number of records that should've been retrieved by all of the queries performed.
The trigger simply says:
*with the appropriate field names and the correct function name and all that stuff...
Hi Lee,
I see that you are calling your class inside a For loop in your trigger. And in your class, there are a few SOQL calls already. So basically you are making SOQL calls inside a loop, which is not a good idea.
I suggest that you pass the trigger.new to your class and process the list in there.
-Hengky-
Do you mean the above? I'm not sure how this could work... if I insert more than one opportunity, I need to make the comparisons between each of their different OwnerIDs and those OwnerIDs' historic opportunity wins/losses, which produces the same number of queries, surely?
Or am I missing the point?
So, I tried the code below, modified so that I think, I'm "passing" trigger.new, but I'm still over my 50000 limit. I'm completely at a loss...
Any suggestions?
Hi Lee,
You are still getting the limit exception because basically you are still calling the SOQL queries in a loop.
I would use a map to store the Size and Wins for each queries, something like this:
The above code is only for the first factor. You can use similar code for the rest of the factors.
Note: the above code is not tested.
-Hengky-
Hello again,
I don't fully follow the code, by which I mean, I don't know how I'm supposed to use the numbers stored by the map at the end. Outside each of the five loops of the type you've done, I need to do something like the above (but with all of the variables included to form my "average"), but I don't know to access the "wins" of the map. I don't understand maps all that well, can you help me fit this final piece of the puzzle?
All the best,
Lee
Hi Lee,
Could you post up your code, please?
Thanks.
-Hengky-
So my code is a few repetitions of what you posted initially for the different dependent variables I'm trying to model about. Here it is:
What I don't understand is how I actually get the probability out at the end from the map...
Lee
Apologies, your original comments are present before each new Set.
Also, the "key" that maps my probabilities to my opportunity values is a little sketchy is it not? I.e. every other value that constitutes part of a map is discrete, whereas my opportunity value is continuous, and, therefore more difficult to map. I'm thinking I could create a dummy formula field that's a string or something to replicate the effect of the bucketing I had previously employed (where I said if my value was over x, do this, if it was between x and y, do something else and so on...).
Whatever... I'm waffling. I'll figure that part out. All I really want to know is how to arithmetically use the values stored in the map.
Hi Lee,
Here is how to calculate the X at the end of your original code with Map
-Hengky-
Yes, it is possible to create a formula field to 'bucket' the value.
Below formula field returns text data type:
Then in your query you can just use it like this:
-Hengky-