Extreme deletion time

[bonsai]

※This is the Paris version.

There are 7 million records in the table.
Running the following script will take 8 hours to delete all records.

var delrec = new GlideRecord(table_name); 

var a=[];
a[0] ="aaaa";

delrec.addQuery("Reference_field_name.Referenced_string_field_name",
"STARTSWITH",
a[0].toString().substr(0, 2)); //Record starting with "aa" in the referenced string field name


delrec.addQuery("sys_created_on", ">=", "2020-10-01 00:00:00");


delrec.query();

delrec.deleteMultiple();

However, if you execute the script with one condition deleted in the table with the same condition as shown below, all records will be deleted in 4 minutes.

var delrec = new GlideRecord(table_name); 


delrec.addQuery("sys_created_on", ">=", "2020-10-01 00:00:00");


delrec.query();

delrec.deleteMultiple();

Will the processing speed be so slow if the condition "starts with" is set for the reference table?

delrec.addQuery("Reference_field_name.Referenced_string_field_name",
"STARTSWITH",
a[0].toString().substr(0, 2)); //Record starting with "aa" in the referenced string field name

[Maik Skoddow]

Hi

yes, that's right, because for each dot-walk criteria in the background, a table join is performed. And a join of a table with millions of records with another table is really expensive and time-consuming.

Kind regards
Maik

[bonsai]

thank you for your answer. Is there a way to implement it without using dotwalk? I want to reduce the deletion time.

[Maik Skoddow · Accepted solution]

Hi

this question is difficult to answer as I don't know why you have to dot-walk to another table for deletion purposes. And it also depends on how often you have to delete a table with millions of records. If execution time really plays a business-critical role then my solution would be to add a boolean custom field "can_delete" to your table which holds the result of the calculation 

"Reference_field_name.Referenced_string_field_name", "STARTSWITH", a[0].toString().substr(0, 2)

But how and when that calculation has to be performed again depends on you scenario I don't know. And for speeding up, I also would index that new column.

Kind regards
Maik

[bonsai]

thank you for your answer.
The reason for using dot-walk is that I want to specify the character string displayed in the reference setting field as the filter condition.

I thought it was a good idea to create a new field.
thank you.