kis专业版13.0组装核算的问题
金蝶云社区-huoxian
huoxian
0人赞赏了该文章 715次浏览 未经作者许可,禁止转载编辑于2016年07月21日 13:21:18

kis专业版13.0组装核算的问题:客户的入出库单据目前已经达到230000多张的,最多的单据类型是组装单,每月大概超过1500张,现在遇到的问题是在进行组装核算时(哪怕是选择2-3天的数据)都会出现核算卡死的情况。我们跟踪了一下客户的数据,发现客户的数据库icstockbill 表的数量有230000条以上的数量,icstockbillentry表数量达到了1百万条记录,跟踪到的核算的语句是:
UPDATE v1 SET
FPrice=cast((ROUND(v2.FAmount,2)+ROUND(t5.Ffee*FEXChangeRate,2)) as decimal(23,10))/v1.FQty,
FAmount=(ROUND(v2.FAmount,2)+ROUND(t5.Ffee*FEXChangeRate,2)),
v1.FAuxPrice=cast((isnull(t4.FCoefficient,1)+isnull(t4.FScale,0))*(ROUND(v2.FAmount,2)+ROUND(t5.Ffee*FEXChangeRate,2)) as decimal(23,10))/v1.FQty
From icstockbill u1
INNER JOIN icstockbillentry v1 ON u1.FinterID=v1.FinterID
INNER JOIN (SELECT Sum(t2.FAmount) as FAmount,Max(t2.FSourceInterID) AS FSourceInterID
FROM icstockbill t1
INNER JOIN icstockbillentry t2
ON t1.FinterID=t2.FinterID AND t1.Ftrantype=29 AND t1.FBillTypeID=3
AND t1.FDate>='2016-06-01' AND t1.FDate<'2016-07-01'
GROUP BY FSourceInterID )v2
ON v1.FSourceInterID=v2.FSourceInterID
INNER JOIN t_icitem t3 ON v1.Fitemid=t3.fitemid
INNER JOIN t_measureunit t4 ON t4.FItemID=v1.FUnitID
INNER JOIN ICCHange t5 ON (v1.FSourceInterID=t5.FID AND t5.FClassTypeID=1007000)
WHERE u1.FinterID IN(222632,222634,222636,222639,222641,......)
AND v1.FQty>0 AND v2.FAmount>0

(WHERE u1.FinterID IN(222632,222634,222636,222639,222641,......)这里的内码号每月有15000个以上,这里精简了)。
这样运行,速度会非常慢,基本就是卡死。如果将WHERE u1.FinterID IN(222632,222634,222636,222639,222641,......)
AND v1.FQty>0 AND v2.FAmount>0 调整为WHERE v1.FinterID IN(222632,222634,222636,222639,222641,......)
AND v1.FQty>0 AND v2.FAmount>0 核算就很快。请金蝶开发人员分析一下原因,并给以解答!!!