Part 1 : Too many soql in Real Life
I have encountered a lot of question about Too Many SOQL limit in Salesforce before in different companies. Honestly I don't feel it is fun work however while working on them I gain a lot of knowledge depending on how I plan to fix it and increase some confidence level because , hey you read trigger and code line by line, review any component such as Process Builder, Flow. Looking closely to any possible holes and understand it and try to figure out the most effective way to deliver eat.
So at last, I writing this entry as I got so many misunderstood about Too many soql limit. Some thought it is so complex and the rest of them thought it's so simple until it being treated as miscellanous.
This is how we started...
imagine that you travel to somewhere, maybe far away to the mountain with very great car that have spacious space (uhmm of course the car space have limit, spacious does not mean you can put everything inside.
You need to put your item in your car. So you put maybe tent, maybe stove, maybe charcoal if you planning to campaign or maybe anything. Now this is WHERE the risk begin. It could be you will decide as long it fit, I will put anything that I want to bring to the mountain. Oh, maybe pyjama. Imagine sleeping in the tent at the top of mountain wearing nice pyjama. SOOOO Instagrammable. After all it fit into our car space. Just throw into it, nobody care - it works after all.
Then suddenly you have an important friend that would like to join. She will join you later. She will be a very helpful friend especially when we reach the mountain. However, when we meet her we have not much car space. At this time really no space until one of her important item , well so important to us not fit. In this case, imagine that Process Builder come into to perform some action after trigger. It hit into error.
Should we blame her - not to bring item that only fit into our car? Why not, after all we hit into not enough space problem when she join. The space is suffient for us all this time and not more.
In this case, developer tend to fix process builder or any component that hit the limit after referring to Debug Log.
Ok, fine. Our car space not enough but we still need our friend to join with her important item. No choice we need to figure out how to add more space. Should we just buy some rooftop carrier or maybe we can tie the item at the car top. We need to enable her to join regardless how ugly and heavy our car look like. We may not able to speed up to the mountain. We need to reach the mountain on time, some one keep checking us whether we reach there or yet.
In this case, imagine that you start fixing the code by putting all the by pass, controlling which soql and everything. Somehow to many bypass does not mean clearing the space. The soql still added more, it just some additional space to enable it to include more SOQL.
Suddenly we got call from our relative that stay nearby the mountain. He tell us to buy some generator, other important items.The reason is no shop near the mountain. Anything could happen and we need to be prepared. Ahh.. we no longer able to depend on by pass. It will screw up our car.
So we have an idea. The good one. As someone else to bring other item to us. So we call our friend, asking whether it is okay to help us bring some item with their car. Of course we have many friends but still not all available. These friends are free to take their own time, maybe they can stop somewhere else first before getting onto the mountain. Yes, you can call your friend here @future Queueable or basically asynchronous process. It deliver something but when it feel free.
Then we continue the journey, during the journey we face a lot of difficulties especially with space. We start to throw some item. Unfortunately some is very nice item. We start blaming our car because the space is so small. Worst case, if there some item that very important that we need to bring we need to rethink and redesign everything. There a lot of throwing some item, wasting resources until then even our car not working. When we reach the mountain, we cannot right away settle down as we need to wait our friend to reach there.
...that's how the story will end for this time being.
Imagine the journey, the item is user requirement or let say projects. Sometime we can say no, sometime we have to bring it and the item usually be added more and more during the journey.
How it will end differently if we start deciding to rearrange our item, make it compact at the early stage. Of couse, there some cases that we still running out space regardless how we tidy up the item from the start BUT it won't be the same. Deciding to buy a truck after utilize all the car space in a very organized way is much better rather than having a truck with not enough space issue due to unorganize habit.