Another cursor conversion...
-
That'll work... looks like noeld posted the message at the same time and he didn't see your reply.
-
I preffer,
DELETE FROM PARTY_COMMENT WHERE exists
(SELECT * FROM PARTY WHERE PARTY.CORRESPONDENCE_ID = @corrid and
PARTY.PARTY_ID = PARTY_COMMENT.PARTY_ID
)
DELETE FROM PHONE WHERE exists
(SELECT * FROM PARTY WHERE PARTY.CORRESPONDENCE_ID = @corrid and
PARTY.PARTY_ID = PHONE.PARTY_ID
)
Because next time it would be a composed key (the IN won't do the charm then), and, in old times, EXISTS did use the index while the IN approach did not.
(SQL2K does use the index with IN and EXISTS)
-
Jorge,
I thought about EXISTS, but figured IN was just as good. I suppose I could set up a test to see if they come up with the same execution plan...
-
I vote for exists
. -
Select * ? Wouldn't this be even more efficient?
DELETE FROM PARTY_COMMENT WHERE exists
(SELECT PARTY_ID FROM PARTY WHERE PARTY.CORRESPONDENCE_ID = @corrid and
PARTY.PARTY_ID = PARTY_COMMENT.PARTY_ID
)
DELETE FROM PHONE WHERE exists
(SELECT PARTY_ID FROM PARTY WHERE PARTY.CORRESPONDENCE_ID = @corrid and
PARTY.PARTY_ID = PHONE.PARTY_ID
)
??
-
Here's my choice in order (for any operation, not just delete).
Exists and join (test each to see the fastest for that query)
in.
You do what you want with that. But in either case you have to test to see what is fastest.
-
A sub query in a EXISTS does not return rows, so the columns list is not relevant. It's just a syntax issue. That’s not the case with the IN case where the result set has to be exactly one column (computed if necessary, but one column).
The engine is just checking the condition and (assuming the index use as intended) it is not even reading the actual table pages.
-
Thanks. I've had that "Select *"="Bad" beaten into me for so long...
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply