按 ID 选择多行，是否有比 WHERE IN 更快的方法 [英] Selecting multiple rows by ID, is there a faster way than WHERE IN

问题描述

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

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

SELECT [Id]来自 [MyTable]WHERE [Id] 在 (1,5,9)

然而，这对于IN"子句中的大量项目来说相当慢

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

查询 1 个随机密钥(其中)花费了 0 毫秒查询 1000 个随机密钥(其中)需要 46 毫秒查询 2000 个随机密钥(其中)需要 94 毫秒查询 3000 个随机密钥(其中)需要 249 毫秒查询 4000 个随机密钥(其中)需要 316 毫秒查询 5000 个随机密钥(其中)花费了 391 毫秒查询 6000 个随机密钥(其中)需要 466 毫秒查询 7000 个随机密钥(其中)需要 552 毫秒查询 8000 个随机密钥(其中)需要 644 毫秒查询 9000 个随机密钥(其中)需要 743 毫秒查询 10000 个随机密钥(其中)需要 853 毫秒

有没有比使用 WHERE IN 更快的方法来做到这一点.

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

我听说一个

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

查询 1 个随机键用了 1ms查询 1000 个随机密钥需要 34 毫秒查询 2000 个随机密钥需要 69 毫秒查询 3000 个随机密钥需要 111 毫秒查询 4000 个随机密钥需要 143 毫秒查询 5000 个随机密钥需要 182 毫秒查询 6000 个随机密钥需要 224 毫秒查询 7000 个随机密钥需要 271 毫秒查询 8000 个随机密钥需要 315 毫秒查询 9000 个随机密钥需要 361 毫秒查询 10000 个随机密钥需要 411 毫秒

解决方案

好的，所以我通过定义一个表类型，然后将该类型直接传递到查询中并加入到查询中，让它运行得非常快.

在 SQL 中

CREATE TYPE [dbo].[IntTable] AS TABLE([值] [整数] NULL)

代码中

DataTable dataTable = new DataTable("mythang");dataTable.Columns.Add("value", typeof(Int32));toSelect.ToList().ForEach(selectItem => dataTable.Rows.Add(selectItem));使用 (SqlCommand 命令 = 新 SqlCommand(@选择 *FROM [dbo].[实体] e内部连接@ids on e.id = value", con)){var parameter = command.Parameters.AddWithValue("@ids", dataTable);参数.SqlDbType = System.Data.SqlDbType.Structured;parameter.TypeName = "IntTable";使用 (SqlDataReader reader = command.ExecuteReader()){而 (reader.Read()){结果.Add(reader.GetInt32(0));}}}

这会产生以下结果

查询 1 个随机键(传入表值)耗时 2 毫秒查询 1000 个随机键(传入表值)耗时 3 毫秒查询 2000 个随机键(传入表值)需要 4ms查询 3000 个随机键(传入表值)耗时 6 毫秒查询 4000 个随机键(传入表值)耗时 8 毫秒查询 5000 个随机键(传入表值)耗时 9 毫秒查询 6000 个随机键(传入表值)耗时 11 毫秒查询 7000 个随机键(传入表值)耗时 13 毫秒查询 8000 个随机键(传入表值)耗时 17ms查询 9000 个随机键(传入表值)耗时 16 毫秒查询 10000 个随机键(传入表值)耗时 18 毫秒

I have a SQL Table and I would like to select multiple rows by ID. For example I would like to get the row with IDs 1, 5 and 9 from my table.

I have been doing this with a WHERE IN statement similar to below:

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

However this is quite slow for large numbers of items in the 'IN' clause

Below is some performance data from selecting rows using where in from a table with 1,000,000 rows

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

Is there a faster way than using WHERE IN to do this.

We cant do a join as this is between disconnected systems.

I have heard an in memory temp table joined to the data in MYSQL may be faster but from my research MSSQL doesn't have have an in memory table option and even so wouldn't it be prone to exactly the same index scan on insert into the temp table as the WHERE IN has?

EDIT:

This table has ID as a PK so has the default PK index, cf

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]

Execution plan

Here is a GIST for a console app which produces these performance results https://gist.github.com/lukemcgregor/5914774

EDIT 2 I created a function which creates a temp table from a comma separated string, and then joined vs that table. Its faster but i think mostly because of the issue with parsing the query with 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

解决方案

OK so I got it going really fast by defining a table type and then passing that type directly into the query and joining onto it.

in SQL

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

in code

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));
        }
    }
}

this produces the following results

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

这篇关于按 ID 选择多行，是否有比 WHERE IN 更快的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！