UNION ALL vs OR condition in sql server query

by

The issue is that you are specifying two conditions with OR that apply to separate tables in your query. Because of this, the nonclustered index seek has to return most or all of the rows in your big table because OR logic means they might also match the condition clause in the second table.

Look at the SQL execution plan in all three examples above, and notice the number of rows that come out of the nonclustered index seek from the big table. The ultimate result may only return 1,000 or fewer of the 800,000 rows in the table but the OR clause means that the contents of that table have to be cross-referenced with the conditional in the second table since OR means they may be needed for the final query output.

Depending on your execution plan, the index seek may pull out all 800,000 rows in big table because they may also match the conditions of the OR clause in the second table. The UNION ALL is two separate query against one table each, so the index seek only has to output the smaller result set that might match the condition for that query.

I hope this makes sense. I’ve run across the same situation while refactoring slow-running SQL statements.

Cheers,

Andre Ranieri

More Related Contents:

  1. SQL QUERY COUNT THEN ADD
  2. SQL server command help and tutorials for specific commands?
  3. How do I UPDATE from a SELECT in SQL Server?
  4. How to calculate age (in years) based on Date of Birth and getDate()
  5. CROSS JOIN vs INNER JOIN in SQL
  6. INNER JOIN vs LEFT JOIN performance in SQL Server
  7. Function vs. Stored Procedure in SQL Server
  8. How to get difference between two rows for a column field?
  9. What’s the best way to select the minimum value from several columns?
  10. Counting DISTINCT over multiple columns
  11. SQL Server SELECT into existing table
  12. How to check if a stored procedure exists before creating it
  13. Insert Into… Merge… Select (SQL Server)
  14. Correct use of transactions in SQL Server
  15. The parameterized query expects the parameter which was not supplied
  16. SQL Server: drop table cascade equivalent?
  17. SQL Server converting varbinary to string
  18. SSIS Source Format Implicit Conversion for Datetime
  19. How do I prevent a database trigger from recursing?
  20. SQL Server Operating system error 5: “5(Access is denied.)”
  21. UNION the results of multiple stored procedures
  22. Executing SQL Server Agent Job from a stored procedure and returning job result
  23. how does SELECT TOP works when no order by is specified?
  24. SQL Server FOR XML Path make repeating nodes
  25. SELECT max(x) is returning null; how can I make it return 0?
  26. How to return default value from SQL query
  27. SQL INSERT INTO from multiple tables
  28. Group consecutive rows of same value using time spans
  29. Conditional aggregation performance
  30. SQL add filter only if a variable is not null

Leave a Comment