Short summary Performance of subqueries method depends on the data distribution. Performance of conditional aggregation does not depend on the data distribution. Subqueries method can be faster or slower than conditional aggregation, it depends on the data distribution. Naturally, if the table has a suitable index, then subqueries are likely to benefit from it, because … Read more
In Oracle, NULL values are not indexed, i. e. this query: SELECT * FROM table WHERE column IS NULL will always use full table scan since index doesn’t cover the values you need. More than that, this query: SELECT column FROM table ORDER BY column will also use full table scan and sort for same … Read more
So your C# code is sending an ad hoc SQL query to SQL Server, using what method? Have you considered using a stored procedure? That would probably ensure the same performance (at least in the engine) regardless of who called it. Why? The ARITHABORT setting is one of the things the optimizer looks at when … Read more
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN, it will collect all the results from the sub-query before further processing.