寻求性能高手
ALTER PROCEDURE [dbo].[up_PP_AddCuringInfo]@TyreNO CHAR(10),--胎号@GreenTyreNo CHAR(10),--成型工号@MaterialID CHAR(9),--物料编号@LeftOrRight CHAR(1),--左右模标志@BeginTime VARCHAR(20),--硫化开始时间@MachineID VARCHAR(7),--机台编号@IDinFac varchar(20),--机台名称@DayShiftName VARCHAR(4),--班次@MouldID VARCHAR(10),--模具编号@WorkerID VARCHAR(20),--操作工人名@ShiftNo VARCHAR(16),--接班编号@GroupName VARCHAR(10)--班组ASSET NOCOUNT ONDECLARE @GroupID VARCHAR(2)--班组编号DECLARE @TempTyreNO varchar(10)--胎号临时变量DECLARE @TempGreenTyreNo varchar(15)--成型工号临时变量DECLARE @SizeID varchar(10)--规格DECLARE @PlyRatingID varchar(10)--层级DECLARE @PatternID varchar(10)--花纹DECLARE @BrandID varchar(10)--品牌DECLARE @StandardID varchar(10)--标准DECLARE @AttributeID varchar(10)--属性DECLARE @SizeName varchar(30)--规格DECLARE @PlyRatingName varchar(30)--层级DECLARE @PatternName varchar(30)--花纹DECLARE @BrandName varchar(30)--品牌DECLARE @StandardName varchar(30)--标准DECLARE @AttributeName varchar(30)--属性DECLARE @MATERIALCODE varchar(30)--物料编码DECLARE @PLANID VARCHAR(16)--计划编号DECLARE @PLANDETAILID VARCHAR(2)--计划明细编号DECLARE @DayShiftID VARCHAR(2)DECLARE @GreenTyreMaterialID VARCHAR(20)--胎胚物料编号DECLARE @GreenTyreMaterialCode VARCHAR(80)--胎胚物料名称SET @DayShiftID=CASE @DayShiftName WHEN '早' THEN '01' WHEN '中' THEN '02' WHEN '夜' THEN '03' END--得到班组编号SELECT @GroupID=GroupIDFROM TB_HR_GroupWHERE GroupName=@GroupName--得到品牌、规格、层级、花纹、标准、属性SELECT @SizeID=T1.SizeID, @SizeName=T1.SizeName, @PlyRatingID=T1.PlyRatingID, @PlyRatingName=T1.PlyRatingName, @PatternID=T1.PatternID, @PatternName=T1.PatternName, @BrandID=T1.BrandID, @BrandName=T1.BrandName, @StandardID=T1.StandardID, @StandardName=T1.StandardName, @AttributeID=T1.AttributeID, @AttributeName=T1.AttributeName, @MATERIALCODE=T1.MATERIALCODEFROM V_MaterialCode T1WHERE MaterialID=@MaterialID--获得计划编号SELECT @PLANID=SUBSTRING(@ShiftNo,8,6)+SUBSTRING(@ShiftNo,1,7)+SUBSTRING(@ShiftNo,14,2)+'0'DECLARE @planCount intSELECT @planCount=COUNT(1) FROM TB_PP_PLAN WHERE PlanID=@PLANIDIF(@planCount<=0)--如果计划表里没有该计划,则创建BEGIN INSERT tb_PP_Plan(PlanID,PlanDate,PlanShift,MachineID,PlanState,RecorderID,RecordTime) VALUES(@PLANID,GETDATE(),@DayShiftID,@MachineID,'2',@WorkerID,GETDATE())ENDSELECT TOP 1 @PLANDETAILID=ISNULL(PLANDETAILID,'') FROM TB_PP_PLANDETAIL WHERE PLANID=@PLANID AND MATERIALID=@MaterialID AND PLANSTATE='2'IF(@PLANDETAILID='')BEGIN SELECT @PLANDETAILID='0'+ISNULL(CAST(CAST(MAX(PlanDetailID) AS INT)+1 AS VARCHAR),'1') FROM tb_PP_PlanDetail WHERE planid=@PLANID INSERT TB_PP_PLANDETAIL(PlanID,PlanDetailID,MaterialID,PlanAmount,PlanState,PlanFlag,Remark,RecorderID,RecordTime,GroupID,RealAmount) VALUES(@PLANID,@PLANDETAILID,@MaterialID,200,'2','2','系统自动添加',@WorkerID,GETDATE(),@GroupID,0)END--获得胎胚物料SELECT @GreenTyreMaterialID=T1.MaterialID,@GreenTyreMaterialCode=T2.MaterialCode FROM tb_PP_MouldingOutput T1 LEFT JOIN tb_TE_Material T2 ON T1.MaterialID=T2.MaterialIDWHERE GreenTyreNo = @TyreNoSET XACT_ABORT ONBEGIN TRAN T1 --添加胎胚出库信息 UPDATE tb_PP_MouldingOutput SET OutFlag='1' WHERE GreenTyreNo=@TyreNo --更新胎胚库存表 UPDATE tb_PP_MouldReMainInfo SET Amount=Amount-1 WHERE MaterialID=@GreenTyreMaterialID UPDATE tb_SY_tyrestate SET spec=@SizeID, SpecName=@SizeName, layer=@PlyRatingID, layerName=@PlyRatingName, texture=@PatternID, textureName=@PatternName, Brand=@BrandID, brandName=@BrandName, [standard]=@StandardID, standardname=@StandardName, attribute=@AttributeID, attributename=@AttributeName, MaterialID=@MaterialID, MaterialName=@MATERIALCODE, SulfBeginTime=@BeginTime, SulfEndTime=@BeginTime, SulfEquipCode=@IDinFac, SulfShift=@DayShiftName, SulfTeam=@GroupName, sulfcarrayshift=@shiftno, SulfOper=@WorkerID, sulfpattern=@MouldID, TyreState='05'--硫化未质检 WHERE TyreId=@TyreNO--存在则更新 IF @@rowcount=0 BEGIN INSERT INTO tb_SY_tyrestate(spec, layer, Brand, texture, [standard], attribute, SpecName, layerName, textureName, brandName, standardname, attributename, TyreID, GreenTyreId, MaterialID, MaterialName, SulfBeginTime, SulfEndTime, SulfEquipCode, SulfShift,SulfTeam,sulfcarrayshift, SulfOper, sulfpattern, TyreState) VALUES(@SizeID, @PlyRatingID, @BrandID, @PatternID, @StandardID, @AttributeID, @SizeName, @PlyRatingName, @PatternName, @BrandName, @StandardName, @AttributeName, @TyreNO, @GreenTyreNO, @MaterialID, @MATERIALCODE, @BeginTime, @BeginTime, @IDinFac, @DayShiftName,@GroupName,@shiftno, @WorkerID, @MouldID, '05') END INSERT INTO tb_PP_CuringOutput(TyreNo, ShiftNo, GreenTyreNO, MaterialID, PlanNO, PlanDetailNo, BeginTime, EndTime, MouldID, LeftOrRight,SulfEquipCode, SulfOper,SulfShift,SulfTeam) VALUES(@TyreNO, @ShiftNo, @GreenTyreNo, @MaterialID, @PLANID, @PLANDETAILID, @BeginTime,@BeginTime, @MouldID, @LeftOrRight, @IDinFac, @WorkerID,@DayShiftName,@GroupName) --更新生产计划表，实际完成数 IF(@PLANDETAILID<>'') BEGIN UPDATE TB_PP_PLANDETAIL SET REALAMOUNT = ISNULL(REALAMOUNT+1,1), GROUPID=(CASE ISNULL(REALAMOUNT+1,1) WHEN 1 THEN @GroupID ELSE GROUPID END) WHERE PLANID=@PLANID AND PLANDETAILID=@PLANDETAILID END --更新机台状态表 UPDATE tb_PP_MachineCurrentInfo SET TyreNo=@TyreNo ,CapsuleCount=CapsuleCount+1 WHERE MachineID=@MachineID AND LeftOrRight=@LeftOrRightIF @@ERROR<>0BEGIN ROLLBACK TRAN T1ENDELSEBEGIN COMMIT TRAN T1END
SELECT TOP 1 @PLANDETAILID=ISNULL(PLANDETAILID,'') FROM TB_PP_PLANDETAIL WHERE PLANID=@PLANID AND MATERIALID=@MaterialID AND PLANSTATE='2'[color=#FF0000]IF(@PLANDETAILID='')[/color]BEGIN 。。。。END
[解决办法]
楼主最近可好！
[解决办法]
1.看下IO情况，如果有较大的逻辑读，看是否有适合的索引.
SET STATISTICS IO ON
EXEC up_PP_AddCuringInfo
2.慢，也有可能是资源等待，比如更新或查询有，有其它事务在更新。
3.SET XACT_ABORT ON时，印象中捕捉不到@@error，那么下面的是否有用？
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN T1
END
[解决办法]
UPDATE tb_SY_tyrestate SET spec=@SizeID, SpecName=@SizeName, layer=@PlyRatingID, layerName=@PlyRatingName, texture=@PatternID, textureName=@PatternName, Brand=@BrandID, brandName=@BrandName, [standard]=@StandardID, standardname=@StandardName, attribute=@AttributeID, attributename=@AttributeName, MaterialID=@MaterialID, MaterialName=@MATERIALCODE, SulfBeginTime=@BeginTime, SulfEndTime=@BeginTime, SulfEquipCode=@IDinFac, SulfShift=@DayShiftName, SulfTeam=@GroupName, sulfcarrayshift=@shiftno, SulfOper=@WorkerID, sulfpattern=@MouldID, TyreState='05'--硫化未质检 WHERE TyreId=@TyreNO--存在则更新 IF @@rowcount=0 BEGIN INSERT INTO tb_SY_tyrestate(spec, layer, Brand, texture, [standard], attribute, SpecName, layerName, textureName, brandName, standardname, attributename, TyreID, GreenTyreId, MaterialID, MaterialName, SulfBeginTime, SulfEndTime, SulfEquipCode, SulfShift,SulfTeam,sulfcarrayshift, SulfOper, sulfpattern, TyreState) VALUES(@SizeID, @PlyRatingID, @BrandID, @PatternID, @StandardID, @AttributeID, @SizeName, @PlyRatingName, @PatternName, @BrandName, @StandardName, @AttributeName, @TyreNO, @GreenTyreNO, @MaterialID, @MATERIALCODE, @BeginTime, @BeginTime, @IDinFac, @DayShiftName,@GroupName,@shiftno, @WorkerID, @MouldID, '05') END
[解决办法]
