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.
| 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 |
|
|
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/
|
|
|
|
|
|
|
|
|
Delete overhead - takes to much to delete 10 rows