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

    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
    104
    Provided Answers: 6
    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
    6
    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
    104
    Provided Answers: 6
    also, have you looked at how many rows you are deleting? Maybe delete the first xK at a time

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
  •