IF EXISTS versus IF NOT EXISTS problem.
Posted on 2007-04-06
I would like to know if IF EXISTS is better than IF NOT EXISTS. I have a 'settings' table which I am building for my application. There are 10 settings a member can choose from on my site. By default all settings are ticked, so in my 'settings' table I am trying to decide whether to insert all rows into that table when a member joins (10 rows for 10 settings). And then in my procs I would have to do IF EXISTS if I am to allow a certain action pertaining to a setting. So if I have 1 million members that's a potential 10 million rows in this table (it is a basic table though, just 3 fields).
On the other hand I could have no settings in this table, and when a member unticks a setting I would store it in there, so the table in effect stores the settings the member DOESN'T want to allow. And so in my procs I would then do IF NOT EXISTS when testing for it.
My question is, I'm assuming IF NOT EXISTS is slower than IF EXISTS, because IF NOT EXISTS has to theoritically check the whole table? Whereas IF EXISTS stops when it finds a match? But the only way I can use IF EXISTS is if I am storing all settings by default (and removing a row when members untick them). So the table then would be much much bigger than if I was doing the opposite and not storing the settings. Which open is going to be quicker and better for the app?