SQL Server Performance Forum – Threads Archive

Query help

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

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.

In our organization they are using this functions any possible way please tell me.
Thanks &regards,
pinky

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by pinky In our organization they are using this functions any possible way please tell me. Thanks Ã‚Â®ards, pinky <hr height="1" noshade id="quote"></blockquote id="quote"> Not clear what are you trying to say?[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre id="code"> Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

… 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

]]>