As it stands currently, I have created a scheduled apex job that is supposed to run nightly and compare records being imported into a 'staging' object against records already in custom objects, and either creating or modifying these custom object records depending on what it finds in the system already. The process works as intended, and runs for any amount of records X that is passed into the method.
The problem that I am finding, is that it does not handle the sheer number of record necessary, as we would like for this class to be able to process around 1000 records nightly, but currently it can only handle around 300. I have looked through my code and optimized/bulkified it as much as possible, but when I look in the logs I am finding that huge amounts of time are being wasted running validation rules over and over on objects relative to the time spent in the code I have written, as we have several validation rules and a couple trigger calls on basically every object being touched by this process.
We have a pattern that I see repeating in the logs several time when using the Database.insert function that essentially - inserts 100 records, run a custom object validation rule over all records, fire a workflow over all 100 records, fire account validation rules over 100 records, fire account trigger over 100 records.
This then repeats until all of the custom object records are created/updated.
This takes the bulk of my Apex CPU time limit to the point that it times out for any number of records significantly higher than 300, which is quite obviously a far cry from 1,000.
Someone suggested that I look into the Bulk API, but having never used it before or had anyone knowledgeable to introduce me to it, I am hesitant to get too far into redesigning it for the bulk api until I know that it will both solve the problem I am currently having and not run into any others.
In the Salesforce documentation, it says that the bulk api 'provides a programmatic option to quickly load your org’s data into Salesforce.'. However, since the process I have designed is far more complicated than simply inserting data and needs to compare a record against the entire system's records at a time, it is not clear to me if this would be a good use case for the bulk api. I am under the impression that the bulk api is more meant for inserting or updating static records of absurdly high volumes, and it would be difficult or even impossible to leverage it to run through complicated logic of comparing and taking specific action based on differences between records.
Given that my problem is CPU Apex time caused by the insertion and not the size or number of insertions themselves, does this seem like a good use case for the bulk api? If not, does anyone have any suggestions as to how we may get around this CPU time issue in order to be able to process that number of records a day??
I am happy to provide some or all of the code if requested, but it is quite a large and complex code base, so I felt it would be easier to describe the problem and see if I could get an answer just from that. If code would help to answer, please let me know and I will provide. Otherwise if there is anything that needs clarified please let me know, I realize this is a bit long winded and hard to follow, so happy to help clear things up if I can.