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

[Mark Roethof]

Hi there,

You are applying deleteMultiple which is good, setLimit nice to test.

Question though, what kind of records are these? Should for example Business Rules / Flows / etc kick-in or not? If not, you could apply spo.setWorkflow(false). That might influence the performance you are experiencing a lot.

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list

[Ajay37]

Hi @Mark Roethof ,

Thank you. Disabling the business rules might leave any references related to this record right? Is it okay to disable?

[Mark Roethof]

That's really up to you. You didn't share much about your setup, so I don't know.

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020-2022 ServiceNow Community MVP
2020-2022 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list

[Jaspal Singh]

Deletion is something that should always be avoided but incase you still need you can run it as a Scheduled job & run it on demand or once as per your need. It will execute on backend. Also, you can try enclosing it in function

deleteme();

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

Well how long it takes may have may reasons. For instance, its relations with other tables, etc. & all those are to be taken care as well on deletion & thus would vary