Unable to update 33k records, and system becomes unresponsive

[Shankha]

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. 

[HIROSHI SATOH]

I think you should use [updateMultiple]. Other than that...

• Index Fields: Ensure that the u_site_id field in the customer_account table is indexed. This improves the efficiency of data retrieval for your query.
• Consider Querying for Specific Fields: If you only need to update specific fields like u_pause_lifecycle and u_pause_un_pause, use ga.addQuery('fields', 'u_site_id,u_pause_lifecycle,u_pause_un_pause'); to retrieve only those fields.
• Batch Processing:
  Instead of updating all records at once, split the job into batches. This could be done by limiting the number of records retrieved and processed in each cycle, reducing the overall load.
  

var limit = 1000; // Number of records to process at a time
var offset = 0;
while (true) {
    var gr = new GlideRecord('customer_account');
    gr.addQuery("u_site_id", site_id);
    gr.addQuery("u_account_type", accounttype);
    gr.setLimit(limit);
    gr.setOffset(offset);
    gr.query();
    
    if (!gr.hasNext()) {
        break;
    }
    
    while (gr.next()) {
        gr.u_pause_lifecycle = true;
        gr.u_pause_un_pause = true;
        gr.update();
    }
    offset += limit;
}

[Shankha]

Thank you Hiroshi. I will just give it a try. I will let you know.