SELECT a.sex, b.age, c.name, d.state, e.type, f.head FROM a LEFT OUTER JOIN b ON a.PInstID = b.ID LEFT OUTER JOIN c ON c.ID = b.PrID LEFT OUTER JOIN d ON d.ID = c.PSetID LEFT OUTER JOIN e ON a.PInstID = e.PInstID AND a.HeaderID = e.ID LEFT OUTER JOIN f ON e.efctID = f.ID WHERE (a.Status IN (0, 1)) 1)别名在这里没必要,因为和表名相同 2)各连接ID建立索引 3)定期rebuild索引,减少碎片
SELECT
a.sex,
b.age,
c.name,
d.state,
e.type,
f.head
FROM a
LEFT OUTER JOIN b ON a.PInstID = b.ID
LEFT OUTER JOIN c ON c.ID = b.PrID
LEFT OUTER JOIN d ON d.ID = c.PSetID
LEFT OUTER JOIN e ON a.PInstID = e.PInstID AND
a.HeaderID = e.ID
LEFT OUTER JOIN f ON e.efctID = f.ID
WHERE (a.Status IN (0, 1))
1)别名在这里没必要,因为和表名相同
2)各连接ID建立索引
3)定期rebuild索引,减少碎片
如果这个语句是经常查询的话,建议在a.Status上建立聚集索引,因为条件只有这一个。
查下你这个sql的执行计划情况
a.Status上建立聚集索引