条件参数可多选（也可能为空）情况下如何优化大表查询速度？

[alex_cjh]

查询一个表的时候比较慢

A表大概有1亿条的数据

有一个条件参数@CODE

可能是空，也可能是1234， 也可能是1234|4567|1111|1112......这个样子的
现在的做法是查询时在where加下面的条件
(ISNULL(@CODE,'')='' OR CHARINDEX('|'+A.CODE+'|','|'+@CODE+'|')>0)

主要因为要查询为空的情况，每次查询会耗时1-2分钟
因为会有好几个条件，所以一个存储过程就会耗时8分钟以上了，比较难接受。

用等号或者把判断时候为空去掉，查询会快很多...但是不符合实际需求

本人算是SQL新手，还请大家指教一下。

[开着拖拉机泡妞]

--借助系统表来写个例子  不知是否能提快你的查询速度
--你的问题使用了charindex()h函数，基本上可以说A.CODE这个字段上的索引废了  我的方法没测试过  单手我避开这个函数


create table #test
(
	id int identity,
	value int
)
declare @str varchar(max)
set @str='1|2|3|4|36|'
set @str=REPLACE(@str,'|',' union all select ')
set @str='insert #test(value) select '+LEFT(@str,LEN(@str)-17)
print @str
exec (@str)
select * from #test
/*
id          value
----------- -----------
1           1
2           2
3           3
4           4
5           3

(5 行受影响)

*/ 

--然后再inner join返回你的数据

select 
	a.number,
	a.name,
	a.type 
from 
	master..spt_values a 
inner join 
	#test b 
on 
	a.number=b.value and a.type='p'
	
/*
number      name                                type
----------- ----------------------------------- ----
1           NULL                                P  
2           NULL                                P  
3           NULL                                P  
4           NULL                                P  
36          NULL                                P  

(5 行受影响)

*/

我看了执行计划 除了对我建的临时表#test进行了表扫描 
master..spt_values表上的索引被充分利用到了  而临时表的数据量肯定是有限的  不会有很多 如果真的数据量很大，可以考虑给临时表的value字段加上索引

--个人一个想法 仅供参考

[SQL77]

1亿的表。你设计的查询这样？

[开着拖拉机泡妞]

--借助系统表来写个例子  不知是否能提快你的查询速度
--你的问题使用了charindex()h函数，基本上可以说A.CODE这个字段上的索引废了  我的方法没测试过  单手我避开这个函数


create table #test
(
	id int identity,
	value int
)
declare @str varchar(max)
set @str='1|2|3|4|36|'
set @str=REPLACE(@str,'|',' union all select ')
set @str='insert #test(value) select '+LEFT(@str,LEN(@str)-17)
print @str
exec (@str)
select * from #test
/*
id          value
----------- -----------
1           1
2           2
3           3
4           4
5           3

(5 行受影响)

*/ 

--然后再inner join返回你的数据

select 
	a.number,
	a.name,
	a.type 
from 
	master..spt_values a 
inner join 
	#test b 
on 
	a.number=b.value and a.type='p'
	
/*
number      name                                type
----------- ----------------------------------- ----
1           NULL                                P  
2           NULL                                P  
3           NULL                                P  
4           NULL                                P  
36          NULL                                P  

(5 行受影响)

*/

我看了执行计划 除了对我建的临时表#test进行了表扫描 
master..spt_values表上的索引被充分利用到了


select 
	a.number,
	a.name,
	a.type 
from 
	master..spt_values a 
where CHARINDEX('|'+LTRIM(number)+'|','|'+'1|2|3|4|36|')>0 and type='p'

[alex_cjh]

[Quote=引用 3 楼 的回复:]

1亿的表。你设计的查询这样？
[/Quote]

求指教^^

[alex_cjh]

[Quote=引用 4 楼 的回复:]

SQL code

--借助系统表来写个例子 不知是否能提快你的查询速度
--你的问题使用了charindex()h函数，基本上可以说A.CODE这个字段上的索引废了 我的方法没测试过 单手我避开这个函数


create table #test
(
id int identity,
value int
)
declare @str varchar(max)
set @s……
[/Quote]
谢谢，我试一下。

[开启时代]

把为空和非空的分开来查询 然后用 union all 合并结果 试试速度如何。