• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

优化多表查询

CRMNet 2008-03-13 08:50:58
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

怎么优化优化?数据量大了很慢
...全文
123 点赞 收藏 16
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
CRMNet 2008-03-13
select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

这两句哪个效率更高?
回复
csshan 2008-03-13
可以使用临时表
回复
csshan 2008-03-13
在日期条件上建索引,日期型最好是datetime类型,
如果数据量不是很大(<百万级)建立聚集索引

create CLUSTERED index
indexData on tableName(columName)

能快些,

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


回复
CRMNet 2008-03-13
Haiwer 说的没错,这些字段都建立了索引。
是否建立个相应的视图,速度会快?
回复
能看到的可能有用的索引(也许已经有了,也许并不是最优的)
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 2008-03-13
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 
回复
zhuyx808 2008-03-13
我给排下版,优化主要是把左连接去掉,分成几条语句完成



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
回复
建立索引

由于你的好多字段没有写明是哪个表的,具体的索引组合看不出来
回复
dawugui 2008-03-13
这么多内容,只能尝试对各个表的相关字段建立主键,索引.
回复
dawugui 2008-03-13
看看.
回复
friendlyFour 2008-03-13
在s_saledt上建立聚集索引。vip_cardlist.s_cardid上建立非聚集索引。在表关联的列上建非聚集索引。
将where s_saledt> ='2006/07/30' and s_saledt <='2006/07/31'
改为:between and
另:不建议建立试图。
回复
骑蚊子旅游 2008-03-13
inner join&left outer join&right outer join 区别
刚从网上摘录了一篇文章:
http://blog.csdn.net/my22xo/archive/2008/03/13/2177374.aspx
回复
骑蚊子旅游 2008-03-13
[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
回复
niming520 2008-03-13
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的區別再那里?
回复
viva369 2008-03-13
select * from a inner join b on a.ID=b.BID
select * from a,b where a.ID=b.BID

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

回复
测试下就知道了
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-13 08:50
社区公告
暂无公告