CODEWITHSUNDEEP
sql-server-2005query-optimization
d-man
d-man

Reputation: 58083

Sqlserver 2005 delete query with join

Sqlserver 2005 delete query with join

I have following query with deletes the record, is there some thing in this query we can optimize ? the way deleting record with join is being used is perfect ? any overhead deleting with join records ?

DELETE BATCH_SCORE
    FROM BATCH_SCORE BS JOIN INQUIRY_MATCH IM ON BS.BATCH_ID = IM.BATCH_ID AND IM.INQUIRY_MATCH_ID = #inquiryMatchId#
    WHERE NOT EXISTS (SELECT TOP 1 1 FROM INQUIRY_MATCH WHERE BATCH_ID = BS.BATCH_ID AND CURRENT_STATE IN ('OPN','RVW',
    'ASN', 'ESC', 'PND')) AND BS.PERMANENT_BATCH = 'N'

i wrote new query as follows, is new query better then above one

 DELETE
    BATCH_SCORE
FROM
    BATCH_SCORE BS
WHERE
    NOT EXISTS (
SELECT
    TOP 1 1
FROM
    INQUIRY_MATCH
WHERE
    INQUIRY_MATCH_ID = 1011 AND
    BATCH_ID = BS.BATCH_ID AND
    CURRENT_STATE IN ('OPN',
    'RVW',
    'ASN',
    'ESC',
    'PND')) AND
    BS.PERMANENT_BATCH = 'N'

Upvotes: 0

Views: 221

Answers (1)

Adam Anderson
Adam Anderson

Reputation: 508

No, there is no particular overhead using a JOIN in a DELETE statement. Both queries are fairly simple with little room for optimization. If performance is lacking, try adding indexes to the columns being searched (INQUIRY_MATCH.INQUIRY_MATCH_ID, INQUIRY_MATCH.BATCH_ID, BATCH_SCORE.BATCH_ID)

An index on INQUIRY_MATCH.CURRENT_STATE might not help depending on how many distinct values there are in that column. A general rule of thumb is that MSSQL won't choose to use an index unless there is roughly 5% selectivity (e.g. 20 distinct values evenly distributed). This guideline probably completely rules out indexing BATCH_SCORE.PERMANENT_BATCH, since I would guess that it only contains 'N' and 'Y' values.

Upvotes: 1

Related Questions