sql - 具有非常大列表的 IN 运算符在 SQL Server 中速度太慢。我应该用什么代替？

Question

我得到了一个复杂的 SQL 查询，其中 IN 语句可能包含超过 50,000 个 ID。查询在 20-30 秒后超时，解决此问题的唯一方法是更改​​查询以在不超过 1-2 秒内返回结果。增加超时不是一个选项。

创建表:

CREATE TABLE Items
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Name] NVARCHAR(200) NULL,
) 

CREATE TABLE Feature
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [ItemId] [INT] NOT NULL,
    [TagId] [INT] NOT NULL,
)

Feature 表 ItemId 列也是一个非唯一、非聚集索引。

选择脚本:

SELECT i.Name
FROM Items i
INNER JOIN Feature f ON i.Id = f.ItemId
WHERE f.TagId IN (123, 234, 456, .....)

即使 Items 表有 3-400,000 条记录，而 IN 运算符列表中有大约 80,000 条记录，它也会超时。

我必须显着提高性能

Answer 1

这里的关键是从查询文本 SQL 中获取 IDS。您可以通过 JSON 或 XML 或表值参数传递 IDS，或批量加载临时表。一切都会快得多。

EG这个

use AdventureWorks2017
set nocount on
dbcc freeproccache
go

declare @ids varchar(max) = ( select STRING_AGG(cast(salesorderid as varchar(max)),',') from sales.SalesOrderHeader )

select @ids 

select count(*)
from 
sales.SalesOrderheader

declare @sql nvarchar(max) = '
select * 
from sales.salesorderheader
where SalesOrderID in (' + @ids + ')'

print '---------IN list---------'
set statistics time on
exec (@sql)
set statistics time off
print '---------IN list---------'



print '---------JSON array---------'
set statistics time on
select * 
from sales.salesorderheader
where SalesOrderID in ( select value from openjson('[' + @ids + ']') )
set statistics time off
print '---------JSON array---------'

产出

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
---------IN list---------
SQL Server parse and compile time: 
   CPU time = 11781 ms, elapsed time = 12115 ms.

 SQL Server Execution Times:
   CPU time = 657 ms,  elapsed time = 1453 ms.

 SQL Server Execution Times:
   CPU time = 12438 ms,  elapsed time = 13569 ms.
---------IN list---------
---------JSON array---------

 SQL Server Execution Times:
   CPU time = 656 ms,  elapsed time = 984 ms.
---------JSON array---------

在 C# 中使用此方法非常简单。只需将数组或列表序列化为字符串，并将其传递给 SqlParameter。例如:

var con = new SqlConnection("Server=localhost;database=adventureworks2017;integrated security=true");
con.Open();

var ids = Enumerable.Range(1, 50_000).ToList();

var cmd = con.CreateCommand();
cmd.CommandText = "select * from sales.SalesOrderHeader where salesorderid in (select value from openjson(@pIds))";
var pIds = cmd.Parameters.Add("@pIds", SqlDbType.NVarChar);

pIds.Value = JsonSerializer.Serialize(ids);

using (var rdr = cmd.ExecuteReader())
{
        while (rdr.Read())
        {
            //do whatever
            Console.Write(".");
        }
}