优化多表查询

[CRMNet]

select issue.s_goodsid,issue.s_deptid,s_deptname,s_style,s_emplid,s_gdsname,f_saleqty,f_execpri,s_saledt,s_saletm,s_cashid from issue INNER join vip_cardlist on issue.s_saledt=vip_cardlist.s_date and issue.i_listid=vip_cardlist.i_listid and issue.s_posid=vip_cardlist.s_posid left outer join goods on issue.s_goodsid=goods.s_goodsid and dbo.issue.s_deptid = dbo.goods.s_deptid LEFT OUTER JOIN department on issue.s_deptid=department.s_deptid where s_saledt>='2006/07/30' and s_saledt<='2006/07/31' and vip_cardlist.s_cardid='123456' order by s_saledt desc

怎么优化优化？数据量大了很慢

[一把编程的菜刀]

测试下就知道了

[viva369]

select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

----------------
执行计划是一致的，后者是sql较前的版本

[niming520]

select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

inner join ,left outer join,right outer join的區別再那里?

[骑蚊子旅游]

[Quote=引用 13 楼 niming520 的回复:]
select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

inner join ,left outer join,right outer join的區別再那里?
[/Quote]


UP

[骑蚊子旅游]

inner join&left outer join&right outer join 区别
刚从网上摘录了一篇文章：
http://blog.csdn.net/my22xo/archive/2008/03/13/2177374.aspx

[friendlyFour]

在s_saledt上建立聚集索引。vip_cardlist.s_cardid上建立非聚集索引。在表关联的列上建非聚集索引。
将where s_saledt> ='2006/07/30' and s_saledt <='2006/07/31'
改为：between and
另：不建议建立试图。

[dawugui]

看看.

[dawugui]

这么多内容,只能尝试对各个表的相关字段建立主键,索引.

[昵称被占用了]

建立索引

由于你的好多字段没有写明是哪个表的，具体的索引组合看不出来

[zhuyx808]

我给排下版，优化主要是把左连接去掉，分成几条语句完成

select issue.s_goodsid
	,issue.s_deptid
	,s_deptname
	,s_style
	,s_emplid
	,s_gdsname
	,f_saleqty
	,f_execpri
	,s_saledt
	,s_saletm
	,s_cashid 
from issue INNER join vip_cardlist on issue.s_saledt=vip_cardlist.s_date 
	and issue.i_listid=vip_cardlist.i_listid 
	and issue.s_posid=vip_cardlist.s_posid 
	left outer join goods on issue.s_goodsid=goods.s_goodsid 
		and dbo.issue.s_deptid = dbo.goods.s_deptid 
	LEFT OUTER JOIN department on issue.s_deptid=department.s_deptid 
	where s_saledt> ='2006/07/30' 
		and s_saledt <='2006/07/31' 
		and vip_cardlist.s_cardid='123456' 
		order by s_saledt desc

[CRMNet]

select issue.s_goodsid,issue.s_deptid,s_deptname,s_style,s_emplid,s_gdsname,f_saleqty,f_execpri,s_saledt,s_saletm,s_cashid from issue INNER join vip_cardlist on issue.s_saledt=vip_cardlist.s_date and issue.i_listid=vip_cardlist.i_listid and issue.s_posid=vip_cardlist.s_posid left outer join goods on issue.s_goodsid=goods.s_goodsid and dbo.issue.s_deptid = dbo.goods.s_deptid LEFT OUTER JOIN department on issue.s_deptid=department.s_deptid where s_saledt> ='2006/07/30' and s_saledt <='2006/07/31' and vip_cardlist.s_cardid='123456' order by s_saledt desc 

[昵称被占用了]

能看到的可能有用的索引（也许已经有了，也许并不是最优的）
issue(s_saledt,i_listid,s_posid)
vip_cardlist(s_cardid)
vip_cardlist(s_date,i_listid ,s_posid )
goods(s_goodsid,s_deptid )
department(s_deptid)

[CRMNet]

Haiwer 说的没错，这些字段都建立了索引。
是否建立个相应的视图，速度会快？

[csshan]

在日期条件上建索引，日期型最好是datetime类型，
如果数据量不是很大（<百万级）建立聚集索引

create CLUSTERED index
indexData on tableName(columName)

能快些，

如果〉百万级，就建非聚集NONCLUSTERED索引，

[csshan]

可以使用临时表

[CRMNet]

select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

这两句哪个效率更高？