<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
Simple Delete Query is Taking Huge Time
Simple Delete Query is Taking Huge Time
Rate Topic
Display Mode
Topic Options
Author
Message
debanjan.ray
debanjan.ray
Posted Thursday, January 24, 2013 12:03 AM
SSC Rookie
Group: General Forum Members
Last Login: Yesterday @ 1:08 AM
Points: 27,
Visits: 93
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
Post #1410924
Bhuvnesh
Bhuvnesh
Posted Thursday, January 24, 2013 12:13 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410927
Koen Verbeeck
Koen Verbeeck
Posted Thursday, January 24, 2013 12:22 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373,
Visits: 6,470
Maybe you should create a job to automatically update your statistics, since they're clearly out of date.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1410930
debanjan.ray
debanjan.ray
Posted Thursday, January 24, 2013 12:31 AM
SSC Rookie
Group: General Forum Members
Last Login: Yesterday @ 1:08 AM
Points: 27,
Visits: 93
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.
Post #1410933
Bhuvnesh
Bhuvnesh
Posted Thursday, January 24, 2013 12:34 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410935
Bhuvnesh
Bhuvnesh
Posted Thursday, January 24, 2013 12:35 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
do FK are with "
on cascade delete
" option ?
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410938
debanjan.ray
debanjan.ray
Posted Thursday, January 24, 2013 12:39 AM
SSC Rookie
Group: General Forum Members
Last Login: Yesterday @ 1:08 AM
Points: 27,
Visits: 93
No.. On delete cascade option is not there with FK....
Post #1410940
Koen Verbeeck
Koen Verbeeck
Posted Thursday, January 24, 2013 12:43 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373,
Visits: 6,470
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
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1410941
Bhuvnesh
Bhuvnesh
Posted Thursday, January 24, 2013 12:56 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
Koen Verbeeck (1/24/2013)
Do not disable foreign keys
Any other reference ?
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410948
Koen Verbeeck
Koen Verbeeck
Posted Thursday, January 24, 2013 12:58 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373,
Visits: 6,470
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
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1410954
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
