SQL Server 2019: Performance degradation with large IN clause in WHERE condition
I'm working with important performance optimization when executing a query that uses a large IN clause in SQL Server 2019. The query looks something like this: ```sql SELECT * FROM Orders WHERE CustomerID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20); ``` When I run this query with about 20 customer IDs, it takes several seconds to complete. However, when I reduce the list to just 5 IDs, the performance improves significantly. I've tried using a temporary table to store the customer IDs and then joining it with the Orders table: ```sql CREATE TABLE #CustomerIDs (CustomerID INT); INSERT INTO #CustomerIDs VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20); SELECT o.* FROM Orders o JOIN #CustomerIDs c ON o.CustomerID = c.CustomerID; ``` However, this still isn't giving me the performance boost I was hoping for. I've also checked the execution plan, and it shows a table scan on the Orders table, which is concerning. The Orders table has a non-clustered index on CustomerID, but it seems to be ignored in this case. I'm wondering if there's a better approach I should consider, especially since the IN clause can grow even larger in certain scenarios. Is there an optimal way to handle large lists in SQL queries without sacrificing performance? Any recommendations or best practices would be appreciated!