February 29, 2012 at 4:12 am
I am using inline table value functions in my sp to return multiple result sets and use union all to combine results.
To refer the result set many time in Sp I store the result set from inline table value function in TempTable.
The Query to call of the one of the most expensive function in sp is given below.
INSERT INTO #RecoveryLoanee(LEVEL4_CODE,
LEVEL4_NAME,
LEVEL5_CODE,
LEVEL5_NAME,
LEVEL6_CODE,
LEVEL6_NAME,
product_Name,
package_Name,
product_code,
package_code,
borrower_no,
Due_Date,
RECEIPT_DATE,
RecPA,
Recsc,
Reclsc,
RecPenalty,
Rebate)
SELECT
LEVEL4_CODE,
LEVEL4_NAME,
LEVEL5_CODE,
LEVEL5_NAME,
LEVEL6_CODE,
LEVEL6_NAME,
product_Name,
package_Name,
product_code,
package_code,
borrower_no,
Due_Date,
RECEIPT_DATE,
RecPA,
Recsc,
Reclsc,
RecPenalty,
Rebate
FROM dbo.CR_RecoveryReceiptWise_Rpt(@DisbursementDateFrom,@DisbursementDateTo,@ReportingDate,@PRODUCTCODE,@PackageCode,@PurposeCode,@DonorCode,@ProjectCode,@MemberCode,@LoanApplyDate,@LoanStatus,@RepaymentMode,@RepaymentPeriod,@BorrowerNo,@SanctionNo,@PhasePLA,@PovertyRank,@Gender,@CNIC,@CoCode,@CoType,@CoStatus,@OfficeCodes)
The code of the function is also given below along with attached execution plan.
CREATE FUNCTION [dbo].[CR_RecoveryReceiptWise_Rpt]
(
@DisbursementDateFrom VARCHAR(20),
@DisbursementDateTo VARCHAR(20),
@ReportingDate VARCHAR(20),
@PRODUCTCODE varchar(20),
@PackageCode varchar(20),
@PurposeCode varchar(20),
@DonorCode varchar(20),
@ProjectCode varchar(20),
@MemberCode VARCHAR(30),
@LoanApplyDate VARCHAR(20),
@LoanStatus char(5),
@RepaymentMode varchar(20),
@RepaymentPeriod varchar(20),
@BorrowerNo VARCHAR(30),
@SanctionNo Varchar(25),
@PhasePLA char(5),
@PovertyRank varchar(20),
@Gender char(5),
@CNIC Varchar(25),
@CoCode varchar(20),
@CoType char(5),
@CoStatus varchar(4),
@OfficeCodes varchar(1000)=NULL
)
RETURNS TABLE AS
RETURN
(
SELECT
i.LEVEL3_CODE
,i.LEVEL3_NAME
,i.LEVEL4_CODE
,i.LEVEL4_NAME
,i.LEVEL5_CODE
,i.LEVEL5_NAME
,i.LEVEL6_CODE
,i.LEVEL6_NAME
,LL.member_code
,LL.loan_apply_date
,L.CHEQUE_DATE
,LL.DUE_DATE
,LL.RECEIPT_DATE
,L.PAYOFF_DATE
,LL.inst_no
,sum(isnull(LL.recpa,0)) RecPA
,sum(isnull(LL.recsc,0)) RecSc
,sum(isnull(LL.reclsc,0)) RecLSc
,sum(isnull(LL.rebate,0)) Rebate
,sum(isnull(LL.RecPenalty,0)) as RecPenalty
,L.PACKAGE_CODE
,pck.PACKAGE_NAME
,L.PRODUCT_CODE
,product.PRODUCT_NAME
,sum(isnull(LL.ADV_RECOVERY,0)) ADV_RECOVERY
,EXCESS
,L.Borrower_NO
,L.SANCTION_NO
,co.CO_CODE
,co.CO_NAME
,co.CO_TYPE
,co.Office_Code
,m.GENDER
,isnull(M.MEMBER_FIRST_NAME,'') + ' ' + isnull(M.MEMBER_Last_NAME,'') AS MEMBER_FIRST_NAME
,m.Guardian_Name
,LL.RECEIPT_BOOK
,LL.RECEIPT_NO
,M.POVERTY_RANK_CODE
,L.REPAYMENT_MODE_CODE
,L.Disbr_Date as DisbursementDate
,isnull(M.CNIC,M.OldNIC) as NIC
,M.Joining_Date
,L.PHASE
,L.CREDIT_PERIOD
,LM.Loanee_ID
,CO.UC_Code
,CO.City_Code
,LM.CHEQUE_NO
FROM
dbo.CR_co CO
INNER JOIN dbo.ADM_ChildOffices_Get(@OfficeCodes) Office ON CO.OFFICE_CODE=Office.OFFICE_CODE
INNER join ADM_OFFICE O ON (Office.office_code= O.office_code)
INNER join V_OFFICE_HIERARCHYDT i ON (O.office_code = i.level6_code and O.office_level = i.depthlevel)
inner join dbo.CR_CO_LOANEE_MASTER LM ON CO.CO_CODE = LM.CO_Code
INNER Join dbo.CR_CO_LOANEE L ON LM.Loanee_ID = L.Loanee_ID
INNER Join dbo.CR_CO_LOANEE_Ledger LL ON LM.Loanee_ID = LL.Loanee_ID
INNER join dbo.CR_co_member M on (LM.MEMBER_CODE= M.MEMBER_CODE)
INNER join dbo.CR_PACKAGE pck ON L.package_code = pck.package_code
INNER join dbo.CR_product product on (L.product_code = product.product_code)
WHERE
(@ReportingDate between i.From_Date and i.To_Date) AND
(LM.CO_CODE = @CoCode or @CoCode is null )
AND (CO.CO_CODE = @CoCode or @CoCode is null)
AND (CO.CO_TYPE = @COTYPE or @COTYPE is null)
AND (CO.STATUS = @CoStatus or @CoStatus is null)
And LM.Cheque_Date>=@DisbursementDateFrom and
LM.Cheque_Date<=@DisbursementDateTo AND
LL.RECEIPT_DATE >= @DisbursementDateFrom AND
LL.RECEIPT_DATE <= @ReportingDate
AND LL.Book_Type = 1
AND (L.member_code=@membercode or @membercode is null)
and (L.LOAN_APPLY_DATE=@LoanApplyDate or @LoanApplyDate is null)
AND (L.REPAYMENT_MODE_CODE= @RepaymentMode or @RepaymentMode is null)
AND (L.BORROWER_NO = @BorrowerNo or @BorrowerNo is null)
And (L.SANCTION_NO = @SanctionNo or @SanctionNo is null)
AND (case LM.phase
when 'PL' then 1
when 'PP' then 1
else 0 end = @phasePLA or @phasePLA is null)
AND (L.phase=@LoanStatus or @LoanStatus is null)
AND (L.PRODUCT_CODE =@PRODUCTCODE or @PRODUCTCODE is null)
AND (L.PACKAGE_CODE =@PACKAGECODE or @PACKAGECODE is null)
AND (L.PURPOSE_CODE =@PURPOSECODE or @PURPOSECODE is null)
AND (L.DONOR_CODE =@DONORCODE or @DONORCODE is null)
AND (L.PROJECT_CODE=@ProjectCode or @ProjectCode is null)
AND (M.POVERTY_RANK_CODE =@PovertyRank or @PovertyRank is null)
AND (M.Gender =@Gender or @Gender is null)
and (M.CNIC= @CNIC or M.OLDNIC= @CNIC or @CNIC is null )
group by
i.LEVEL3_CODE
,i.LEVEL3_NAME
,i.LEVEL4_CODE
,i.LEVEL4_NAME
,i.LEVEL5_CODE
,i.LEVEL5_NAME
,i.LEVEL6_CODE
,i.LEVEL6_NAME
,LL.member_code
,LL.loan_apply_date
,LL.inst_no
,LL.RECEIPT_DATE
,L.PAYOFF_DATE
,L.PACKAGE_CODE
,pck.PACKAGE_NAME
,L.PRODUCT_CODE
,product.PRODUCT_NAME
,L.Cheque_Date
,EXCESS
,LL.DUE_DATE
,L.Borrower_NO
,L.SANCTION_NO
,co.CO_CODE
,co.CO_NAME
,co.CO_TYPE
,co.Office_Code
,m.GENDER
,m.MEMBER_FIRST_NAME
,M.MEMBER_Last_NAME
,Guardian_Name
,LL.RECEIPT_BOOK
,LL.RECEIPT_NO
,M.POVERTY_RANK_CODE
,L.REPAYMENT_MODE_CODE
,L.Disbr_Date
,m.CNIC
,m.OLDNIC
,M.Joining_Date
,L.PHASE
,L.CREDIT_PERIOD
,LM.Loanee_ID
,CO.UC_Code
,CO.City_Code
,LM.CHEQUE_NO
);
Insert into #RecoveryLoanee temptable is more expensive operation.Is ther any fast alternative of this.
Any Help to optimize the Query.
February 29, 2012 at 7:44 am
Your function is what is known as a "catch-all" query and these are known to have performance problems. Gail has a very good article about "Catch-all" queries[/url] and how to improve the performance.
You'll probably need to use dynamic SQL. As far as I know, you can't use dynamic SQL in a function, but I don't think that you really want to use a function for this anyhow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 29, 2012 at 9:37 pm
We are using functions for code reuseability in some sps.
So What Should be the exact alternative of functions to make query fast. I have very long queries so dynamic query not look a good option.
Thanks
March 1, 2012 at 3:40 am
azhar.iqbal499 (2/29/2012)
We are using functions for code reuseability in some sps.So What Should be the exact alternative of functions to make query fast. I have very long queries so dynamic query not look a good option.
Thanks
Using functions does indeed helps with code reusability, however your example of catch-all query will never perform fast.
You can read about catch-all query from link provided in previous post. As for suggetsion about what you can replace your function with:
If you know the most common set of parameters used in WHERE claues - create it as view, for the rest, you can create stored proc which will implement catch-all query using dynamic sql (as adviced in the link above). Then yuo can use it in your other procs. Yes, you will not be able to join to it, and you will need always to create table before executing sp to get results, but you will see performance you are looking after...
March 1, 2012 at 3:58 am
If you're using SQL 2008 SP2 onwards, you can change the select to:
SELECT
LEVEL4_CODE,
LEVEL4_NAME,
LEVEL5_CODE,
LEVEL5_NAME,
LEVEL6_CODE,
LEVEL6_NAME,
product_Name,
package_Name,
product_code,
package_code,
borrower_no,
Due_Date,
RECEIPT_DATE,
RecPA,
Recsc,
Reclsc,
RecPenalty,
Rebate
FROM dbo.CR_RecoveryReceiptWise_Rpt(@DisbursementDateFrom,@DisbursementDateTo,@ReportingDate,@PRODUCTCODE,@PackageCode,@PurposeCode,@DonorCode,@ProjectCode,@MemberCode,@LoanApplyDate,@LoanStatus,@RepaymentMode,@RepaymentPeriod,@BorrowerNo,@SanctionNo,@PhasePLA,@PovertyRank,@Gender,@CNIC,@CoCode,@CoType,@CoStatus,@OfficeCodes) OPTION(RECOMPILE)
This will then use the new optimisations for static parameter evaluation and come up with a much better plan for the catch-all parameters, but at the expense of having to compile a new plan for each execution. Don't use this if you're using versions prior to 2k8 SP2 as it either won't provide the optimisations, or can produce an incorrect results bug
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply