Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2016
    Posts
    11

    Question Unanswered: I need a better performance query for a simple situation

    Hi,

    I have two tables A (IdA, Field1) and B (IdB, Field2).
    The join can be done with A.field1 = B.Field2. Field1 and Field2 are varchar(14).
    Table A has 1.312.241 rows and table B has 5000 rows.
    I would like to delete on table A the rows where A.field1 doesn´t exist on table B.Field2.

    When i´ve tried the follow query it keeps runing for more than 10 minutes and i don´t have the delete finished:
    DELETE FROM A where Field1 not in (select Field2 from B);

    Can anyone tell me another query that i could use instead with better performance?

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    a lot of time an EXIST/NOT EXISTS will perform much faster than an IN/NOT IN.

    I haven't used it much, but what about using EXCEPT and turn that into the rows you delete?

  3. #3
    Join Date
    Jun 2016
    Posts
    11
    Quote Originally Posted by DNance View Post
    a lot of time an EXIST/NOT EXISTS will perform much faster than an IN/NOT IN.

    I haven't used it much, but what about using EXCEPT and turn that into the rows you delete?
    Thanks, i will test your suggestions.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,595
    Provided Answers: 1
    Are there triggers or cascading delete constraints on the table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    also, have you looked at how many rows you are deleting? Maybe delete the first xK at a time

  6. #6
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4

    Chunk Delets

    I always use chunk delete's for large tables, you can see your results much faster and your not growing your log. Log growth is a real performance killer. I also created a temp table of Key fields with a index to match the order of the table I wished to delete from. Somewhere I read that it helps reduce paging, but that depends on your data. I only mention it because I do a WHERE IN you probably want a WHERE NOT IN. Try this,

    /* ************************************** */
    /* Delete record from Returns Table */
    /* ************************************** */
    SET @r = 1;
    WHILE @r > 0
    BEGIN TRY
    BEGIN TRANSACTION;

    DELETE TOP (100000)
    FROM dbo.Trace_Return_Table
    WHERE TR_SerNum IN (SELECT TR_SerNum FROM #ControlTable);

    SET @r = @@ROWCOUNT;
    COMMIT;
    CHECKPOINT;
    SET @STRMSG = N'Deleted 100000 Rows' + N' '
    + RTRIM(CAST(GETDATE() AS nvarchar(30)));
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

    END TRY
    BEGIN CATCH
    SET @STRMSG = (SELECT ERROR_MESSAGE())
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    IF @@TRANCOUNT>0 ROLLBACK
    END CATCH

    For now I am using SET ROWCOUNT depending on your version consider:
    Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP
    Last edited by wjburke2; 06-05-17 at 11:56. Reason: omited a point

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •