Simple Delete Query is Taking Huge Time

Question

Simple Delete Query is Taking Huge Time

debanjan.ray

SSC Eights!

Points: 962
More actions
January 24, 2013 at 12:03 am

#151173

Hi All,
One of simple delete query is taking more than 10hrs to execute. There are more than 12 foreign keys dependent on that table. Query is like this,
Delete from Some_Table where table_sr_key !=-99 and sector_id=@some_code
But if we execute the same query after updating the statistics of the table then the query get executed in few minutes.
Please help me how me handle this issue so that those query will execute without any manual intervention.
Thanks in advance,
Debanjan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

debanjan.ray (1/24/2013)
One of simple delete query is taking more than 10hrs to execute. There are more than 12 foreign keys dependent on that table. Query is like this,
Delete from Some_Table where table_sr_key !=-99 and sector_id=@some_code

Two questions :

How much data it contains ?

and do FK are with "on cascade delete" option ?

Also post table defintion along with index definition

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Maybe you should create a job to automatically update your statistics, since they're clearly out of date.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

debanjan.ray SSC Eights! Points: 962 More actions · Answer 3

That table contains only 1lac rows.

Table PK:

Table_sr_key, table_eff_strt_dt

Table FK(10 Fks like same structure):

PRD_SR_KEY, EFF_STRT_DT

REFERENCES Product (PRD_SR_KEY, EFF_STRT_DT)

Table is refernced by more than 20 table.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

still you havent posted what all i asked

anyways

i will suggest you to take batch approach for "Deletion" . and i dont think "periodic statistics updation will help you much HERE "

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

do FK are with "on cascade delete" option ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

debanjan.ray SSC Eights! Points: 962 More actions · Answer 6

No.. On delete cascade option is not there with FK....

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

debanjan.ray (1/24/2013)
That table contains only 1lac rows.
Table PK:
Table_sr_key, table_eff_strt_dt
Table FK(10 Fks like same structure):
PRD_SR_KEY, EFF_STRT_DT
REFERENCES Product (PRD_SR_KEY, EFF_STRT_DT)
Table is refernced by more than 20 table.

This is primarily a English-speaking site, so 95% of the people here don't know what you mean with lac (or lakh). Also read the following article about keeping the integrity of your database when deleting rows in a table referenced by FKs:

Do not disable foreign keys

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Koen Verbeeck (1/24/2013)
Do not disable foreign keys

Any other reference ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Bhuvnesh (1/24/2013)
Koen Verbeeck (1/24/2013)
Do not disable foreign keys
Any other reference ?

Maybe. I'm sure Google can help you out 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP