sql - 按ID选择多行，有没有比WHERE IN更快的方法

Question

我有一个 SQL 表，我想按 ID 选择多行。例如，我想从表中获取 ID 为 1、5 和 9 的行。

我一直使用类似于下面的 WHERE IN 语句来执行此操作:

SELECT [Id]
FROM [MyTable]
WHERE [Id] IN (1,5,9)

但是，对于“IN”子句中的大量项目来说，这非常慢

下面是使用 where in 从 1,000,000 行表中选择行的一些性能数据

Querying for 1 random keys (where in) took 0ms
Querying for 1000 random keys (where in) took 46ms
Querying for 2000 random keys (where in) took 94ms
Querying for 3000 random keys (where in) took 249ms
Querying for 4000 random keys (where in) took 316ms
Querying for 5000 random keys (where in) took 391ms
Querying for 6000 random keys (where in) took 466ms
Querying for 7000 random keys (where in) took 552ms
Querying for 8000 random keys (where in) took 644ms
Querying for 9000 random keys (where in) took 743ms
Querying for 10000 random keys (where in) took 853ms

是否有比使用 WHERE IN 更快的方法来执行此操作。

我们无法进行连接，因为这是在断开连接的系统之间。

我听说过in memory temp table joined to the data in MYSQL may be faster但根据我的研究，MSSQL 没有内存表选项，即使如此，它在插入临时表时是否也容易进行与 WHERE IN 完全相同的索引扫描？

编辑:

该表的 ID 作为 PK，因此具有默认的 PK 索引，参见

CREATE TABLE [dbo].[Entities](
    [Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_dbo.Entities] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

执行计划

这是一个控制台应用程序的 GIST，它产生这些性能结果 https://gist.github.com/lukemcgregor/5914774

编辑2我创建了一个函数，它从逗号分隔的字符串创建一个临时表，然后连接到该表。它更快，但我认为主要是因为用 where in 解析查询的问题

Querying for 1 random keys took 1ms
Querying for 1000 random keys took 34ms
Querying for 2000 random keys took 69ms
Querying for 3000 random keys took 111ms
Querying for 4000 random keys took 143ms
Querying for 5000 random keys took 182ms
Querying for 6000 random keys took 224ms
Querying for 7000 random keys took 271ms
Querying for 8000 random keys took 315ms
Querying for 9000 random keys took 361ms
Querying for 10000 random keys took 411ms

Answer 1

好的，我通过定义表类型然后将该类型直接传递到查询中并连接到它，速度非常快。

在 SQL 中

CREATE TYPE [dbo].[IntTable] AS TABLE(
    [value] [int] NULL
)

在代码中

DataTable dataTable = new DataTable("mythang");
dataTable.Columns.Add("value", typeof(Int32));

toSelect.ToList().ForEach(selectItem => dataTable.Rows.Add(selectItem));

using (SqlCommand command = new SqlCommand(
    @"SELECT * 
    FROM [dbo].[Entities] e 
    INNER JOIN @ids on e.id = value", con))
{
    var parameter = command.Parameters.AddWithValue("@ids", dataTable);
    parameter.SqlDbType = System.Data.SqlDbType.Structured;
    parameter.TypeName = "IntTable";

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            results.Add(reader.GetInt32(0));
        }
    }
}

这会产生以下结果

Querying for 1 random keys (passed in table value) took 2ms
Querying for 1000 random keys (passed in table value) took 3ms
Querying for 2000 random keys (passed in table value) took 4ms
Querying for 3000 random keys (passed in table value) took 6ms
Querying for 4000 random keys (passed in table value) took 8ms
Querying for 5000 random keys (passed in table value) took 9ms
Querying for 6000 random keys (passed in table value) took 11ms
Querying for 7000 random keys (passed in table value) took 13ms
Querying for 8000 random keys (passed in table value) took 17ms
Querying for 9000 random keys (passed in table value) took 16ms
Querying for 10000 random keys (passed in table value) took 18ms