进牛人优化下SQL存储过程
合成某个卡片，数据库中设置了多个合成材料，这个材料有可能1个，也有可最多是13个，我现在是一个个逐一判断，有点傻瓜，是否可以优化？求高手帮忙。
ALTER PROCEDURE [dbo].[Usp_Prize_Mixture_Result]--卡片合成结果
@IDINT,--ID
@CodeINT,--编码
@Type INT--类型(25，50，75，100)
AS
BEGIN
BEGIN TRAN Tran_MixturePrize
--检测VIP会员
DECLARE @VIP INT
SELECT @VIP = F_VIP FROM T_Prize_Mixture WHERE K_Code = @Code
IF @VIP = 1
BEGIN
IF NOT EXISTS(SELECT * FROM T_User_VIP WHERE K_ID=@ID)
BEGIN
SELECT 2
RETURN
END
END
DECLARE @PropCode int
DECLARE @Num int
DECLARE @Money1 int
DECLARE @Money2 int
DECLARE @Money3 int
DECLARE @Money4 int
SELECT @PropCode = F_Code, @Num = F_Num,@Money1 = F_Money1,
@Money2 = F_Money2,@Money3 = F_Money3,@Money4 = F_Money4
FROM T_Prize_Mixture WHERE K_Code = @Code
--检测特殊道具
IF @PropCode > 0 AND @Num > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @PropCode
AND F_Quantity >= @Num)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 3
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@PropCode,@Num
END
END
DECLARE @Money int
IF @Type = 25
BEGIN
set @Money = @Money1
END
IF @Type = 50
BEGIN
set @Money = @Money2
END
IF @Type = 75
BEGIN
set @Money = @Money3
END
IF @Type = 100
BEGIN
set @Money = @Money4
END
--检测钱
DECLARE @sign0 INT
EXECUTE [Usp_User_UpdateYMoney_Public] @ID,@Money,0,@sign0 out
IF @sign0 = 0
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 4
RETURN
END
--逐一检测合成材料是否足够
IF @Code = 13305 --生日卡(1986/12/30)--11501[0]--12302[2]--12309[9]--12314[A/1]
BEGIN
--declare @today varchar(30)
--set @today = CONVERT(varchar(30),GETDATE(),111)
--declare @Num1 varchar(1)
--declare @Num2 varchar(1)
--declare @Num3 varchar(1)
--declare @Num4 varchar(1)
--declare @Num5 varchar(1)
--declare @Num6 varchar(1)
--declare @Num7 varchar(1)
--declare @Num8 varchar(1)
--set @Num1 = substring(@today,1,2)
--set @Num2 = substring(@today,2,3)
--set @Num3 = substring(@today,3,4)
--set @Num4 = substring(@today,4,5)
--set @Num5 = substring(@today,6,7)
--set @Num6 = substring(@today,7,8)
--set @Num7 = substring(@today,9,10)
--set @Num8 = substring(@today,10,11)
--DECLARE @Code1 int
--SELECT * FROM T_Prize_UserTicket WHERE
--F_ID = @ID AND F_Quantity > 0 AND F_Code = @Code1
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
DECLARE @MixCode1 int
DECLARE @MixNum1 int
DECLARE @MixCode2 int
DECLARE @MixNum2 int
DECLARE @MixCode3 int
DECLARE @MixNum3 int
DECLARE @MixCode4 int
DECLARE @MixNum4 int
DECLARE @MixCode5 int
DECLARE @MixNum5 int
DECLARE @MixCode6 int
DECLARE @MixNum6 int
DECLARE @MixCode7 int
DECLARE @MixNum7 int
DECLARE @MixCode8 int
DECLARE @MixNum8 int
DECLARE @MixCode9 int
DECLARE @MixNum9 int
DECLARE @MixCode10 int
DECLARE @MixNum10 int
DECLARE @MixCode11 int
DECLARE @MixNum11 int
DECLARE @MixCode12 int
DECLARE @MixNum12 int
DECLARE @MixCode13 int
DECLARE @MixNum13 int
SELECT @MixCode1=F_Code1,@MixNum1=F_Num1,@MixCode2=F_Code2,@MixNum2=F_Num2,
@MixCode3=F_Code3,@MixNum3=F_Num3,@MixCode4=F_Code4,@MixNum4=F_Num4,
@MixCode5=F_Code5,@MixNum5=F_Num5,@MixCode6=F_Code6,@MixNum6=F_Num6,
@MixCode7=F_Code7,@MixNum7=F_Num7,@MixCode8=F_Code8,@MixNum8=F_Num8,
@MixCode9=F_Code9,@MixNum9=F_Num9,@MixCode10=F_Code10,@MixNum10=F_Num10,
@MixCode11=F_Code11,@MixNum11=F_Num11,@MixCode12=F_Code12,@MixNum12=F_Num12,
@MixCode13=F_Code13,@MixNum13=F_Num13 FROM T_Prize_Mixture WHERE K_Code = @Code
IF @MixCode1 > 0 AND @MixNum1 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode1
AND F_Quantity >= @MixNum1)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode1,@MixNum1
END
END
IF @MixCode2 > 0 AND @MixNum2 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode2
AND F_Quantity >= @MixNum2)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode2,@MixNum2
END
END
IF @MixCode3 > 0 AND @MixNum3 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode3
AND F_Quantity >= @MixNum3)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode3,@MixNum3
END
END
IF @MixCode4 > 0 AND @MixNum4 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode4
AND F_Quantity >= @MixNum4)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode4,@MixNum4
END
END
IF @MixCode5 > 0 AND @MixNum5 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode5
AND F_Quantity >= @MixNum5)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode5,@MixNum5
END
END
IF @MixCode6 > 0 AND @MixNum6 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode6
AND F_Quantity >= @MixNum6)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode6,@MixNum6
END
END
IF @MixCode7 > 0 AND @MixNum7 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode7
AND F_Quantity >= @MixNum7)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode7,@MixNum7
END
END
IF @MixCode8 > 0 AND @MixNum8 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode8
AND F_Quantity >= @MixNum8)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode8,@MixNum8
END
END
IF @MixCode9 > 0 AND @MixNum9 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode9
AND F_Quantity >= @MixNum9)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode9,@MixNum9
END
END
IF @MixCode10 > 0 AND @MixNum10 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode10
AND F_Quantity >= @MixNum10)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode10,@MixNum10
END
END
IF @MixCode11 > 0 AND @MixNum11 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode11
AND F_Quantity >= @MixNum11)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode11,@MixNum11
END
END
IF @MixCode12 > 0 AND @MixNum12 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode12
AND F_Quantity >= @MixNum12)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode12,@MixNum12
END
END
IF @MixCode13 > 0 AND @MixNum13 > 0
BEGIN
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket
WHERE F_ID = @ID
AND F_Code = @MixCode13
AND F_Quantity >= @MixNum13)
BEGIN
ROLLBACK TRAN Tran_MixturePrize
SELECT 5
RETURN
END
ELSE
BEGIN
EXECUTE [Usp_Prize_UsePrize] @ID,@MixCode13,@MixNum13
END
END
END
IF NOT EXISTS(SELECT * FROM T_Prize_UserTicket WHERE F_ID = @ID AND F_Code = @Code)
BEGIN
INSERT T_Prize_UserTicket(F_ID,F_Code,F_Quantity,F_Point)
VALUES(@ID,@Code,1,0)
END
ELSE
BEGIN
SET NOCOUNT ON
UPDATE T_Prize_UserTicket
SET F_Quantity = F_Quantity + 1
WHERE F_ID = @ID
AND F_Code = @Code
SET NOCOUNT OFF
END
COMMIT TRAN Tran_MixturePrize
SELECT 1
END
[解决办法]
这么长谁跟你优化啊。而且你的事务包得太多了，建议考虑是否有必要对SELECT操作也做事务控制，一般对增、删、改就够了，除非并发量非常大而且数据要求非常准确。否则没必要对select做事务，不然你的程序运行过久的话容易出现阻塞。
另外，考虑一下你的程序中是否有一些比较独立的功能可以单独成为一个存储过程或者函数，这样可读性会好很多。另外，如果运行非常慢，就先看看执行计划中耗时最大的是哪部分，再单独优化。
[解决办法]
代码太乱了
要缩进啊，该换行的换行，不用没有什么意义的注释删除掉，不然会累死后面看的人
一般只在插入、更新、删除等操作才需要事务提交，否则一般没有必要
[解决办法]
每一项检测都可以写成一个单独的存储过程。
如果有测试数据，就很容易找出来哪里错误。
粗看了一下，逻辑和语法上可以优化的地方有不少，具体还要看程序需要，目前不好评价。
[解决办法]
对SELECT操作没必要做事务控制,优化的话 这个涉及到多次重复判断，可以考虑把独立的功能提取出来。还有写法也可以考虑精简替换
