Rewrite the code for performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Rewrite the code for performance

Hi, I have a stored procedure that I inherited which when run takes more than 8 mts to run. The indexes are in place but still seems to take time. is there any way that the following code can be rewritten and optimised?. Any help is appreciated. CREATE PROCEDURE dbo.spSPIFInsClaimDetail_New (
@ClaimID int
, @BundleID int
, @PromotionID INT
, @ClaimInvoiceID INT
, @EnteredQty int
)
AS SET NOCOUNT ON DECLARE @ClaimDetailID int
, @EntryDetail nvarchar(255)
, @ClaimDetailCount int
, @ProductCategoryID int
, @ProductCategoryMaxQuantity int
, @CurrentCategoryQuantity int
, @PromotionTypeID int
, @CurrentTotalQuantity int
, @BundleStartDate DATETIME
, @BundleEndDate DATETIME
, @InvoiceStartDate DATETIME
, @ValidBundle TINYINT
, @BundleMaxQtyPerClaim INT
, @BundleMaxQtyPerUser INT
, @ClaimDetailProdStatus INT–added kljungberg 6/19/2003
, @ClaimDetailStatus INT –added kljungberg 6/19/2003
, @MaxDollarAmtPerPromotion INT
, @TotalBenefitsClaimedToDate DECIMAL
, @MaxDollarExceeded INT SET @MaxDollarExceeded = 1
SELECT @BundleStartDate = InvoiceBeginDate, @BundleEndDate = InvoiceEndDate
FROM tblPromotionBundles
WHERE (BundleID = @BundleID) AND (PromotionID = @PromotionID) SELECT @InvoiceStartDate = InvoiceDate
FROM tblClaimInvoice
WHERE ClaimInvoiceID = @ClaimInvoiceID IF (@InvoiceStartDate BETWEEN @BundleStartDate AND @BundleEndDate)
BEGIN
SET @ValidBundle = 1
END
ELSE
BEGIN
SET @ValidBundle = 0
END SELECT @ProductCategoryID = P.ProductCategoryID
FROM tblBundleProducts BP
INNER JOIN tblProduct P
ON BP.ProductID = P.ProductID
INNER JOIN tblLUProductCategory LPC –added join to LPC kljungberg 6/9/2003
ON P.ProductCategoryID = LPC.ProductCategoryID
WHERE BP.BundleID = @BundleID AND LPC.Status = 1 EXEC @ProductCategoryMaxQuantity = fnGetProductCategoryMaxQuantity @PromotionID, @ProductCategoryID
–EXEC @BundleMaxQtyPerClaim = fnGetUserBundleUnitCap @PromotionID,@BundleID –replaced kljungberg 7/17/2003
EXEC @BundleMaxQtyPerClaim = fnGetUserBundleUnitCapPerClaim @PromotionID,@BundleID
EXEC @BundleMaxQtyPerUser = fnGetUserBundleUnitCapAllClaims @PromotionID,@BundleID
/********************Break the bank validation added kljungberg 9/15/2003*************************************************************************/
/* First check to see if an attribute was set for max dollar per promotion*/
EXEC @MaxDollarAmtPerPromotion = fnGetMaxDollarAmountPerPromotion @PromotionID
/*next see what is the total benefit amount already claimed to date*/
SELECT @TotalBenefitsClaimedToDate = TotalClaimedBenefits
FROM vwSPIFTotalClaimedBenefitsByPromotion
WHERE (PromotionID = @PromotionID) SELECT @PromotionTypeID = PromotionTypeID FROM tblPromotion WHERE PromotionID = @PromotionID IF @PromotionTypeID = 0 –Reseller Sales Rep
BEGIN
SELECT @CurrentTotalQuantity = SUM(CD.EnteredQty)
FROM tblClaimDetail CD
INNER JOIN tblClaim C
ON CD.ClaimID = C.ClaimID
WHERE C.ClaimID IN (
SELECT C1.ClaimID
FROM tblClaim C1
WHERE C1.UserID IN (
SELECT U.UserID
FROM tblUser U
WHERE OutletID = (
SELECT OutletID
FROM tblUser
WHERE UserID = (
SELECT C2.UserID
FROM tblClaim C2
WHERE C2.ClaimID = @ClaimID
AND C2.PromotionID = @PromotionID
)
)
)
AND C1.StatusID NOT IN (10, 11) –Cancelled, Expired
AND C1.PromotionID = @PromotionID
)
AND CD.StatusID <> 15 –Deleted
AND C.StatusID NOT IN (10, 11) –Cancelled, Expired
AND C.PromotionID = @PromotionID
END IF @PromotionTypeID = 1 –End-User
BEGIN SELECT @CurrentTotalQuantity = SUM(CD.EnteredQty)
FROM tblClaimDetail CD
INNER JOIN tblClaim C
ON CD.ClaimID = C.ClaimID
WHERE C.ClaimID = @ClaimID
AND C.PromotionID = @PromotionID
AND C.StatusID NOT IN (10, 11) –Cancelled, Expired
AND CD.StatusID <> 15 –Deleted
AND C.StatusID NOT IN (10, 11) –Cancelled, Expired
AND CD.ClaimInvoiceID = @ClaimInvoiceID
AND CD.BundleID = @BundleID
END IF @EnteredQty + @CurrentTotalQuantity > @ProductCategoryMaxQuantity
BEGIN
SELECT @EnteredQty = 0 –This will cause error message
END IF @EnteredQty + @CurrentTotalQuantity > @BundleMaxQtyPerClaim
BEGIN
SELECT @EnteredQty = 0
END
ELSE IF @EnteredQty > @BundleMaxQtyPerClaim
BEGIN
SELECT @EnteredQty = 0
END IF ISNULL(@BundleMaxQtyPerUser,0) > 0
BEGIN
IF @EnteredQty + ISNULL(@CurrentTotalQuantity,0) > @BundleMaxQtyPerUser
BEGIN
SELECT @EnteredQty = 0
END
END IF ISNULL(@MaxDollarAmtPerPromotion,0) > 0
BEGIN
IF @TotalBenefitsClaimedToDate >= @MaxDollarAmtPerPromotion
BEGIN
SELECT @EnteredQty = 0
SELECT @MaxDollarExceeded = 0
END
END IF @ValidBundle > 0 AND @MaxDollarExceeded > 0
BEGIN
IF @EnteredQty > 0
BEGIN
INSERT INTO tblClaimDetail (
ClaimID
, BundleID
, ClaimInvoiceID
, EnteredQty
, StatusID
)
VALUES (
@ClaimID
, @BundleID
, @ClaimInvoiceID
, @EnteredQty
, 14 –Inserted
)
END
ELSE
BEGIN
INSERT INTO tblClaimDetail (
ClaimID
, BundleID
, ClaimInvoiceID
, EnteredQty
, StatusID
)
VALUES (
@ClaimID
, @BundleID
, @ClaimInvoiceID
, @EnteredQty
, 15 –Deleted )
END SELECT @ClaimDetailID = @@IDENTITY SELECT @EntryDetail = ‘Claim detail inserted, ClaimDetailID = ‘ + CONVERT(varchar(10), @ClaimDetailID) EXEC spInsStatusHistory 14, 4, @ClaimDetailID, @EntryDetail –Inserted, ClaimDetail
SET @ClaimDetailStatus = 14
–Create claim detail prod entries for each entered qty
SELECT @ClaimDetailCount = 1 CREATE TABLE #tmpClaimDetail(ClaimDetailID INT, BundleID INT, StatusID INT) WHILE (@ClaimDetailCount <= @EnteredQty)
BEGIN
–EXEC spInsUpdClaimDetailBundle @[email protected], @[email protected], @StatusID=30 –inserted
INSERT #tmpClaimDetail(ClaimDetailID, BundleID,StatusID )
VALUES(@ClaimDetailID, @BundleID, 30) SET @ClaimDetailCount = @ClaimDetailCount + 1
END INSERT tblClaimDetailBundle (ClaimDetailID
,BundleID
,StatusID
,InsertDate
,InsertUser
,UpdateDate
,UpdateUser
)
SELECT ClaimDetailID
, BundleID
, StatusID
, getdate()
, user
, getdate()
, user
FROM #tmpClaimDetail DROP TABLE #tmpClaimDetail EXEC @ClaimDetailProdStatus = spSPIFInsClaimDetailProdNew @ClaimDetailID, @BundleID IF @ClaimDetailProdStatus = 24
BEGIN
EXEC spUpdClaimDetailStatus @ClaimDetailID = @ClaimDetailID , @StatusID = 22
SET @ClaimDetailStatus = 22
END
END SELECT @ClaimDetailID AS ClaimDetailID, @EnteredQty AS EnteredQty, @ClaimDetailStatus AS ClaimDetailStatus, @MaxDollarExceeded AS MaxDollarExceeded
SET NOCOUNT OFF
Sagar
Have you looked at the execution plan or profiler to see what parts taking the longest to run and has the greatest cost on the query plan? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
The first part seems to be this statement:<br /><br />SELECT @TotalBenefitsClaimedToDate = TotalClaimedBenefits<br />FROM vwSPIFTotalClaimedBenefitsByPromotion<br />WHERE (PromotionID = @PromotionID)<br /><br />and here is the code in the view:<br /><br />SELECT TOP 100 PERCENT dbo.tblClaim.PromotionID, SUM(CONVERT(MONEY, dbo.tblBenefitItem.Amount)) AS TotalClaimedBenefits<br />FROM dbo.tblClaim INNER JOIN<br /> dbo.tblClaimBenefit ON dbo.tblClaim.ClaimID = dbo.tblClaimBenefit.ClaimID INNER JOIN<br /> dbo.tblBenefitItem ON dbo.tblClaimBenefit.BenefitItemID = dbo.tblBenefitItem.BenefitItemID INNER JOIN<br /> dbo.tblProgramPromotions ON dbo.tblClaim.PromotionID = dbo.tblProgramPromotions.PromotionID INNER JOIN<br /> dbo.tblClaimDetailProd ON dbo.tblClaimBenefit.ClaimDetailProdID = dbo.tblClaimDetailProd.ClaimDetailProdID<br />WHERE (dbo.tblProgramPromotions.ProgramID = 2) AND (dbo.tblClaimDetailProd.StatusID = 24) AND (dbo.tblClaim.StatusID IN (4, 7, 8, 9, 2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)<br />GROUP BY dbo.tblClaim.PromotionID<br />ORDER BY dbo.tblClaim.PromotionID<br /><br /><br />Sagar
]]>