select @id = 1;with s1 as ( select id from List t1 where class = (select class from List where id = @id) ) select id into #test from s1 ;select * from #test where dbo.Func_bool_Faucet(id) = 1 ;生成新的临时表, 再where就非常快了, 也是不到一秒执行完
好像就是一个子查询,不用with改为: select @id = 1;select id from ( select id from List t1 where class = (select class from List where id = @id) ) s1 where dbo.Func_bool_Faucet(s1.id) = 1要几秒钟?
找到解决办法了, 但是原因就不清楚了, 估计是表格的多层嵌套导致吧 因为自定义函数里面也包含了: select id from List t1 where class = (select class from List where id = @id) 解决办法就是再多定义一个变量select @id = 1;declare @class; select @class = (select class from List where id = @id) ;with s1 as ( select id from List t1 where class = @class ) select id from s1 where dbo.Func_bool_Faucet(s1.id) = 1 ;
select id from List t1
where class = (select class from List where id = @id)
)
select id
into #test
from s1
;select *
from #test
where dbo.Func_bool_Faucet(id) = 1
;生成新的临时表, 再where就非常快了, 也是不到一秒执行完
select @id = 1;select id
from (
select id from List t1
where class = (select class from List where id = @id)
) s1
where dbo.Func_bool_Faucet(s1.id) = 1要几秒钟?
因为自定义函数里面也包含了:
select id from List t1
where class = (select class from List where id = @id)
解决办法就是再多定义一个变量select @id = 1;declare @class;
select @class = (select class from List where id = @id)
;with s1 as (
select id from List t1
where class = @class
)
select id
from s1
where dbo.Func_bool_Faucet(s1.id) = 1
;