How to delete more than 13 thousand records from a table at once?

[Ashirav]

I tried that "System Nav-->Tables & Columns-->Delete all records--> Type Delete" thing, but that didn't work. 

When I select the records to delete, they take forever to show the popup that asks Whether or not to delete:-

Surely there must be another method, something that will not require me to stay awake the whole night to delete these records manually?

The table is the "core_company" table.

[Edit after 2 replies: ] The conditions are "Name starts with xyz" and "Created by abc". Please help me in scripting also if some script is required.

Thanks!

[balu3 · Accepted solution]

Hi Ashi,

Just like what Alberto said. You can do that.

Else just go to the list and do the following as in the image.

1) Enter the query you want in the filter condition.

2) Run the query

3) Right click on the query string and click copy query.

4) Put that in gr.addEncodedQuery('<paste_here>');

5) Run the script

Some points to be noted when deleting more than 1000 records

1) It might take like more than an hour or 2 in normal cases.

2) It might take more than 10 hours in special cases. Special cases means that there are many operations done along with this. Like say running Business Rules.

  a) It can occur because of multiple operations done as part of Business Rules

  b) Because of cascaded deletes

  c) Because of poorly structured Business Rules and logic.

  d) Because of multiple indexes regeneration simultaneously (although I'm not sure of this, I just heard this one).

You can use gr.setWorkflow(false); before the delete statement to avoid some of them. Although this should be used if and only if you do not want other condition to run as part of your delete statement.

I'd recommend you to split the delete operation into batches of 10 or so.

Like,

var gr = new GlideRecord('core_company');

gr.addEncodedQuery('nameSTARTSWITHxyz^sys_created_bySTARTSWITHabc');

gr.setLimit(1000);

gr.deleteMultiple();

Run this code multiple times until there are no records left.

Also, please keep in mind that running a scheduled script or background script in a production instance requires your client's approval (as per most of the client's policies I'm aware of). Please check that once as well.

Thanks

Balu

[Ashirav]

Thank You, Balu for your help! 🙂

[dvp]

Try the below script

var gr = new GlideRecord("core_company");
gr.addEncodedQuery("nameSTARTSWITHxyz^sys_created_by=abc"); // Replaces xyz and abc values
gr.deleteMultiple();

[Ashirav]

Thank You!