如何在WHERE子句中改进包含带有Split函数的IN的sql？

Let say I have a query which will operate on on millions of record. Now, I have this in my WHERE clause,

假设我有一个查询,它将对数百万条记录进行操作。现在,我在WHERE子句中有这个,

WHERE Status IN (SELECT VALUE FROM SPLIT(@Status,','))

Clearly, 'SELECT VALUE FROM SPLIT(@Status,',')' will be operate on every row. I need to improve this.

很明显,'从分割中选择值(@Status,',')'将在每一行上运行。我需要改进这个。

3 个解决方案

#1

You can either do you current solution or compare its performance with an INNER JOIN:

您可以使用当前解决方案,也可以将其性能与INNER JOIN进行比较:

SELECT A.*
FROM YourTable A
INNER JOIN SPLIT(@Status,',') B
ON A.Status = B.Value

#2

How anout placing the results from the SPLIT function in a table variable/temp table first and then doing an INNER JOIN on this rather?

如何将SPLIT函数的结果首先放在表变量/临时表中,然后再对此进行INNER JOIN?

#3

Unpack you string to a temp table and use that in your query instead. Having an index on the field in the temp table should help.

将字符串解压缩到临时表并在查询中使用它。在临时表中对字段建立索引应该有所帮助。

create table #Split (Value varchar(10) primary key)

insert into #Split(Value)
select distinct Value
from SPLIT(@Status, ',')

select SomeCol
from YourTable
where Status in (select Value from #Split)

drop table #Split

#1