求优化这条SQL语句

[weeksun23]

RT

select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from SetCause as sc,CauseTime as ct,CauseInfo as ci,TeaAccount as ta where ct.scid = sc.scid and sc.cau_id = ci.cau_id and sc.tea_id = ta.tea_id  and ci.cau_id not in (select ci.cau_id from SetCauseTime as sct,SetCause as sc,CauseTime as ct,CauseInfo as ci where sct.ct_id = ct.ct_id and ct.scid = sc.scid and sc.cau_id = ci.cau_id and sct.stu_id = @stu_id) and ci.cau_name like '%'+@causeValue+'%'

可以拆分，越效率越好

[Dong]

select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from
SetCause as sc,
CauseTime as ct,
(Select * From CauseInfo Where cau_name like '%'+@causeValue+'%')[ci],
TeaAccount as ta ,
(Select * From SetCauseTime Where stu_id <> @stu_id)[sct]
where
sct.ct_id = ct.ct_id and
ct.scid = sc.scid and
sc.cau_id = ci.cau_id and
sc.tea_id = ta.tea_id
------------------------------

[Dong]

select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from 
SetCause as sc,
CauseTime as ct,
(Select * From CauseInfo Where cau_name like '%'+@causeValue+'%')[ci],
TeaAccount as ta ,
(Select * From SetCauseTime Where stu_id <> @stu_id)[sct],
where 
sct.ct_id = ct.ct_id and
ct.scid = sc.scid and 
sc.cau_id = ci.cau_id and 
sc.tea_id = ta.tea_id

[weeksun23]

[Quote=引用 3 楼 perisenjut 的回复:]
对关键列聚集索引吧
[/Quote]
具体应该怎么做？

[weeksun23]

[Quote=引用 6 楼 ju523756055 的回复:]
SQL code

视图 索引
[/Quote]
不懂。。。

[weeksun23]

[Quote=引用 10 楼 upcc 的回复:]
SQL code

select ct.ct_id,sc.scid,tea_name,cau_name,ct_time from
SetCause as sc,
CauseTime as ct,
(Select * From CauseInfo Where cau_name like '%'+@causeValue+'%')[ci],
TeaAccount as ta ,
(Sel……
[/Quote]
我测试过，与我原来的select结果不一样

[anny_gomine]

看了就头疼

[sundayzhao]

太烦了，改为not Exists能快些

[mike]

好麻煩，最好建立索引，SQL中Exists好像不能用吧

[perisenjut]

对关键列聚集索引吧

[紫竹林畔]

关键
and ci.cau_name like '%'+@causeValue+'%'
这里没什么可优化的

[victor_yang]

出现了 not in 和 like 操作符，这些都是很慢的，要看看数据库结构才知道怎么优化。

[jmx123456789]

视图 索引

[zhangweitc123]

[Quote=引用 6 楼 ju523756055 的回复:]
SQL code


视图 索引
[/Quote].

[EnForGrass]

这么多表，视图处理