Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple Delete Query is Taking Huge Time Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 12:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:07 AM
Points: 31, Visits: 161
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
Posted Thursday, January 24, 2013 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1410927
Posted Thursday, January 24, 2013 12:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 13,320, Visits: 10,184
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
Posted Thursday, January 24, 2013 12:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:07 AM
Points: 31, Visits: 161
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
Posted Thursday, January 24, 2013 12:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1410935
Posted Thursday, January 24, 2013 12:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1410938
Posted Thursday, January 24, 2013 12:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:07 AM
Points: 31, Visits: 161
No.. On delete cascade option is not there with FK....
Post #1410940
Posted Thursday, January 24, 2013 12:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 13,320, Visits: 10,184
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
Posted Thursday, January 24, 2013 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1410948
Posted Thursday, January 24, 2013 12:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 13,320, Visits: 10,184
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 »

Add to briefcase

Permissions Expand / Collapse