Is SQL IN bad for performance?

by

There are several considerations when writing a query using the IN operator that can have an affect on performance.

First, IN clauses are generally internally rewritten by most databases to use the OR logical connective. So col IN ('a','b','c') is rewritten to: (COL = 'a') OR (COL = 'b') or (COL = 'c'). The execution plan for both queries will likely be equivalent assuming that you have an index on col.

Second, when using either IN or OR with a variable number of arguments, you are causing the database to have to re-parse the query and rebuild an execution plan each time the arguments change. Building the execution plan for a query can be an expensive step. Most databases cache the execution plans for the queries they run using the EXACT query text as a key. If you execute a similar query but with different argument values in the predicate – you will most likely cause the database to spend a significant amount of time parsing and building execution plans. This is why bind variables are strongly recommended as a way to ensure optimal query performance.

Third, many database have a limit on the complexity of queries they can execute – one of those limits is the number of logical connectives that can be included in the predicate. In your case, a few dozen values are unlikely to reach the built-in limit of the database, but if you expect to pass hundreds or thousands of value to an IN clause – it can definitely happen. In which case the database will simply cancel the query request.

Fourth, queries that include IN and OR in the predicate cannot always be optimally rewritten in a parallel environment. There are various cases where parallel server optimization do not get applied – MSDN has a decent introduction to optimizing queries for parallelism. Generally though, queries that use the UNION ALL operator are trivially parrallelizable in most databases – and are preferred to logical connectives (like OR and IN) when possible.

More Related Contents:

  1. Add a column with a default value to an existing table in SQL Server
  2. Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?
  3. How to Join to first row
  4. Calendar Table – Week number of month
  5. Pivot using SQL Server 2000
  6. How to get the first and last date of the current year?
  7. How can I generate a temporary table filled with dates in SQL Server 2000?
  8. How Can I Sort A ‘Version Number’ Column Generically Using a SQL Server Query
  9. Perform regex (replace) in an SQL query
  10. WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value
  11. Find last sunday
  12. Difference between language sql and language plpgsql in PostgreSQL functions
  13. SQL Select between dates
  14. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  15. Mixing implicit and explicit JOINs
  16. how can I Update top 100 records in sql server
  17. How can I have NHibernate only generate the SQL without executing it?
  18. get next sequence value from database using hibernate
  19. Selecting Random Rows in MySQL
  20. concatenate two database columns into one resultset column
  21. SQLite and custom order by
  22. Update a table with join?
  23. How can I compare time in SQL Server?
  24. Improve SQL Server query performance on large tables
  25. Timestamp Difference In Hours for PostgreSQL
  26. Show all rows that have certain columns duplicated
  27. T-SQL Skip Take Stored Procedure
  28. MSSQL Regular expression
  29. Check if access table exists
  30. Postgres and Indexes on Foreign Keys and Primary Keys

Leave a Comment