SQL Server and Efficiency of Large Range of Values within an IN Clause

Question

I have a case similar to this MySQL question regarding the IN clause, but for SQL Server.

Specifically, I am building an executable SQL string, and within it is the potential for a VERY long list of enumerated items for use within an IN clause (I.E. 1000+).

Is this an efficient way of constructing my filter from a dynamic list of criteria, or should I insert the criteria data into a temp table and then JOIN it to my export table for the filtering operation?

Pros and Cons of each approach would be appreciated if the answer isn't quite straight forward.

I apologize if this question has been asked. The linked MySQL question is rather old. I'd imagine that this is a duplicate for SQL Server, but I can't find it.

Aaron Bertrand · Accepted Answer · 2019-09-06T13:11:22.180

You forgot to tell us what version of SQL Server you're using. Of course with each new version we get capabilities that help us solve problems in more efficient ways.

In SQL Server 2005+ you can use a simple table-valued function like this to implement a join:

CREATE FUNCTION [dbo].[SplitInts]
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM ( SELECT Item = x.i.value('(./text())[1]', 'int') FROM 
            ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
              + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)
          ) AS y WHERE Item IS NOT NULL
   );
GO

Now pass in your big list and join:

CREATE PROCEDURE dbo.GetData
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT t.col1, t.col2 --, ...
      FROM dbo.DataTable AS t
      INNER JOIN dbo.SplitInts(@List, ',') AS i
      ON t.ColumnID = i.Item;
END
GO

This is not fantastic (I blogged about performance of various methods here and under 5000 values there is little difference), but may very well perform better than an ad hoc IN (...huge list...) query.

In SQL Server 2008+ you can use a table-valued parameter. Similarly as above you can pass a DataTable or whatever structure from C# into a stored procedure and perform a join. If you're using 2008 or above I can add an example for that too.

Ah, excellent article and this is exactly what I was looking for. I must say, after reading your article, the XML hiccup at the 500-1000 items test is quite odd. Did you ever find the cause of that specific inefficiency? Just curious. — RLH, Mar 08 '12 at 19:29
No, I've been meaning to test again (and include TVP in the comparison), but haven't come around to it yet. — Aaron Bertrand, Mar 08 '12 at 19:31

score 1 · Answer 2 · answered Mar 08 '12 at 19:27

1

I suggest the staging table approach, and use a where exists(...) clause, e.g.

select * from Employees e
where exists(select 1 from BigEmplCriteriaTable where criteria=e.EmployeeLoc)

answered Mar 08 '12 at 19:27

John Dewey

6,985
3
22
26

SQL Server and Efficiency of Large Range of Values within an IN Clause

2 Answers2