SQL Server Performance

Query help

Discussion in 'Getting Started' started by pinky, Jun 12, 2007.

  1. pinky New Member

    Hi all,
    This function takes more than 1 minit time to execute.how to reduce the function execution time.any alternative is there......

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    ALTER FUNCTION fRPT_FIN_CUSTDOCWISE (@INPBRANCHID VARCHAR(8000), @FromDate DATETIME,@ToDate DATETIME)
    RETURNS @fTBL_FIN_CUSTOMERAGING TABLE (COMPANY_ID VARCHAR(50) COLLATE ARABIC_CI_AI,COMPANY_NAME VARCHAR(50) COLLATE ARABIC_CI_AI,
    BRANCH_ID VARCHAR(50) COLLATE ARABIC_CI_AI, BRANCH_NAME VARCHAR(50) COLLATE ARABIC_CI_AI,
    UNIQ_ID VARCHAR(50) COLLATE ARABIC_CI_AI,DOCUMENT_TYPE VARCHAR(50) COLLATE ARABIC_CI_AI,
    DOC_ID VARCHAR(50) COLLATE ARABIC_CI_AI,TRANS_ID VARCHAR(50) COLLATE ARABIC_CI_AI,
    DOCUMENT_NO VARCHAR(100) COLLATE ARABIC_CI_AI,POSTING_DATE DATETIME,ACCOUNT_ID VARCHAR(50) COLLATE ARABIC_CI_AI,
    CUSTOMER_NAME VARCHAR(100) COLLATE ARABIC_CI_AI,CUSTOMER_CODE VARCHAR(100) COLLATE ARABIC_CI_AI,
    DEBIT_AMOUNT DECIMAL(34,4),CREDIT_AMOUNT DECIMAL(34,4),CUSTOMER_ORDER VARCHAR(50) COLLATE ARABIC_CI_AI,
    NARRATION VARCHAR(500) COLLATE ARABIC_CI_AI,UNADJUSTEDAMT VARCHAR(50) COLLATE ARABIC_CI_AI)
    AS
    BEGIN
    DECLARE @TBLBRANCH TABLE (BRANCHID VARCHAR(50))
    DECLARE @TBLDOCIDS TABLE (DOC_IDS VARCHAR(50))

    INSERT INTO @TBLBRANCH SELECT ORG_ID FROM VORGANIZATION WHERE ORG_ID IN (SELECT VALUE FROM FTBLSPLIT(@INPBRANCHID,';')) OR TOP_PARENT IN (@INPBRANCHID)

    INSERT INTO @fTBL_FIN_CUSTOMERAGING SELECT VIEWC.Org_Id,VIEWC.Name,VIEWB.Org_Id,VIEWB.Name,
    vADJM.UNIQ_ID,vADJM.DOC_TYPE,vADJM.DOC_ID,vADJM.TRANS_ID,vADJM.DOCUMENT_NO,vADJM.POSTING_DATE,vADJM.ACCOUNT_ID,
    ACMST.ACCOUNT_NAME,ACMST.ACCOUNT_CODE,vADJM.DEBIT_AMOUNT,vADJM.CREDIT_AMOUNT,
    vADJM.CUSTOMER_ORDER,vADJM.NARRATION,vADJM.UNADJUSTEDAMT FROM VRPT_FIN_AR_ADJUSTMENTS vADJM
    INNER JOIN VFINANCEACCOUNTS ACMST ON ACMST.ACCOUNT_ID = vADJM.ACCOUNT_ID AND ACMST.ACCOUNT_TYPE = COALESCE ('C' , 'C')
    INNER JOIN vOrganization VIEWB ON VIEWB.ORG_ID = vADJM.BRANCH_ID
    INNER JOIN vOrganization VIEWC ON VIEWB.Top_Parent = VIEWC.Org_Id
    WHERE VIEWB.Org_Id COLLATE ARABIC_CI_AI in (SELECT BRANCHID COLLATE ARABIC_CI_AI FROM @TBLBRANCH) AND POSTING_DATE Between CONVERT(CHAR(10),@FromDate,120) and CONVERT(CHAR(10),@ToDate,120)

    --DELETE FROM @TBLDOCIDS
    --INSERT INTO @TBLDOCIDS SELECT DOC_ID FROM @fTBL_FIN_CUSTOMERAGING GROUP BY DOC_ID HAVING COUNT(DOC_ID)=1

    UPDATE @fTBL_FIN_CUSTOMERAGING SET UNADJUSTEDAMT='AOpening' WHERE DOC_ID IN (SELECT COLUMN001 FROM OATABLE044 WHERE COLUMN006=ACCOUNT_ID)
    UPDATE @fTBL_FIN_CUSTOMERAGING SET UNADJUSTEDAMT='BAdjustment' WHERE UNADJUSTEDAMT=''

    UPDATE @fTBL_FIN_CUSTOMERAGING SET UNADJUSTEDAMT='Unadjusted' WHERE DOC_ID IN (SELECT COLUMN002 COLLATE ARABIC_CI_AI FROM OATABLE076 GROUP BY COLUMN002 HAVING COUNT(COLUMN002)=0)

    --DELETE FROM @fTBL_FIN_CUSTOMERAGING WHERE BRANCH_ID IN (SELECT BRANCHID COLLATE ARABIC_CI_AI FROM @TBLBRANCH)
    --AND POSTING_DATE Between CONVERT(CHAR(10),@FromDate,120) and CONVERT(CHAR(10),@ToDate,120)
    --AND DOC_ID IN (SELECT DOC_ID FROM @fTBL_FIN_CUSTOMERAGING WHERE POSTING_DATE Between CONVERT(CHAR(10),@FromDate,120) and CONVERT(CHAR(10),@ToDate,120) GROUP BY DOC_ID,ACCOUNT_ID HAVING SUM(DEBIT_AMOUNT)=SUM(CREDIT_AMOUNT))

    RETURN
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  2. satya Moderator

    Why don't you use this as a Stored procedure?
    SP are much better for performance than functions.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. pinky New Member

    In our organization they are using this functions any possible way please tell me.


    Thanks &regards,
    pinky
  4. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by pinky</i><br /><br />In our organization they are using this functions any possible way please tell me.<br /><br /><br />Thanks ®ards,<br />pinky<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Not clear what are you trying to say?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  5. alzdba Member

    ... In our organization they are using this functions any possible ....

    so far for common sense !

    Many people try to provide wildecard lists (@INPBRANCHID VARCHAR(8000)) ... and get stuck sooner or later.

    Keep it simple
    .... work with known values
    .... make it readable
    .... maybe the system will survive you and then who will be able to do simple maintenance
    .... confuscation is not a synonym for job security ... on the contrary !
    .... aviod the unnecessary (COALESCE ('C' , 'C') will be optimized, but takes optimizer time)
    .... avoid extra runs

Share This Page