February 10, 2015 at 4:18 pm
As Scott says, you should not need the isnull.
They were needed with the NOT IN because if a null value had been in the list, it would have messed up the NOT IN. So the isnull makes it 0, which is presumably never matched. With a join and NOT EXISTS you don't need this, which also helps the DB pick the appropriate index.
If its fast enough now, great. If there is still an issue, I'd look further into that other foreign key constraint. What action does it do for referential integrity? (CASCADE, NO ACTION, SET NULL, or SET DEFAULT)? Depending on how it manages things you may want to alter the solution further.
February 11, 2015 at 6:55 am
Wow, thanks everyone for the overwhelming response in help. I'm going to go with the NOT EXISTS because that feels like the smallest change from the vendor's code, but I learned a lot of new options that I don't typically think about.
Cheers,
Rick
Rick Todd
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply