Delete overhead - takes to much to delete 10 rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete overhead - takes to much to delete 10 rows

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-25 : 19:14:54

Good afternnon

i need your help wit this:

for this delete it takes an hour and a half to delete just 10 rows in average

delete
from DIM_ACA_ATENCION
WHERE COD_DIM_ATENCION >= '70'
AND COD_DIM_ATENCION <= '80'

while i run the execution plan for this query not only it shows the execution plan for this table that is an index seek (cost 14%) BUT shows me the execution plan for the table hechos_incioencias (Table scan 41%) because this table has no indexes (i think this is the bottleneck)

the table DIM_ACA_ATENCION is being referenced by HECHOS_INCIDENCIAS because a constrain

sp_fkeys dim_aca_atencion it shows me a row:

PKTABLE_QUALIFIER|PKTABLE_OWNER|PKTABLE_NAME|PKCOLUMN_NAME|FKTABLE_QUALIFIER|FKTABLE_OWNER|FKTABLE_NAME|FKCOLUMN_NAME|KEY_SEQ|UPDATE_RULE|DELETE_RULE|FK_NAME|PK_NAME|DEFERRABILITY
dm1_elite|dbo|DIM_ACA_ATENCION|COD_DIM_ATENCION|dm1_elite|dbo|HECHOS_INCIDENCIAS|COD_DIM_ATENCION|1|1|1|FK_HECHOS_INCIDENCIAS_DIM_ACA_ATENCION|PK_DIM_ACA_ATENCION|7

what i see the overheat is the lack of an index for one table Ive got 2 questions:

1. Dropping the constrain could corrupt data ? (is the right solution ?)

2. the second choice is create indexes:

sp_helpindex dim_aca_atencion

IX_DIM_ACA_ATENCION_NUM_ATENCION_FUENTE nonclustered located on INDICES FUENTE, NUM_ATENCION
PK_DIM_ACA_ATENCION nonclustered, unique, primary key located on INDICES COD_DIM_ATENCION

sp_helpindex hechos_incidencias
The object does not have any indexes.

could i create indexes on both tables by the column fec_historico which is in both tables, or create just an index in the table hechos_incidencias by the colum com_dim_aca_atencion ?

which of the 2 choices is the right one ?

thanks for your help

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-25 : 21:07:58

Seems you have cascading deletes.

You'd do better to delete from the child tables first, then the parent table.

Cascading FKs are a serious design flaw. I don't permit them in my databases. They produce difficult to track down bugs, cause unexpected data loss, and lead to issues like you're experiencing now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 22:19:55

you can use below logic to create recursive delete queries based on fk relationship

http://visakhm.blogspot.com/2011/11/recursive-delete-from-parent-child.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 01:42:21

If you start a transaction, drop the fk constraints, do the deletes and then recreate the fk constraints and then either commit or rollback the transaction, you will be fine data quality wise.
And it will probably run much faster too.

N 56°04'39.26"
E 12°55'05.63"

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-26 : 12:19:31

OK Thanks, in order to use the code for finding out recursively the object relationships and then delete from tables the dependent
records, to be honest, i have no idea and i dont undesrtand the logic, but i know it it has something to do with
Common table expresion in which ive got to learn intensely by myself
however, ive got 2 questions:

1. within the code with parameters should i replace to relate the objects.
;with object_cte(tblid,tblname,rtblid,rtblname,level)

2. within the code i din't see the sentence to do the delete

For the third advise what do you mean with a start a transacation and is strongly necessary ?

2 drop the fk constraints, do the deletes and then recreate the fk constraints and then either commit or rollback the transaction, you will be fine data quality wise.
And it will probably run much faster too.

could be

ALTER TABLE hechos_incidencias
DROP CONSTRAINT FK_t1id

ALTER TABLE hechos_incidencias
ADD CONSTRAINT FK_t1id FOREIGN KEY (t1id) REFERENCES t1 (t1id) ON DELETE
CASCADE

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 12:58:21

quote:
Originally posted by alejo46

OK Thanks, in order to use the code for finding out recursively the object relationships and then delete from tables the dependent
records, to be honest, i have no idea and i dont undesrtand the logic, but i know it it has something to do with
Common table expresion in which ive got to learn intensely by myself
however, ive got 2 questions:

1. within the code with parameters should i replace to relate the objects.
;with object_cte(tblid,tblname,rtblid,rtblname,level)
you dont have to replace anything.they're fields within CTE
2. within the code i din't see the sentence to do the delete
the given stub contains logic to return objects in correct order. you need to add DELETE table part in last select and generate the delete script using object names
For the third advise what do you mean with a start a transacation and is strongly necessary ?

2 drop the fk constraints, do the deletes and then recreate the fk constraints and then either commit or rollback the transaction, you will be fine data quality wise.
And it will probably run much faster too.

could be

ALTER TABLE hechos_incidencias
DROP CONSTRAINT FK_t1id

ALTER TABLE hechos_incidencias
ADD CONSTRAINT FK_t1id FOREIGN KEY (t1id) REFERENCES t1 (t1id) ON DELETE
CASCADE

Thanks in advance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources