有没有办法在Sp内部优化此存储过程或查询 [英] Is there any way to Optimize this Store Procedure or Queries inside Sp

问题描述

我有以下商店程序，我想知道有什么方法可以优化商店程序中的查询，节省时间和效率。

I have the following store procedure, i wanted to know that is there any way to optimize the queries in the Store Procedure in terms of saving time and efficiency.

CREATE PROC uspSetSubscriptionPayment
@PayMethod varchar(50), @PackageType Varchar(25),@Userid int,@ConstID int,
@SubscriptionIdentity int OUT,@PaymentIdentity int out,@CurrentSubscriptionid int
AS
BEGIN
IF (@CurrentSubscriptionid = 0)
BEGIN
INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
VALUES(@Userid,(SELECT PackageID From TblPackages Where Category = @PackageType),@ConstID,
GETDATE(),GETDATE()+30,(SELECT MaxNotification From TblPackages Where Category = @PackageType),
(SELECT MaxVideos From TblPackages Where Category = @PackageType),
(SELECT MaxArticles From TblPackages Where Category = @PackageType),
(SELECT MaxEvent From TblPackages Where Category = @PackageType),
(SELECT MaxAllias From TblPackages Where Category = @PackageType),
(SELECT Cost From TblPackages Where Category = @PackageType),1,1)
SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
VALUES(@SubscriptionIdentity,(SELECT Cost From TblPackages Where Category = @PackageType),GETDATE(),@PayMethod,1)
SELECT @PaymentIdentity = SCOPE_IDENTITY()
END 
ELSE
BEGIN
UPDATE TblSubscription SET IsActive = 0 WHERE SubscriptionId =@CurrentSubscriptionid
INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
VALUES(@Userid,(SELECT PackageID From TblPackages Where Category = @PackageType),@ConstID,
GETDATE(),GETDATE()+30,(SELECT MaxNotification From TblPackages Where Category = @PackageType) + (SELECT MaxNotification From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxVideos From TblPackages Where Category = @PackageType) + (SELECT MaxArticles From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxArticles From TblPackages Where Category = @PackageType) + (SELECT MaxVideos From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxEvent From TblPackages Where Category = @PackageType) + (SELECT MaxEvent From TblSubscription Where SubscriptionId =@CurrentSubscriptionid),
(SELECT MaxAllias From TblPackages Where Category = @PackageType),
(SELECT Cost From TblPackages Where Category = @PackageType),1,1)
SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
VALUES(@SubscriptionIdentity,(SELECT Cost From TblPackages Where Category = @PackageType),GETDATE(),@PayMethod,1)
SELECT @PaymentIdentity = SCOPE_IDENTITY()
END
END

推荐答案

你可以开始这样的事情：



Btw，核心编写程序是逻辑结构而不是实际语法。

You could start something like this:

Btw, the core of writing a program is logical structure than the actual syntax.

CREATE PROC uspSetSubscriptionPayment
	@PayMethod varchar(50), 
	@PackageType varchar(25),
	@Userid int,
	@ConstID int,
	@SubscriptionIdentity int OUT,
	@PaymentIdentity int out
AS
BEGIN
	SELECT * INTO #Temp FROM TblPackages WHERE Category = @PackageType 
	
	INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, Cost, IsActive, IsPaid)
	SELECT @Userid, PackageID, @ConstID, GETDATE(), GETDATE() + 30, MaxNotification, MaxVideos, MaxArticles, MaxEvent, MaxAllias, Cost, ?, ?) FROM #Temp
	
	SELECT @SubscriptionIdentity = SCOPE_IDENTITY()
	INSERT INTO TblPayment (SubscriptionId, Amount, PayDate, PayMethod, PayStatus)
	VALUES(@SubscriptionIdentity,(SELECT Cost FROM #Temp),GETDATE(), @PayMethod,1)
END 

这篇关于有没有办法在Sp内部优化此存储过程或查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！