December 31, 2005 at 10:29 am
I wan't expert opinions on this particular statement.
In my database I have 10 tables. The PK in one of the tables is a FK in the rest of the tables.
The SID is a PK(primary Key)and UK(Unique Key) in the table Student
For Ex:
SELECT @rowcount = SID FROM STUDENT WHERE SID = @sid
Based on the above statement I have the following DELETE statements:
.
IF @ROWCOUNT > 0
DELETE FROM TABLE 2 WHERE SID = @sid
DELETE FROM TABLE 3 WHERE SID = @sid
.....4,5,6
What my DBA is saying is that, it is not a good practice to check if the rowcount > 0 and then execute the DELETE statement. He is saying just execute all the DELETE statements irrespective of SID exists in the othere 9 tables or not. I kind of disagree with his opinion as the compiler doesn't have to go through the DELETE statements if my @rowcount =0.
Experts please post ur comments on the above.
December 31, 2005 at 2:16 pm
There isn't much of a difference. By following what your DBA is saying, you will avoid one SQL statement that checks for the existence of a record (but that check is based on PK column so it really is not a hit at all) and by not checking for the existence of the record and executing the deletes directly against the child table(s) in proper order won't incur any additional costs either. If this code is in a stored procedure, then it will be already parsed and compiled and the conditional check won't buy you much.
However, regardless of the approach, please do make sure that the foreign keys are indexed properly otherwise those deletes statements could take a hit.
Also, have you guys evaluated using cascading DRI (declarative Referential Integrity) in your case ? You could define the constraints as "ON DELETE CASCADE" and that way, the RDBMS will take care of cleaning the child records for you. But there are some considerations before you can decide whether cascading DRI is for you or not. Read up BOL on it.
December 31, 2005 at 2:53 pm
Thanks Sharma for your expert opinion on this subject. The indexes are there on the child tables and I have not used the "ON DELETE CASCADE" auto delete on child statements. The T-sql is in sp, and coming from a OOP backround, I am kind of using the checks and balances before firing statements.
January 2, 2006 at 2:32 am
If you are deleting data from all the tables .Then probably the best way to do this would be use the Undocumented Stored procedure
declare @command1 varchar(8000)
Exec sp_MSforeachtable @command1="delete from table ?"
The above undocumented stored procedure would delete the data from all the tables recursively ..
Worth a try ...
Cheers ...
Vasanth
January 2, 2006 at 4:03 pm
>>.Then probably the best way to do this would be use the Undocumented Stored procedure
This would probably be the absolute worst way, since:
- the SQL is dynamic, therefore not pre-compiled & cached, therefore is *less* optimized and
- this executes the delete on *every* table, not just the subset of 10 in the requirements and
- has no guarantee on ordering, therefore may generate FKey errors on deleting records that are still referenced and
- since it's undocumented, there is no guarantee it will continue to function in future versions
Less optimized, wrong results, possible RI errors and not future proof. Can't think of a worse solution.
January 3, 2006 at 10:08 am
Contrary to what was stated earlier there is extra overhead to checking the existence before the delete. Your executing a statement twice. Potentially flushing things out of cache. The bigger question is why. What are you going to do with that information. If you issue the delete statement it hits the table once and only once and your done. Best case when checking is you always hit the table once and possibly multiple times, for what purpose? I agree with your dba, you should not run that check. If you need auditing to see how many rows are affected I would use @@rowcount after each delete to see how many rows were affected by the statement.
Tom
January 3, 2006 at 7:27 pm
January 3, 2006 at 10:38 pm
>>Contrary to what was stated earlier there is extra overhead to checking the existence before the delete.
Indeed there is. But also contrary to everything stated earlier, I don't think a single 1 of us has a snowball's chance in answering this because we are missing so many key relevant pieces of info. Like:
- Table DDL
- Index definitions .. and last, but by no means least
- Index data distributions and query execution plans
All we have is:
>>The indexes are there on the child tables
If 1, just 1 of the indexes on the 10 dependant tables indexes the SID column in a non-clustered index that has data distribution that is not selective enough, and if that table is large enough, then checking rowcount will win performance-wise .. because the DELETE will tablescan or indexscan and not index seek. I'd probably construct it as an IF EXISTS (...) instead of counting to a local variable, but I *would* use the IF block if I had issues with any 1 of the 10 deletes not using an index seek.
IOW, to answer the original question:
>>I wan't expert opinions on this particular statement.
Opinions like that come with a price - you need to invest the time necessary to provide sufficient info for those opinions to be relevant.
January 6, 2006 at 10:36 am
I agree with a previous post about the size of the tables. If table3 has 10 million rows, why not check for existence in the student table. Of course, the existence of student doesn't guarantee data in table 3 but the lack of a student guarantees there isn't.
I would change the if to use an exists either way though.
if exists (SELECT 1 FROM STUDENT WHERE SID = @sid)
begin
DELETE FROM TABLE 2 WHERE SID = @sid
DELETE FROM TABLE 3 WHERE SID = @sid
.....4,5,6
end
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply