How to bulk SPECIFC data from a DB respecting ALL CONSTRAINTS!

  • John Mitchell-245523 - Friday, November 16, 2018 5:40 AM

    Understood, 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.

  • Jonathan AC Roberts - Friday, November 16, 2018 6:14 AM

    scout7-500377 - Friday, November 16, 2018 6:08 AM

    Jonathan AC Roberts - Friday, November 16, 2018 5:47 AM

    scout7-500377 - Friday, November 16, 2018 4:55 AM

    Jonathan AC Roberts - Friday, November 16, 2018 4:31 AM

    If 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! 🙂

  • scout7-500377 - Friday, November 16, 2018 6:16 AM

    Jonathan AC Roberts - Friday, November 16, 2018 6:14 AM

    scout7-500377 - Friday, November 16, 2018 6:08 AM

    Jonathan AC Roberts - Friday, November 16, 2018 5:47 AM

    scout7-500377 - Friday, November 16, 2018 4:55 AM

    Jonathan AC Roberts - Friday, November 16, 2018 4:31 AM

    If 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

  • Jonathan AC Roberts - Friday, November 16, 2018 6:34 AM

    scout7-500377 - Friday, November 16, 2018 6:16 AM

    Jonathan AC Roberts - Friday, November 16, 2018 6:14 AM

    scout7-500377 - Friday, November 16, 2018 6:08 AM

    Jonathan AC Roberts - Friday, November 16, 2018 5:47 AM

    scout7-500377 - Friday, November 16, 2018 4:55 AM

    Jonathan AC Roberts - Friday, November 16, 2018 4:31 AM

    If 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

    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