# Which is faster? IN (list) or IN (SELECT FROM Temp)

Kenneth Fisher, 2018-10-11

If you’ve done much with IN (list) then you’ve realized that it basically translates out to

`col=val1 OR col=val2 OR ....`

You’ve probably also realized that you can throw all the values into a temp table and do this

```SELECT *
FROM tablename
WHERE col IN (SELECT col FROM #temp```

But which is faster? Well, let’s find out. First I’m going to create a table of 1,000,000 values to pull against. This way I will hopefully get some useful times and not one that takes 10ms and the other 9ms.

tl;dr; It was pretty inconclusive even at large numbers of values.

```-- Set up the lookup table
USE Test;
GO
WITH x AS (
SELECT TOP (100)
CAST(ROW_NUMBER() OVER (ORDER BY val.[name]) AS INT) AS x
FROM [master]..spt_values val),
Nums AS (
SELECT
CAST(ROW_NUMBER() OVER (ORDER BY x.x) AS INT) AS Num
FROM x
CROSS JOIN x y
CROSS JOIN x z)
SELECT
REPLICATE('a',ABS(CHECKSUM(NEWID()) % 1000)) AS col1,
REPLICATE('a',ABS(CHECKSUM(NEWID()) % 1000)) AS col2,
REPLICATE('a',ABS(CHECKSUM(NEWID()) % 1000)) AS col3,
REPLICATE('a',ABS(CHECKSUM(NEWID()) % 1000)) AS col4,
INTO ListTable
FROM Nums;
GO```

I’m going to do some tests of various sizes. The first one will be a small test of just 10 values.

Small test (10 values)

```-- Get a small list of values to look up
SELECT TOP 10 DateCol INTO #ListTemp
FROM ListTable
ORDER BY newid();
DECLARE @ListVar nvarchar(1000)
SELECT @ListVar =
STUFF((SELECT ', ' + QUOTENAME(CONVERT(varchar(30), DateCol, 121),'''')
FROM #ListTemp
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
, 1, 2, '');
DECLARE @sql varchar(max)
SET @sql =
'SELECT * FROM ListTable
WHERE DateCol IN ('+@ListVar+');'
PRINT @sql;```

Now using the query printed out:

```SET STATISTICS TIME, IO ON
SELECT * FROM ListTable
WHERE DateCol IN ('2000-01-02 01:28:00.000', '2000-01-06 07:05:00.000', '2000-01-05 20:24:00.000', '2000-01-02 18:15:00.000', '2000-01-02 08:12:00.000', '2000-01-07 03:48:00.000', '2000-01-07 18:07:00.000', '2000-01-06 03:31:00.000', '2000-01-03 19:55:00.000', '2000-01-04 22:13:00.000');
SELECT * FROM ListTable
WHERE DateCol IN (SELECT DateCol FROM #ListTemp);```

I ran this twice and discarded the first run because it was loading the data into memory. Yes, I realize that could be useful information, but I’m skipping that part of it for today.

```SQL Server parse and compile time:
CPU time = 12 ms, elapsed time = 12 ms.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 0 ms.
(1049 row(s) affected)
SQL Server Execution Times:
CPU time = 844 ms,  elapsed time = 984 ms.
(1049 row(s) affected)
SQL Server Execution Times:
CPU time = 484 ms,  elapsed time = 472 ms.```

Ok, so the logical reads are about the same. More from the IN (SELECT) actually, but only by a fraction because of the read on the temp table and some work tables. The time, however, was almost exactly half using the temp table. Now, this was without any indexes. So now I’m going to run the same queries again but this time having put a non-clustered index on ListTable.

`CREATE INDEX ix_ListTable ON ListTable(DateCol);`

This is a pretty simple index, and I don’t have a clustered index, nor is this a covering index but honestly, it’s a SELECT * so a covering index isn’t likely anyway.

This time the results are:

```SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
(1049 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 264 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
(1049 row(s) affected)
SQL Server Execution Times:
CPU time = 47 ms,  elapsed time = 208 ms.```

Oddly enough slightly less reads for the IN (SELECT) this time and it’s only slightly faster. I’m also tried adding an index on the temp table but with no significant change.

Summary (10 values)

With a small test (only 10 values) the IN (SELECT) was faster, with or without an index.

Large test (1000 values)

I’m going to use the exact same process as above but I’m not going to actually post the query because I feel like it will be too long for a blog post. Here is the code to generate the query though if you want to play along. I did have to make a few changes. The TOP became a TOP (1000) instead of a TOP (10), the varchar(1000) became a varchar(max) and the PRINT became a SELECT because you get more information that way.

```SELECT TOP 1000 DateCol INTO #ListTemp
FROM ListTable
ORDER BY newid();
DECLARE @ListVar varchar(MAX)
SELECT @ListVar =
STUFF((SELECT ', ' + QUOTENAME(CONVERT(varchar(30), DateCol, 121),'''')
FROM #ListTemp
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
, 1, 2, '');
DECLARE @sql varchar(max)
SET @sql =
'SELECT * FROM ListTable
WHERE DateCol IN ('+@ListVar+');'
SELECT @sql;```

Without the index: (I’m skipping IO this time because it didn’t seem to tell us much last time)

```(96517 row(s) affected)
SQL Server Execution Times:
CPU time = 1704 ms,  elapsed time = 5472 ms.
(96517 row(s) affected)
SQL Server Execution Times:
CPU time = 1250 ms,  elapsed time = 6031 ms.```

Hmm, faster for the IN (list) this time. Let’s try it with an index on ListTable.

```SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 0 ms.
(95297 row(s) affected)
SQL Server Execution Times:
CPU time = 1375 ms,  elapsed time = 5047 ms.
(95297 row(s) affected)
SQL Server Execution Times:
CPU time = 1250 ms,  elapsed time = 5787 ms.```

Still a bit faster for the IN (list). And again, I tried with an index on the temp table with no major difference.

Summary (1000 values)

This time the IN (list) was faster with and without indexes. By less than a second though.

Summary (10000 values)

I also did a test with 10,000 values. In this case, the IN (list) was still faster without an index, but once I put an index on the table the IN (SELECT) became faster, and with an index on both the table and the temp table it was a bit faster.

Over all summary

My results were really pretty inconclusive. I was really surprised since I truly expected the IN (SELECT) to be faster. Particularly with an index. But in the end, the timing wasn’t significantly different (a few seconds here or there) and I can’t be certain if that’s not something I’ve done wrong. So for most cases, I’m just going to use the version more convenient. For cases where even a fraction of a second matters (100’s of runs a second anyone?) I’m going to have to test to see which is working fastest in that case.

# Book Review: Big Red – Voyage of a Trident Submarine

I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…

Andy Warren

2009-03-10

# Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

# Inserting Markup into a String with SQL

In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code…

Phil Factor

2009-02-18

# Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17