按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]来自 [我的表][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 解析查询的问题

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

解决方案

好的，所以我定义了一个表类型，然后将该类型直接传递到查询中并加入它，从而实现了非常快.

在 SQL 中

创建类型 [dbo].[IntTable] AS TABLE([值] [整数] NULL)

在代码中

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

这会产生以下结果

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

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屋！