case 查询慢
select * from View_Print_Consigness where Print_state=1 and V_Level=(case when substring(d_NO,1,1)=''A'' then ''H'' else ''D'' end)
查询9秒
select * from View_Print_Consigness where Print_state=1
查询3秒
请问为什么加上V_Level=(case when substring(d_NO,1,1)=''A'' then ''H'' else ''D'' end)之后就会慢了这么多呀?
有什么办法替换case的吗?
[解决办法]
说实话,我没看懂你的语句.
[解决办法]
CSDN:多了' '这些空格,看起来不自在..
substring(d_No,1,1)替换为left(d_No,1)
用以下方法生成临时表测测
select *,case when left(d_No,1)='A' then 'H' else 'D' end as Lev
into #
from View_Print_Consigness where Print_state=1
select
*
from
#
where lev=V_Level
[解决办法]
because SQL Compiler cannot do much when you use case/substring in the where clause. Use temporary table or CTE to strip out the substring(d_No,1,1) and put it in a separate column probably will make things better if your table is very large.
[解决办法]
用left代替