How to do Mass deletion of records?

[Ajay37]

Hi,

I need to delete 69 lakhs records from a table and I am looking for better way to do it without any performance impact on the instance. I can't go with Delete all records UI action, as I need to delete records based on certain condition. I have ran a sample script to test how much time it is taking by setting limit to 5. But strangely, even for 5 records it took 30 seconds time to delete.

var spo = new GlideRecord('table_name');
spo.addEncodedQuery('name=NULL');
spo.orderByDesc('sys_created_on');
spo.setLimit(5);
spo.query();
spo.deleteMultiple();

Thanks

[Ajay37]

Hi @Jaspal Singh ,

Thank you. As you said, I will choose scheduled job. But as I have around 69lakhs records what could be the best way to schedule a job instead of triggering it onDemand. If I choose it to trigger for every hour by giving 50000 as limit, if the deletion of 50000 records doesn't get completed in an hour, will the next run triggers as we have scheduled it to run for every hour? Does it make effect on any other jobs that run?

Thanks

[Jaspal Singh]

But as I have around 69lakhs records what could be the best way to schedule a job instead of triggering it onDemand.

Should be fine.

If I choose it to trigger for every hour by giving 50000 as limit, if the deletion of 50000 records doesn't get completed in an hour, will the next run triggers as we have scheduled it to run for every hour? Does it make effect on any other jobs that run?

Scheduled job is more of async so should not be an issue as it will be dependant on job & its scheduled run time. 

[Ankur Bawiskar]

Hi,

you can create multiple fix scripts and make them run in background and use setLimit(50000) for example

But there might be cascading effect which possibly is causing more time for deletion

Refer this link as well

Mass-Deletion and Excess Data Management Recommendations

Regards
Ankur