November 16, 2018 at 6:15 am
John Mitchell-245523 - Friday, November 16, 2018 5:40 AMUnderstood, but if it were me I'd rather prevent any anomalies than try to capture and correct them after the fact. In the worst case, there could be hundreds or even millions of them. Do you have reason to believe that their existence has a significant effect on the performance of the deletes?John
Hi John, no I don't have any concrete evidence that they would be detrimental and am now at the stage of experimenting with different options and this is one of them. I was trying to ascertain if the cost of dropping and recreating the FK's was better than either risking a constraint violation and my whole batch failing after having been running for a while OR if there was any significant overhead with wrapping each statement in it's own TRY/CATCH to ensure that processing continued if there was a failure and revisit the failure later OR if I should be using transactions. All very much trial and error really based upon the constraints that I have to work within.
November 16, 2018 at 6:16 am
Jonathan AC Roberts - Friday, November 16, 2018 6:14 AMscout7-500377 - Friday, November 16, 2018 6:08 AMJonathan AC Roberts - Friday, November 16, 2018 5:47 AMscout7-500377 - Friday, November 16, 2018 4:55 AMJonathan AC Roberts - Friday, November 16, 2018 4:31 AMIf you are deleting from a table based on a query joining several tables then some if the indexes will help performance. So you shouldn't be disabling all non-clustered indexes.Thank you for your reply but the delete statements have no joins in them at all. They are generated from a script that has the required joins and then this query just spits out the resulting delete statement based upon the primary key fields required. Admittedly disabling the indexes was just something I tried thinking it might speed things up if it was not necessary to handle indexes after a delete. I think I may have misunderstood how deletes affect indexes..
In your original post you say are deleting with statements like this:
DELETE FROM [TARGET TABLE}
FROM [TARGET TABLE}
INNER JOIN {JOIN 1} ON {JOIN 2}.{COL}={JOIN 1}.{COL}
INNER JOIN {JOIN 3} ON {JOIN 2}.{COL}={JOIN 3}.{COL}
WHERE [dbo].[tbl_VPOS_Locations].LocationID NOT IN (0, 199, 200, 346, 370)Now you say "the delete statements have no joins in them at all"
So how are you deleting from the tables? Do you store the keys in a temporary table? I don't understand how you are deleting the rows.Because if you read my update from earlier today you will see that I changed my approach
" I have now tried a slightly different approach. I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
"I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
So you are generating a delete statement for every row you are deleting??
Instead, just inset the primary key values that you need to delete into temporary tables (one temporary table for each table you want to delete from) and then delete from each table with a join to its related temporary table.
Now that is a very good idea that I am disappointed that I never thought of, can't see the wood for the trees sometimes! Many thanks. I will give that a try! 🙂
November 16, 2018 at 6:34 am
scout7-500377 - Friday, November 16, 2018 6:16 AMJonathan AC Roberts - Friday, November 16, 2018 6:14 AMscout7-500377 - Friday, November 16, 2018 6:08 AMJonathan AC Roberts - Friday, November 16, 2018 5:47 AMscout7-500377 - Friday, November 16, 2018 4:55 AMJonathan AC Roberts - Friday, November 16, 2018 4:31 AMIf you are deleting from a table based on a query joining several tables then some if the indexes will help performance. So you shouldn't be disabling all non-clustered indexes.Thank you for your reply but the delete statements have no joins in them at all. They are generated from a script that has the required joins and then this query just spits out the resulting delete statement based upon the primary key fields required. Admittedly disabling the indexes was just something I tried thinking it might speed things up if it was not necessary to handle indexes after a delete. I think I may have misunderstood how deletes affect indexes..
In your original post you say are deleting with statements like this:
DELETE FROM [TARGET TABLE}
FROM [TARGET TABLE}
INNER JOIN {JOIN 1} ON {JOIN 2}.{COL}={JOIN 1}.{COL}
INNER JOIN {JOIN 3} ON {JOIN 2}.{COL}={JOIN 3}.{COL}
WHERE [dbo].[tbl_VPOS_Locations].LocationID NOT IN (0, 199, 200, 346, 370)Now you say "the delete statements have no joins in them at all"
So how are you deleting from the tables? Do you store the keys in a temporary table? I don't understand how you are deleting the rows.Because if you read my update from earlier today you will see that I changed my approach
" I have now tried a slightly different approach. I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
"I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
So you are generating a delete statement for every row you are deleting??
Instead, just inset the primary key values that you need to delete into temporary tables (one temporary table for each table you want to delete from) and then delete from each table with a join to its related temporary table.Now that is a very good idea that I am disappointed that I never thought of, can't see the wood for the trees sometimes! Many thanks. I will give that a try! 🙂
I've got a script you might find useful. Instead of "DROPPED ALL Foreign Key Constraints FOR ALL tables BEFORE running the scripts.". you can disable the foreign keys of all tables that have FKs to the table you are deleting from. Then after deletion rin the script again to enable the FKs. So before and after each delete you will need a script like this. All you need to do is edit the @Tablename values and @Disable bit.
DECLARE @Tablename sysname = 'myTableIWantToDeleteFrom'
DECLARE @Disable bit = 1 -- Set to 1 to disable 0 to enable
DECLARE @fkQuery VARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT 'ALTER TABLE [dbo].' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' ' + IIF(@Disable=1,'NOCHECK','CHECK') + ' CONSTRAINT ' + QUOTENAME(fk.NAME) as fkQuery
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = @Tablename
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @fkQuery
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@fkQuery)
-- PRINT @fkQuery
FETCH NEXT FROM db_cursor INTO @fkQuery
END
CLOSE db_cursor
DEALLOCATE db_cursor
SET @fkQuery = 'ALTER TABLE [dbo].' + QUOTENAME(@TableName) + ' ' + IIF(@Disable=1,'NOCHECK','CHECK') + ' CONSTRAINT ALL'
EXEC (@fkQuery)
-- PRINT @fkQuery
November 16, 2018 at 6:42 am
Jonathan AC Roberts - Friday, November 16, 2018 6:34 AMscout7-500377 - Friday, November 16, 2018 6:16 AMJonathan AC Roberts - Friday, November 16, 2018 6:14 AMscout7-500377 - Friday, November 16, 2018 6:08 AMJonathan AC Roberts - Friday, November 16, 2018 5:47 AMscout7-500377 - Friday, November 16, 2018 4:55 AMJonathan AC Roberts - Friday, November 16, 2018 4:31 AMIf you are deleting from a table based on a query joining several tables then some if the indexes will help performance. So you shouldn't be disabling all non-clustered indexes.Thank you for your reply but the delete statements have no joins in them at all. They are generated from a script that has the required joins and then this query just spits out the resulting delete statement based upon the primary key fields required. Admittedly disabling the indexes was just something I tried thinking it might speed things up if it was not necessary to handle indexes after a delete. I think I may have misunderstood how deletes affect indexes..
In your original post you say are deleting with statements like this:
DELETE FROM [TARGET TABLE}
FROM [TARGET TABLE}
INNER JOIN {JOIN 1} ON {JOIN 2}.{COL}={JOIN 1}.{COL}
INNER JOIN {JOIN 3} ON {JOIN 2}.{COL}={JOIN 3}.{COL}
WHERE [dbo].[tbl_VPOS_Locations].LocationID NOT IN (0, 199, 200, 346, 370)Now you say "the delete statements have no joins in them at all"
So how are you deleting from the tables? Do you store the keys in a temporary table? I don't understand how you are deleting the rows.Because if you read my update from earlier today you will see that I changed my approach
" I have now tried a slightly different approach. I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
"I have created a script that generates individual delete statements based on the primary key of the row that I need to delete."
So you are generating a delete statement for every row you are deleting??
Instead, just inset the primary key values that you need to delete into temporary tables (one temporary table for each table you want to delete from) and then delete from each table with a join to its related temporary table.Now that is a very good idea that I am disappointed that I never thought of, can't see the wood for the trees sometimes! Many thanks. I will give that a try! 🙂
I've got a script you might find useful. Instead of "DROPPED ALL Foreign Key Constraints FOR ALL tables BEFORE running the scripts.". you can disable the foreign keys of all tables that have FKs to the table you are deleting from. Then after deletion rin the script again to enable the FKs. So before and after each delete you will need a script like this. All you need to do is edit the @Tablename values and @Disable bit.
DECLARE @Tablename sysname = 'myTableIWantToDeleteFrom'
DECLARE @Disable bit = 1 -- Set to 1 to disable 0 to enable
DECLARE @fkQuery VARCHAR(max)DECLARE db_cursor CURSOR FOR
SELECT 'ALTER TABLE [dbo].' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' ' + IIF(@Disable=1,'NOCHECK','CHECK') + ' CONSTRAINT ' + QUOTENAME(fk.NAME) as fkQuery
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = @TablenameOPEN db_cursor
FETCH NEXT FROM db_cursor INTO @fkQueryWHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@fkQuery)
-- PRINT @fkQuery
FETCH NEXT FROM db_cursor INTO @fkQuery
ENDCLOSE db_cursor
DEALLOCATE db_cursor
SET @fkQuery = 'ALTER TABLE [dbo].' + QUOTENAME(@TableName) + ' ' + IIF(@Disable=1,'NOCHECK','CHECK') + ' CONSTRAINT ALL'EXEC (@fkQuery)
-- PRINT @fkQuery
Excellent, thank you very much, very helpful. 🙂
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply