SQL in (1,2,3.....)问题

[AMinfo]

表：Table
id：自动编号
ST：数字

表的记录大概有100万条左右

现在需要的是：
Select id From [Table] Where [id] in(1,2,3,4,5,................................) and [ST] = 0

但由于in()里面的数据太大，有上千条，导致出现以下错误问题：

查询处理器用尽了内部资源，无法生成查询计划。这种情况很少出现，只有在查询极其复杂或引用了大量表或分区时才会出现。

求解决思路？

[發糞塗牆]

引用 9 楼 AMinfo 的回复:

[quote=引用 6 楼 DBA_Huangzj 的回复:] 先用函数转换你的一串ID，必要的时候加上索引，然后和你的原数据join

--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
   drop function dbo.f_splitSTR
go
 
create function dbo.f_splitSTR
(
    @s varchar(8000),     --要分拆的字符串
    @split varchar(10)    --分隔字符
) 
returns @re table(                      --要返回的临时表
                     col varchar(1000)  --临时表中的列 
                 )
as
begin   
  declare @len int
   
  set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符
   
  while CHARINDEX(@split,@s) >0
  begin
    insert into @re 
    values(left(@s,charindex(@split,@s) - 1))
     
    set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符
  end
   
  insert into @re values(@s)
   
  return   --返回临时表
end
go  
 
 
 
declare @res varchar(100)
set @res='1 2 3 4 5';
 
set @res = REPLACE(@res,' ',',')
 
select * from dbo.f_splitSTR(@res,',') t 
/*
col
1
2
3
4
5
*/

谢谢！这种方法挺好的，但还需要对另外一个字段的值进行判断才能获得结果。[/quote]我这个是例子，是思路，你可以扩展，总的来说，不要in过多的数据

[發糞塗牆]

因为in本质就是or，优化器对每个or都要做分析，过多的or会导致优化器无法分析，最后报错

[让你望见影子的墙]

引用 13 楼 DBA_Huangzj 的回复:

因为in本质就是or，优化器对每个or都要做分析，过多的or会导致优化器无法分析，最后报错

我觉得不用那么麻烦把。 讨论下，这个可用？

Select id From [Table] Where charindex(id,'1,2,3……‘)>0 and [ST] = 0

[發糞塗牆]

引用 14 楼 HEROWANG 的回复:

[quote=引用 13 楼 DBA_Huangzj 的回复:] 因为in本质就是or，优化器对每个or都要做分析，过多的or会导致优化器无法分析，最后报错

我觉得不用那么麻烦把。 讨论下，这个可用？

Select id From [Table] Where charindex(id,'1,2,3……‘)>0 and [ST] = 0

[/quote]这个要看数据量了，当年遇到这个类似情况的时候还不会用charindex，所以没测过是否不抱错。不过这个函数基本上就是表扫描或索引扫描，所以可能性能没那么好。如果换成集合，加上索引，可能能用得上

[让你望见影子的墙]

他要in那么多的数据，怎么着效率也上不了去。至少实现起来简单，但是效率还得实测。

[AMinfo]

引用 14 楼 HEROWANG 的回复:

[quote=引用 13 楼 DBA_Huangzj 的回复:] 因为in本质就是or，优化器对每个or都要做分析，过多的or会导致优化器无法分析，最后报错

我觉得不用那么麻烦把。 讨论下，这个可用？

Select id From [Table] Where charindex(id,'1,2,3……‘)>0 and [ST] = 0

[/quote] 正在尝试用这个办法，但是charindex(id,'1,2,3……')>0要改成charindex(',' +id + ',' ,','+'1,2,3……'+',')>0

[發糞塗牆]

引用 16 楼 HEROWANG 的回复:

他要in那么多的数据，怎么着效率也上不了去。至少实现起来简单，但是效率还得实测。

in一千多个，以前我处理过的，执行计划直接无法生成，后来减少到500多个，跑了2个小时，我换成表join之后，2分钟就出来了

[lei00529]

不要用in 用join，第一in不走索引，第二。。。。 第三：in只适合小数据时使用 是你设计有问题。

[AMinfo]

引用 18 楼 DBA_Huangzj 的回复:

引用 16 楼 HEROWANG 的回复:

他要in那么多的数据，怎么着效率也上不了去。至少实现起来简单，但是效率还得实测。

in一千多个，以前我处理过的，执行计划直接无法生成，后来减少到500多个，跑了2个小时，我换成表join之后，2分钟就出来了

这个数量可能有上万个，不确定性，之前把id集分段进行in后再把结果合在一起耗时在15秒左右，但还是太慢，不能授受，用临时表的话，由于使用频率可能会很大，而且需要再对另外一字段进行处理，效率也不高。

[AMinfo]

引用 14 楼 HEROWANG 的回复:

[quote=引用 13 楼 DBA_Huangzj 的回复:] 因为in本质就是or，优化器对每个or都要做分析，过多的or会导致优化器无法分析，最后报错

我觉得不用那么麻烦把。 讨论下，这个可用？

Select id From [Table] Where charindex(id,'1,2,3……‘)>0 and [ST] = 0

[/quote] 此方法不行，超占CPU，卡住了。

[發糞塗牆]

你用局部临时表就可以避免并发问题，tempdb开大一点，放到IO快的盘上，问题不大。另外一个字段进行处理，就要看看如何整合进去。

[AMinfo]

引用 22 楼 DBA_Huangzj 的回复:

你用局部临时表就可以避免并发问题，tempdb开大一点，放到IO快的盘上，问题不大。另外一个字段进行处理，就要看看如何整合进去。

谢谢！

[AMinfo]

目前还是只能通过分段in的方法处理，8万多条记录通过in处理以及另外一个字段处理时间要15秒左右，主要耗的时间在于分成好几十段后分别查询所需要的时间。

[發糞塗牆]

用join你是觉得麻烦还是慢？

[AMinfo]

引用 25 楼 DBA_Huangzj 的回复:

用join你是觉得麻烦还是慢？

没有用Join方法解决的思路，您的意思是先将ID集存入临时表tmpdb中，然后再用Join的方法从2个表中取得相同值的ID？ 可现在的问题是：这个ID集在表中就是有存在的，只是需要在这个集结果对另外一个字段的值进行判断。

[空心兜兜]

动态拼接成 id=1 or id=2…………

[發糞塗牆]

假设你已经有了这个ID的集合，假设为#t，那么你现在需要怎么对另外一个字段进行判断？

[發糞塗牆]

在你这个例子中，In始终是个性能杀手

[AMinfo]

引用 1 楼 ccs02287 的回复:

动态拼接成 id=1 or id=2…………

有几千个啊，这样可行吗？

[我纯洁全身都纯洁]

oracle也一样 in不能超过1000个