February 13, 2012 at 11:19 am
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
February 13, 2012 at 11:43 am
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?
February 13, 2012 at 11:57 am
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
February 13, 2012 at 2:21 pm
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"
February 13, 2012 at 8:39 pm
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).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply