Pipeline Design for Bulk Record Updates

Question

Hi all. I have a timecards table where users track all the things they're working on. Relatedly, we have a separate child timecard table, with a narrower field list but otherwise the exact same records. This was done a while ago to reduce volume on long term storage of these records because we hit the table limit of 500 MB. So anytime a timecard record gets created or edited in the parent table, there's a pipeline that checks to see if the record has any related child timecard records. If not, it creates a new child timecard record with all the relevant fields populated. If there is already a child timecard record, it updates that child record with all the newest field values. Then as the last step (only for new timecards), updates the newly created child timecard record with the Related Parent Timecard value so it's easy to verify all timecards have exactly 1 child record. Other than being resource-intensive, this pipeline works perfectly fine most of the time.

However, twice/month, we export all our timecards to another system outside Quickbase, which is tracked with a date and checkbox field being updated via Find & Replace in a report to prove they were exported. The problem is, there are often around 5,000 records being updated simultaneously. When that update occurs, with so many timecards being updated all at once, a queue builds up that can take hours for the pipeline to process and update all of the underlying child records. Beyond general performance issues, users are often still creating new timecards, which don't show up for a while in the child timecard table because those new timecards are now in the back of the line to be created/updated.

I want to redesign the pipeline using the On New Bulk Event step, so these records can be processed, created/edited all at once so a queue doesn't build up and take hours to finish running and cause confusion amongst end users. What's the best way to go about that? I tried replacing the trigger from an On New Event to an On New Bulk Event, while leaving everything else about the pipeline the same, but if anything, it's taking even longer than before.

markshnier__you · Answer

The purpose of the On New Bulk event is to ensure that records are processed one by one and don't overwhelm the system and also in use cases where you really want the first record processed before you start processing the second. A small example of that would be where child records are being created in bulk and sometimes they need a corresponding parent record created. If you do not use Bulk Trigger you will get multiple pipelines running simultaneously and both trying to create the same unique parent record. One will fail and generate errors.

But you are right, in terms of performance, Bulk Triggers is going to make the process run slower.

If this is important to you and you can scrounge up some budget for outside consulting, I'm pretty sure that Sharon Faust (Quickbase Junkie) can help you.

A while back Sharon did a Qrew Demo where she used advanced techniques in Pipelines with Jinja which allowed her to achieve a 10x or maybe 100x speed up in processing speed.

harrisonsmith · Answer

interesting, thanks Mark. Sounds like I misunderstood the purpose of the bulk event step. So for updating thousands of records all at once, it's still easiest/most performative to just have the pipeline loop through all the records one at a time (outside of leveraging Sharon's self-created methodology)?

markshnier__you · Answer

.... Yes, aside from Sharon, you should be using the Bulk Step and maybe you don't really care if it takes hours and hours to run because your app will still "Performative" (running nicely) for your regular users.

A very nice design feature of the Bulk Trigger and the Regular Trigger is you can set the number of records for it to trigger on.

So for example, you can set the Regular one to trigger when a maximum of 1 record is updated or created, and that will ensure that your manual entry users still get instant processing. And then you set the Bulk Trigger one to to trigger at 2 or more records being changed.

When I went looking for that setting on the regular trigger for On New Event, in an older Pipeline, the setting at the bottom of the box for the maximum number of records to trigger on, was not there. I was initially confused so I started to build a brand new pipeline and that same On New Event trigger did have the new setting at the bottom of the box. So you may have to go back to your existing regular On New Event trigger and replace it the same so that it will have that new feature.

quickbasejunkie · Answer

HarrisonSmith​ This is the demo Mark was referring to during Empower Pro https://www.quickbasejunkie.com/blog/empowerpro24 it would be Step C: Make Request - using the API_ImportFromCSV API that would be the most interest to you. Only in place of referencing the json output you would be referencing the step A bulk trigger (where you deleted the loop). Such as{%- for i in a %}and{{i.id}},{{i.start_time}}...Quickbase is working on a "Bulk Import" step to go with the "Bulk Trigger". The beta for it has just started. So if this isn't urgent it may be worth waiting for it to be released.-Sharon

markshnier__you · Answer

Just one more thought about the bulk trigger pipeline. If you want a compromise between the regular on new event, which floods your App with 5,000 requests at the same time and the Bulk Trigger which processes one by one, you could speed up the Bulk trigger by a factor of two by making a field which determines if the Record ID is even or odd.Then you could duplicate the Bulk Trigger pipeline and had one of them do the evens and one of them do the odds. The effect of that is you would have the records being processed twice as fast through the Bulk Trigger Pipelines, but that still may be slow enough to have no impact on the performance of your app for the regular users.&nbsp;&nbsp;

Forum Discussion

Pipeline Design for Bulk Record Updates

6 Replies

Related Content

Qrew Tip: Pipeline Null Values

QrewTip: AI Actions Pipelines Channel

Pipeline Not Updating

Pipeline Help

Automatically Update Date Field