SQL Server Performance Forum – Threads Archive

Improve query

Hello! Because of the current database design we have to execute this query quite often. DELETE FROM messages WHERE fromUserId not in (select userid from users WITH (nolock))
DELETE FROM messages WHERE toUserId not in (select userid from users WITH (nolock))
DELETE FROM messages WHERE messageDataId not in (select userid from messagesData WITH (nolock)) Any ways to make this one execute faster?

---

Does the messages table have separate indexes on those three columns? Perhaps NOT EXISTS will speed up the query, especially if you combine the three deletes into a single delete (deletes are always slow operations): DELETE FROM messages
WHERE NOT EXISTS
(SELECT * FROM users WHERE users.userid = messages.userid)
OR NOT EXISTS
(SELECT * FROM users WHERE users.userid = messages.toUserId)
OR NOT EXISTS
(SELECT * FROM users WHERE users.userid = messages.messageDataId)

---

]]>