有关多关键字结构表的查询优化方案.
由于公司需求特殊,
使用物料编码+型号规格为关键字,也就是说物料编码可以重复.
在出入库的时候还要加上颜色字段作为区分.而且资料表还有其它不下五个外键关联字段.
这样导致在做统计查询的时候速度很慢.
查询模式一般为在存储过程中使用如下SQL:
select distinct a.MatCode,a.Spec,a.ColorName,
InAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'),
OutAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'),
From vMatIn a
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
表我都根据查询建好了相应的索引.
我把出入库是放在同一张表的.用正负数来区分的.
目前资料表记录才13万多,感觉速度力不从心了.
有什么好的办法可以解决掉它?
问题点数：20、回复次数：16Top
1 楼vfssqs（lailailai）回复于 2006-11-04 09:36:52 得分 0
select distinct a.MatCode,a.Spec,a.ColorName,
InAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'),
OutAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'),
From vMatIn a
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
这一条SQL本身就有问题
distinct 本身就有分组的效果。而且再加上Order by 还能快起来才怪
InAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'),
OutAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'),
而且要实现你这个功能，你这种写法是最差的一种。Top
2 楼vfssqs（lailailai）回复于 2006-11-04 09:41:08 得分 0
13万记录。不怎么多。Top
3 楼drk928（一起看斜阳）回复于 2006-11-06 14:18:59 得分 0
那你说说怎么写比较快呢?Top
4 楼test2002（test2002）回复于 2006-11-09 16:43:46 得分 0
建议如下使用
declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'
select distinct a.MatCode,a.Spec,a.ColorName,
@piInAmt,
@piOutAmt,
From vMatIn a
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
这样会很快的，１０几万记录是很小的，几千万的记录，都不会慢Top
5 楼test2002（test2002）回复于 2006-11-09 17:19:38 得分 0
1.
select distinct a.MatCode,a.Spec,a.ColorName,
InAmt=
case b.Reason
when '入库' then sum(b.Amt)
end,
OutAmt=
case b.Reason
when '出库' then sum(b.Amt)
end,
From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
2\使用临时表,可能快些
select MatCode,Spec,ColorName,sum(Amt) as InAmt into #temp From vMatInout where Reason='入库'
group by MatCode,Spec,ColorName
select MatCode,Spec,ColorName,sum(Amt) as OutAmt into #temp1 From vMatInout where Reason='出库'
group by MatCode,Spec,ColorName
select distinct a.MatCode,a.Spec,a.ColorName,
b.InAmt=
c.OutAmt
From vMatIn a ,#temp b,#temp1 c
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
你自己测试一下.Top
6 楼drk928（一起看斜阳）回复于 2006-11-10 11:05:41 得分 0
方法一:
服务器: 消息 8120，级别 16，状态 1，行 1
列 'b.reason' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。Top
7 楼drk928（一起看斜阳）回复于 2006-11-10 11:29:18 得分 0
declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'
根本不支持这种写法.:
列前缀 'a' 与查询中所用的表名或别名不匹配。Top
8 楼test2002（test2002）回复于 2006-11-10 16:48:30 得分 0
select distinct a.MatCode,a.Spec,a.ColorName,
c.InAmt,
d.OutAmt,
From vMatIn a ,
vMatInout b，
（
select MatCode,Spec,ColorName,sum(Amt) as InAmt into #temp From vMatInout where Reason='入库'
group by MatCode,Spec,ColorName
） as c,
（
select MatCode,Spec,ColorName,sum(Amt) as OutAmt into #temp From vMatInout where Reason='出库'
group by MatCode,Spec,ColorName
） as d
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname and
a.MatCode=d.MatCode and a.Spec=d.Spec and a.ColorName=d.Colorname
Group By a.MatCode,a.Spec,a.ColorName
Order by a.MatCode,a.Spec,a.ColorName
Top
9 楼test2002（test2002）回复于 2006-11-10 16:54:22 得分 0
declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'
根本不支持这种写法.:
列前缀 'a' 与查询中所用的表名或别名不匹配。
-----------------------------
我写错了
，这样测试一下：
select distinct a.MatCode,a.Spec,a.ColorName,b.Reason,
InAmt=
case b.Reason
when '入库' then sum(b.Amt)
end,
OutAmt=
case b.Reason
when '出库' then sum(b.Amt)
end,
From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname
and (b.Reason="出库＂or b.Reason="入库")
Group By a.MatCode,a.Spec,a.ColorName，b.Reason
Order by a.MatCode,a.Spec,a.ColorName
这样肯定可以了，只是多了一列b.Reason
Top
10 楼test2002（test2002）回复于 2006-11-10 16:57:00 得分 0
select 里面的b.Reason去掉看看行不行，但group by 里面肯定要加b.Reason的．Top
11 楼drk928（一起看斜阳）回复于 2006-11-13 16:00:36 得分 0
你那个distinct要本没有取到作用.它的结果是
a.MatCode,a.Spec,a.ColorName,b.Reason,InAmt,OutAmt为唯一的.有两条记录了.入库一条,出库一条.
Top
12 楼drk928（一起看斜阳）回复于 2006-11-13 16:09:39 得分 0
就是因为group by 里加了reason,所以导致出来就是两条了.Top
13 楼drk928（一起看斜阳）回复于 2006-11-14 09:18:07 得分 0
运行成功.
但是你那个写法的速度还不够我原来的写法的速度快啊....
我的执行要2秒,而你的办法要5秒.Top
14 楼test2002（test2002）回复于 2006-11-14 10:14:13 得分 0
select distinct a.MatCode,a.Spec,a.ColorName,InAmt=
case b.Reason
when '入库' then sum(b.Amt)
end,
OutAmt=
case b.Reason
when '出库' then sum(b.Amt)
end,
From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname
and (b.Reason="出库＂or b.Reason="入库")
Group By a.MatCode,a.Spec,a.ColorName，b.Reason
Order by a.MatCode,a.Spec,a.ColorName
把select 中的b.Reason去掉应该也可以的，只在group by 中加b.Reason，这个方法应该最快的
-----------------------------
我的哪个写法芽 ？
这个？？
要修改，应该比你的快
select distinct a.MatCode,a.Spec,a.ColorName,
c.InAmt,
d.OutAmt,
From vMatIn a ,
（
select MatCode,Spec,ColorName,sum(Amt) as InAmt From vMatInout where Reason='入库'
group by MatCode,Spec,ColorName
） as c,
（
select MatCode,Spec,ColorName,sum(Amt) as OutAmt From vMatInout where Reason='出库'
group by MatCode,Spec,ColorName
） as d
where
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname and
a.MatCode=d.MatCode and a.Spec=d.Spec and a.ColorName=d.Colorname
Order by a.MatCode,a.Spec,a.ColorName
------------------------------
不要子集b,而且要搞错了，不用写入临时表的，把 into #temp去掉，也不用Group by 就行了，如果还不快的话，建议你表vMatIn ,vMatInoutb
都加上联合索引MatCode+Spec+ColorName，
十几万条记录，100ms以内便可以出来查询结果
Top
15 楼test2002（test2002）回复于 2006-11-14 10:23:20 得分 20
实际上，只要索引建对了，你的方法，我这几种方法，怎么也不可能超过一秒的查询速度的。Top
16 楼drk928（一起看斜阳）回复于 2006-11-21 09:07:13 得分 0
你上面的写法实际上是先求了同条件下唯一合计,然后再连接.用临时表也可以做的.表已经建立了聚合索引了.因为matCode有时候过长.所以效率并不高.后来改成了用入库日期做索引.因为查询大部分是按日期先过滤,再按物料类别,物料编号等条件..Top
