System becomes unresponsive while updating 33k records.

[Shankha B]

Hi,

I have a UI action called "Pause Site ID". Activating this UI action triggers a script include containing the necessary code. This code retrieves the specified Site ID and locates all associated accounts. It then updates certain fields for each account sequentially. In instances where a Site ID is linked to approximately 33,000 records, the update process takes about 8 minutes, causing the system to become unresponsive during this time.

Sample code: 

 var site_id = this.getParameter('sysparm_site_id');

        var accounttype = this.getParameter('sysparm_account_type');

        var ga = new GlideRecord('customer_account');

        ga.addQuery("u_site_id", site_id);

        ga.addQuery("u_account_type", accounttype);

       ga.query();



        if (ga.getRowCount() != 0) {

             gs.addInfoMessage("Account pause in progress.");



             while (ga.next()) {

                 ga.u_pause_lifecycle = true;

                 ga.u_pause_un_pause = true;

                 ga.update();

             }

         } else if (ga.getRowCount() == 0) {

             return "Empty";

         }

    

I have used updateMultiple() also. But still, it is taking that much of time. Could you please let me know is there a way to update it faster?

Thanks in advance.

[Robbie]

Hi @Shankha B ,

A couple of things to consider when bulk updating a large data-set.

First and foremost, where possible updates should be asynchronous - as in operating independently and separately of other processes.

The data set should be split into exclusive, non-overlapping data sets where possible (analyze the data to see where it can be logically split, this can include sys_updated_on or sys_created_on and leveraging an orderBy for example).

These batches should then be triggered via a Scheduled Job and ideally outside of core business hours. This will ensure the update is triggered in the background

(If a scheduled job and outside business hours does not work, and perhaps this is a one off, you could consider 'Background scripts' in multiple sessions).

As an FYI - the updateMultiple() is more efficient as a comparison.

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Kudos.


Thanks, Robbie

[Abhijeet_Pawar]

Hello @Shankha B  ,

I encountered a similar issue while dealing with a large number of records, and I developed an approach in my project to address it.

You can perform the Updation operation within a scheduled job, trigger the scheduled job from a Script Include, and then call that Script Include using GlideAjax in your UI action. I hope this provides some clarity.

Additionally, here is the code to execute the scheduled job from script include. Please let me know if you need further clarification.

Thank you!

 var rec = new GlideRecord('sysauto_script');
 rec.get('sys_id', 'fff61e5d1be5b190a72cfdd2cd4bcb37');//pass sys_id of scheduled which will perform updation operation
  SncTriggerSynchronizer.executeNow(rec);

[Shankha B]

Hi  Abhijeet Pawar,  

Thanks for your response. I will try it. Also I have observed one thing. In our script include, multiple functions are defined which runs on particular conditions. Those conditions are defined in the UI action script. Is it possible to pass the parameters in the scheduled job so that the scheduled job figures out what functions it needs to run for which condition.

[Abhijeet_Pawar]

Hello @Shankha B  ,

It seems that passing parameters directly in a scheduled job might not be feasible. However, one alternative is to create multiple scheduled jobs.

In this approach, if a specific condition in the Script Include is met, you can trigger the corresponding scheduled job. While this method isn't ideal, it could still be a workable solution if needed.

Please let me know if you need further clarification.

Thank you!