none
need help to optimize stored procedure

    Question

  • Dear Friends and fellows

    i have this stored procedure but i just want to know is this is optimize one if no the please can you give any idea.

    Alter PROCEDURE Sp_CustomerInv_RelatedServiceDetail_Load
    (
    @Svc_Number int 
    )
    AS
    BEGIN
    	Declare @AccCount int;
    	SELECT @AccCount = COUNT(A.Account_ID)
    	FROM TblSvc S INNER JOIN TblOrder O ON O.Order_Svc_ID = S.SvcID
    				  INNER JOIN tblCONTACT C ON C.ID = O.Order_Billing_ID
    				  INNER JOIN TblAccounts A ON A.Contact_ID = C.ID
    	WHERE   (S.Svc_Number = @Svc_Number) --AND (A.Suspend_Invoices = 0 OR A.Suspend_Invoices = NULL) AND (A.AccDisabled = 0 OR A.AccDisabled = NULL)
    	IF(@AccCount > 1)
    		BEGIN
    				SELECT A.Account_ID,A.ID,A.AccountName,O.Order_Media_Ref,C_2.ID, C_2.NAME
    				FROM TblSvc S INNER JOIN TblOrder O ON O.Order_Svc_ID = S.SvcID
    							  INNER JOIN tblCONTACT C_1 ON C_1.ID = O.Order_Billing_ID
    							  INNER JOIN tblCONTACT C_2	ON C_2.ID = O.Order_Operator_ID
    							  INNER JOIN TblAccounts A ON A.Contact_ID = C_1.ID
    				WHERE (S.Svc_Number = @Svc_Number) 
    				AND (A.Currency_ID = dbo.GetMainCurrencyId() OR A.Account_ID = (SELECT TOP(1) ACC.ID FROM TblAccounts ACC WHERE ACC.Contact_ID = C_1.ID ORDER BY ACC.Account_ID))
    				--AND (A.Suspend_Invoices = 0 OR A.Suspend_Invoices = NULL) AND (A.AccDisabled = 0 OR A.AccDisabled = NULL) 
    		END
    	ELSE
    		BEGIN
    				SELECT A.Account_ID,A.ID,A.AccountName,O.Order_Media_Ref,C_2.ID, C_2.NAME
    				FROM TblSvc S INNER JOIN TblOrder O ON O.Order_Svc_ID = S.SvcID
    							  INNER JOIN tblCONTACT C_1 ON C_1.ID = O.Order_Billing_ID
    							  INNER JOIN tblCONTACT C_2	ON C_2.ID = O.Order_Operator_ID
    							  INNER JOIN TblAccounts A ON A.Contact_ID = C_1.ID
    				WHERE (S.Svc_Number = @Svc_Number) 
    				--AND (A.Suspend_Invoices = 0 OR A.Suspend_Invoices = NULL) AND (A.AccDisabled = 0 OR A.AccDisabled = NULL) 
    		END
    END
    Thanks in advance 

     

    unseendreamzzz

    Tuesday, September 02, 2014 6:57 AM

Answers

All replies

  • Hello,

    Check the execution plan of the SP to see if there is a potential point for optimization; see Displaying Graphical Execution Plans (SQL Server Management Studio)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 02, 2014 3:49 PM
  • I agree with Olaf.

    Quick observation, please don't use prefix the name of the stored procedure with SP_.

    --Prashanth

    Tuesday, September 02, 2014 4:08 PM
  • Why you can not just always select top 1 row (using row_number() approach) if this is the intent? Also, why do you need to use some check for the currency?

    Can you explain more of what do you want to get as a result from this procedure?

    Also a very good point in regards to sp_ prefix. Please change the procedure name.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, September 02, 2014 4:37 PM
    Moderator
  • Why do u want to optimize it? How long does it takes to run? 
    Tuesday, September 02, 2014 5:03 PM
  • This Query will cause a Deadlock because you are accessing twice the table tblAccounts for each row.

    Like Naomi suggest, use row_number.

    This will bring the first Account_ID, if you want the last Account ID, use order by A.Account_ID DESC.

    WITH cte AS(
    	SELECT 
    		A.Account_ID
    		,A.ID
    		,A.AccountName
    		,O.Order_Media_Ref
    		,C_2.ID
    		, C_2.NAME
    		,ROW_NUMBER() OVER(ORDER BY A.Account_ID) as RowID
    	FROM TblSvc S 
    	INNER JOIN TblOrder O ON O.Order_Svc_ID = S.SvcID
    	INNER JOIN tblCONTACT C_1 ON C_1.ID = O.Order_Billing_ID
    	INNER JOIN tblCONTACT C_2	ON C_2.ID = O.Order_Operator_ID
    	INNER JOIN TblAccounts A ON A.Contact_ID = C_1.ID
    	WHERE S.Svc_Number = @Svc_Number
    )
    
    SELECT 
    	* 
    FROM cte 
    WHERE RowID=1;

    Tuesday, September 02, 2014 5:11 PM