search

ironmansoftware / issues

Public Issue tracker for Ironman Software products.
https://ironmansoftware.com
31 stars 2 forks source link

SQL - Slow Delete of Jobs in groom jobs #3414

Closed adamdriscoll closed 2 weeks ago

adamdriscoll commented 2 weeks ago

Version

4.3.0

Severity

Low

Environment

msi

Steps to Reproduce

I had a jobs table with 250 records in it and ran the server-wide groom command manually. It took 8 seconds to complete. This was causing locks on the table that was causing jobs to fail to run. 

DELETE FROM Job WHERE CreatedTime <  DATEADD(day, -30, GETDATE())

Expected behavior

Fast server wide clean up.

Actual behavior

Slow server wide clean up.

Additional Environment data

No response

Screenshots/Animations

No response

adamdriscoll commented 2 weeks ago

The problem is the size of the JobOutput table. Since changing to a row per output message, the table causes jobs to delete very slowly because of all the cascading deletes. We've implemented nibbling deletes on the jobs table to account for this. This will delete jobs in chunks and allow other queries to process in between the chunks.