我的数据库才10万而已,由于要算所有物料,比如一年内没有做过出入库的物料. select distinct a.MakFormID,a.MatType,a.MatCode,a.MatName,a.spec,a.colorname, Amount=(select isnull(sum(c.amount),0) from vMatIn c where (c.makformid=a.makformid or c.FromMakFormID=a.MakFormID) and c.ischeck=1 and c.Matcode=a.Matcode and a.spec=c.spec and a.colorname=c.colorname), a.Unitname from vMatIn a where (a.MatCode like '%'+@Matcode+'%' or a.MatName like '%'+@Matcode+'%') and a.ClientName like '%'+@ClientName+'%' and a.MakFormID+a.MatCode+a.spec+a.ColorName not in (select distinct b.Compose from vMatIn_Compose b where b.theDate>@NDate and (b.MatCode like '%'+@Matcode+'%' or b.MatName like '%'+@Matcode+'%') and b.Mattypeid in (select Mattypeid from fALLMatTypeID(@MatType))) --求包含的子类别 order by a.MatCode 由于关键字条件为:制单,物料号,型号,颜色四个,而又不能唯一,所以导致统计性能下降.这个执行要15秒左右.有办法优化吗?
select distinct a.MakFormID,a.MatType,a.MatCode,a.MatName,a.spec,a.colorname,
Amount=(select isnull(sum(c.amount),0) from vMatIn c where (c.makformid=a.makformid or c.FromMakFormID=a.MakFormID) and c.ischeck=1 and c.Matcode=a.Matcode and a.spec=c.spec and a.colorname=c.colorname),
a.Unitname
from vMatIn a
where
(a.MatCode like '%'+@Matcode+'%' or a.MatName like '%'+@Matcode+'%') and a.ClientName like '%'+@ClientName+'%' and
a.MakFormID+a.MatCode+a.spec+a.ColorName not in
(select distinct b.Compose from vMatIn_Compose b where b.theDate>@NDate and (b.MatCode like '%'+@Matcode+'%' or b.MatName like '%'+@Matcode+'%') and b.Mattypeid in (select Mattypeid from fALLMatTypeID(@MatType))) --求包含的子类别
order by a.MatCode
由于关键字条件为:制单,物料号,型号,颜色四个,而又不能唯一,所以导致统计性能下降.这个执行要15秒左右.有办法优化吗?