最近在优化SQL语句时,碰到一些怪的现象,所以请高手指点一下。
例如下面的简单语句:select a.id_a, b.id_b, c.id_c
from table_a a, table_b b, table_c c
where a.id_a = b.id_a and b.id_b = c.id_b
and a.lb = '1' and b.lb = '2' and c.lb = '3'
如果语句改成:select a.id_a, b.id_b, c.id_c
from (select * from table_a where lb = '1') a
, (select * from table_b where lb = '2') b
, (select * from table_c where lb = '3') c
where a.id_a = b.id_a and b.id_b = c.id_b
或是改成:select a.id_a, b.id_b, c.id_c
from (select id_a from table_a where lb = '1') a
, (select id_a, id_b from table_b where lb = '2') b
, (select id_b, id_c from table_c where lb = '3') c
where a.id_a = b.id_a and b.id_b = c.id_b
假如相关的索引都存在,这三种写法,那种更快一点???
我之前测试过第一种和第三种,有的语句第一种快,有的语句第三种快。
例如下面的简单语句:select a.id_a, b.id_b, c.id_c
from table_a a, table_b b, table_c c
where a.id_a = b.id_a and b.id_b = c.id_b
and a.lb = '1' and b.lb = '2' and c.lb = '3'
如果语句改成:select a.id_a, b.id_b, c.id_c
from (select * from table_a where lb = '1') a
, (select * from table_b where lb = '2') b
, (select * from table_c where lb = '3') c
where a.id_a = b.id_a and b.id_b = c.id_b
或是改成:select a.id_a, b.id_b, c.id_c
from (select id_a from table_a where lb = '1') a
, (select id_a, id_b from table_b where lb = '2') b
, (select id_b, id_c from table_c where lb = '3') c
where a.id_a = b.id_a and b.id_b = c.id_b
假如相关的索引都存在,这三种写法,那种更快一点???
我之前测试过第一种和第三种,有的语句第一种快,有的语句第三种快。
如果select 字段一样多,可以说没有什么差别,sql server2008已经自动智能优化了
#2.至于1和3,就你举的例子中的SQL来说,应该也无任何区别,因为执行计划会生成一样的。至于其它的复杂情况,可能会产生不同的执行计划,而产生不同的查询速度。
一切看执行计划,直接讨论哪个快慢,无意义。
1.推荐用第一种写法.
2.在表名后加(nolock).
3.注意索引碎片问题.
4.分析执行计划.
而在现在的使用过程中,感觉第一种会使用索引,而第三种情况下不知道索引还会不会有用。
所以搞不懂究竟是哪种情况下会快。在测试时,有时第一种和第三种的差异能达到几倍。前两个月写过一个相当复杂的SQL语句,后来因为导致服务器负载太高,被DBA停掉了,让优化。就怕改成第三种写法,结果更差,被骂。
楼主不喜欢用 join 吗,那样看起来更清晰易懂。