Bad performance on deleting referenced database rows with Linq To SQL

Question

Hey,

I've got an performance issue in my application using a MSSQL DB. I have a Database Architecture with the following tables:

• Job (Primary Key: Job ID)
• Jobitem(Primary Key: Jobitem ID, Foreign Key: Job ID)
• Job2Property (Foreign Key: Job ID <-> Property ID)
• Jobitem2Property(Foreign Key: Jobitem ID <-> Property ID)
• Property (Primary Key: Property ID)

Now when I want to delete a Job, I first have to delete its Jobitems, then the Job2Property references and last I can delete the Properties and the Job itself. For the Jobitems, I need to delete the Jobitem2Property references first as well, followed by its Properties and the Jobitem itself.

This is the method to delete the jobs:

public void Delete(List<Job> jobsToDelete)
{
    var jobitemsToDelete = new List<Jobitem>();

    // Gather references to jobitems to delete
    foreach (Job job in jobsToDelete) {
     List<Jobitem> jobitems =
      (from ji in job.Jobitems.Values
       select ji).ToList();

     jobitemsToDelete.AddRange(jobitems);
    }

    // first the related jobitems and the properties have to be deleted
    PropertyManager.Instance.Delete(jobsToDelete);        
    Delete(jobitemsToDelete);

    dbc.Job.DeleteAllOnSubmit(jobsToDelete);
    dbc.SubmitChanges();

    // Remove Job from dictionary to avoid using Refresh()
    foreach (Job job in jobsToDelete) {
     Jobs.Remove(job.Id);
    }
}

The following method deletes the jobitems:

public void Delete(List<Jobitem> jobitemsToDelete)
{
    PropertyManager.Instance.Delete(jobitemsToDelete);

    dbc.Jobitem.DeleteAllOnSubmit(jobitemsToDelete);
    dbc.SubmitChanges();
}

And finally the method to delete the Properties:

public void Delete(List<Job> jobsToDelete)
{
    List<Property> propsToDelete = new List<Property>();

    foreach (Job job in jobsToDelete) {
     if (JobProperties.ContainsKey(job.Id))
      propsToDelete.AddRange(JobProperties[job.Id]);
    }

    List<Job2Property> j2pToDelete =
     (from p in propsToDelete
      select p.Job2Property.First()).ToList();

    dbc.Job2Property.DeleteAllOnSubmit(j2pToDelete);
    dbc.Property.DeleteAllOnSubmit(propsToDelete);
    dbc.SubmitChanges();
}

The method to delete jobitem properties looks exactly the same with the exception that it takes List<Jobitem> as parameter.

Now the performance of deletion is really bad. For a single Job, it takes around 3 seconds. When I delete 10 rows, it takes 13 to 14 seconds. Before I had the Property and XYZ2Property tables, it would take less than a second.

How can I optimize the speed of this? Profiling the application with ProfileSharp I came to the conclusion that the delete function eats about 17% of the total processing time of the application (that's 9405781250 units of time), whereas the call to dbc.SubmitChanges() in the last method to delete properties seems to take most of this time (it states 1475%, but that seems to be a bug of ProfileSharp. Rather, it eats up most of this 17% I guess).

Can I do something about this?

[EDIT] Here's a picture of the profile.

Answer 1

What database is this? Anyway, I wouldn't have the responsibility of deleting all those jobs, jobitems, and jobproperties and what not in the code, but in the database instead. For foreign keys, you can specify a cascading delete, which means that if you delete a certain job, all corresponding jobitems and properties are also deleted. It saves you a lot of code, and it is less error prone. Look it up. In SQL Server Management Studio it is right under the Foreign Key Relationships editor.

It's probably going to be much faster as well, since you don't have to do select queries to retrieve all the corresponding jobitems and properties. So in short, you'd get less code, less bugs, and improved performance.