Cascade delete taking lot of time

  • tripri

    SSCommitted

    Points: 1584

    Hi,

    I got a delete statement which performs cascade delete to some 20 sub tables.

    I got necessary indexes on all the tables.

    Problem is i got around 300K records in my parent table, so when that delete statement starts running it goes fine with avergade duration of 0ms with index seek on all the tables. After reach to 20k or more the average duration is going up to 450 ms and some of the child tables are having index scans which used to have index seek before and it is slowing down the load process completely.

    Can some one suggest me any thing that i can do to fatsen that delete. I used forceseek option too but it is getting applied to just the parent table(which is supposed to be).

    Thank you

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    Delete fewer records at a time. You might be experiencing some delays from logging.

    When you say you have the necessary indexes, that doesn't really explain things. Can you give some examples of tables and the indexes? I assume you mean FKs, but can you let us know.

    Are you deleting from parent to child to grandchild or parent to child in 20 tables?

    Where do you get the average duration from?

  • tripri

    SSCommitted

    Points: 1584

    @Jones,

    I'm deleting from parent->Child->GrandChild

    And i'm looking at Recent expensive Queries in activity monitor to get the averageduration.

    My Table Structure is like

    Student Table in which i have student id as the primary key

    StudentInfo table in which i use the StudentId as the primary key and foreignkey

    so if i delete a student record i will be deleting the records of that student in studentinfo table.

    Sorry if this is not clear

  • SQLFanatic

    SSCommitted

    Points: 1640

    I would diable indexes before performing a huge delete and also consider batch deletes as Steve Jones mentioned above to avoid logging problems.

    🙂

    ________________________________________________________________
    "The greatest ignorance is being proud of your learning"

  • Paul White

    SSC Guru

    Points: 150442

    tripri (2/13/2012)


    After reach to 20k or more the average duration is going up to 450 ms and some of the child tables are having index scans which used to have index seek before and it is slowing down the load process completely.

    Please attach execution plans for the two cases (seek and scan).

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply