What is the fastest way to delete 1000000 records in cmdb (incl cascade deletes) ?

[tommyjensen]

However some records should NOT be deleted?

Using a background script and deleting with deleteMultiple() runs 20 minutes to delete 100 records! It must be possible to do that faster even with cascade deletes.

[sergiu_panaite]

Yes, setWorkflow(false) is ignored in referenced tables, but will be applied on the starting table (cmdb_ci table in this case). If we want to bypass business rules, that's what setWorkflow will do. If not, then we can just remove the setWorkflow.

And, a scheduled script execution is indeed a better idea than background scripts.

Regards,

Sergiu

[uluerje]

I just run the following sql statement in the background script when I need to do a mass delete.   It's a little risky, but it's quick and easy.

gs.sql("delete from TABLE where FIELD='VALUE'");

[tony_barratt]

Hi Jessica,

In general gs.sql is not recommended - you do mention I know "it is a little risky"

There is an interesting explanation of why not to use gs.sql here:

Why using gs.sql is a BAD IDEA!

For example using gs.sql can   result in orphan ("ghost") records in the database.

Best Regards

Tony

[tommyjensen]

Jessica that would not delete related records defined as cascade deletes.

[bernyalvarado]

Hi Tommy,

My recommendation will be to do it from a on demand Scheduled Job. In my experience it has been a far more better approach than doing it through a background script since the background script is dependent of the session alive running the script while the scheduled job is not.

I would just make sure through a background script that your delete script works for one record and then would do it through the rest using the scheduled job. You can validate the rows been deleted as it occurs without impacting at all the script running in scheduled job.

Thanks,

Berny