SQL Server Performance Forum – Threads Archive

Time Difference in Execution Of Sp

Friends I am facing a Problem that’s bit worrying[|)] I am having an Sp by the name IRC_SPrdIntCalc Execution Time Of This Sp in the Query Analyzer is 4 minutes 38 Seconds. The Same Sp when I Invoke thru Vb.Net Code It Takes 23 minutes The Vb.Net Code is placed below Hope Some one can help me in resolving this issue Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim conBSCLNT_ACCR As OleDbConnection Dim concommand As OleDbCommand Dim parmProductID As OleDbParameter Dim intProductID As Integer Label3.Text = "" Label4.Text = "" Label8.Text = "" If Len(TextBox1.Text) <> 0 Then conBSCLNT_ACCR = New OleDbConnection("Provider=SQLOLEDB<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=False;User ID=sa<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=thesysrcbs1;Initial Catalog=BSCLNT_ACCR;Data Source=THESYSRCBS1;Use Procedure for Prepare=1;Auto Translate=True<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />acket Size=32000;Workstation ID=THESYS197;Use Encryption for Data=False;Tag with column collation when possible=False") concommand = New OleDbCommand("IRC_sPrdIntCalc", conBSCLNT_ACCR) concommand.CommandType = CommandType.StoredProcedure concommand.CommandTimeout = CInt(TextBox2.Text) conBSCLNT_ACCR.Open() parmProductID = concommand.Parameters.Add("ReturnValue", OleDbType.Integer) parmProductID.Direction = ParameterDirection.ReturnValue ‘ Add first Input Parameter concommand.Parameters.Add("@p_BrID", "8") ‘ Add second Input Parameter concommand.Parameters.Add("@p_PrdID", CStr(TextBox1.Text)) ‘ Add third Input Parameter concommand.Parameters.Add("@p_CalcToDt", "30-04-2006") ‘ Add fourth Input Parameter concommand.Parameters.Add("@p_BusnDt", "30-04-2006") ‘ Add fifth Input Parameter concommand.Parameters.Add("@p_ValueDt", "30-04-2006") ‘ Add sixth Input Parameter concommand.Parameters.Add("@p_LoginID", "Ver358") ‘ Add seventh Input Parameter concommand.Parameters.Add("@p_Purpose", "ACCRUAL") concommand.ExecuteNonQuery() intProductID = concommand.Parameters("ReturnValue").Value conBSCLNT_ACCR.Close() Else MsgBox("Please Enter the Product ID") End If Catch MsgBox(Err.Description) End Try End Sub With Deep Regards Anil Never Give Up

The code of the SP would have been more helpful. Is there a SET NOCOUNT ON right at the beginning of the SP? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Dear Frank
Here is Sp Code /*
begin tran
EXEC IRC_SPrdIntCalc’25’, ’14’, ’06-07-2004′, ’06-07-2004′,’06-07-2004′,”,’ACCRUAL’
rollback tran
*/
CREATE PROCEDURE dbo.IRC_sPrdIntCalc
(
@p_BrID UDVC10 = Null,
@p_PrdID UDVC16,
@p_CalcToDt UDVC25 = Null,
@p_BusnDt UDVC25 = Null,
@p_ValueDt UDVC25 = Null,
@p_LoginID UDVC25 = Null,
@p_Purpose UDVC25 = Null
)
AS
BEGIN
SET NOCOUNT ON
/**************************************************************************
‘SP NAME :IRC_sPrdIntCalc
‘Parameter :BrID, PrdID, AcctID, Calculation Date,Business Date,
Value Date, Login ID and Purpose
‘Output Parameter :
‘Created By :EDA
‘Created Date :01-06-2005
‘Description :This sp will be used for calculating the interest at the
product level.
‘Modified By :EDA(EDA0304200601)
‘Modified Date :03.04.2006 11:25(For Reversal purpose Demand added)
‘Modified By : RMA 19:28 >> RMA20060525 (TmpIrHdr,TmpIrDet not Cleared For Each Account)
‘Modified Date : 25.05.2006
‘**************************************************************************/
DECLARE @m_AcctID UDVC16,@m_MinSlNo UDINT,
@m_MaxSlNo UDINT,@HasintDepdn UDCH1,
@HasSplRates UDCH1,@HasIntParams UDCH1,
@AcctOpenDt UDDT,@AcctOpnAmt UDLDGRAMT,
@TenureDays UDINT, @TenureMonths UDINT,
@TenureYears UDINT,@p_Inttype UDVC16,
@m_CalcToDt UDDT,@m_BusnDt UDDT,
@m_HasFixedIntRt UDCH1,@m_HasZeroMinSlNo UDINT,
@m_HasZeroMaxSlNo UDINT,@m_ValueDt UDDT,
@m_IsReqMinIntDue UDCH1,@m_NxtDmdDt UDDT,
@m_NxtAccrDt UDDT,@m_NxtCaptDt UDDT,
@m_NxtDt UDDT,@m_NxtIntCalcDt UDDT,
@m_ErrMsgID UDVC10,@m_KeyValues UDVC50,
@m_AcctCnt UDBINT,@m_SetCurDtTime UDVC25,
@m_Freq UDVC10, @m_AccrFreq UDVC10,
@m_DmdFreq UDVC10, @m_IntCalcFreq UDVC10,
@m_CaptFreq UDVC10, @m_CmpFreq UDVC10,
@m_CapitalizeFreqType UDVC10,@m_AccrualFreqType UDVC10,
@m_DemandFreqType UDVC10,@m_IntProdDays UDINT –<< For the purpose of EOT >>–
SET @m_SetCurDtTime = dbo.fgetbusndate(GETDATE(),’DMY-‘) SET @m_CalcToDt = dbo.fSetBusnDate(@P_CalcToDt)
SET @m_BusnDt = dbo.fSetBusnDate(@p_BusnDt)
SET @m_ValueDt = dbo.fSetBusnDate(@p_ValueDt) CREATE TABLE #TmptCalcSlab
(
BrID VARCHAR(16),PrdID VARCHAR(16),AcctID VarChar(16),
FromDt DATETIME, ToDt DATETIME,TotOS UDLDGRAMT, TotPrinOD UDLDGRAMT,
TotIntOD UDLDGRAMT,TotOthOD UDLDGRAMT,
DurSlabID UDINT,AmtSlabID UDINT,PerSlabID UDINT,
DurSlabType UDVC10, AmtSlabType UDVC10,PerSlabType UDVC10,
ROI NUMERIC(12,4),PenalROI NUMERIC(12,4),PenaltyROI NUMERIC(12,4),IRID UDINT,
–OptnType VARCHAR(25), OPtnTypeID VARCHAR(10),
Principal UDLDGRAMT,Interest UDLDGRAMT, Penal UDLDGRAMT,
Penalty UDLDGRAMT, SlabNo BIGINT Identity(1,1),
Flag CHAR(1), DmdFlag CHAR(1), PreInt NUMERIC(12,4),AcctCyID VARCHAR(5),
InstallAmt UDLDGRAMT, PreClsROI NUMERIC(12,4)
) –<< FOR DISPLAYING THE FINAL OUTPUT
CREATE TABLE #TmptCalcSlabFinal
(
BrID VARCHAR(16),PrdID VARCHAR(16),AcctID VarChar(16),
FromDt DATETIME, ToDt DATETIME,TotOS UDLDGRAMT, TotPrinOD UDLDGRAMT,
TotIntOD UDLDGRAMT,TotOthOD UDLDGRAMT,
DurSlabID UDINT,AmtSlabID UDINT,PerSlabID UDINT,
DurSlabType UDVC10,AmtSlabType UDVC10,PerSlabType UDVC10,
ROI NUMERIC(12,4),PenalROI NUMERIC(12,4),PenaltyROI NUMERIC(12,4),IRID UDINT,
Principal UDLDGRAMT,Interest UDLDGRAMT, Penal UDLDGRAMT,
Penalty UDLDGRAMT, SlabNo BIGINT Identity(1,1),
Flag CHAR(1),PreInt NUMERIC(12,4),AcctCyID VARCHAR(5),InstallAmt UDLDGRAMT,
PreClsROI NUMERIC(12,4)
) CREATE TABLE #TmptAcctDetails
(
BrIDVARCHAR(16),PrdIDVARCHAR(16),AcctID VarChar(16),
AcctOpenDt DATETIME,AcctOpnAmt UDLDGRAMT,TenureDays UDINT,
TenureMonths UDINT,TenureYears UDINT,
HasintDepdn CHAR(1),HasSplRates CHAR(1),HasIntParams CHAR(1),
HasFixedIntRt CHAR(1),NxtDmdDt DATETIME, NxtAccrDt DATETIME,
NxtCaptDt DATETIME, NxtIntCalcDt DATETIME,IsRaiseCurrDem CHAR(1),
PrdSplRtID BIGINT, IntDev VARCHAR(5), IntDevPerc NUMERIC(12,4),
ExpiryDt DATETIME, AcctCyID VARCHAR(5),InstallAmt UDLDGRAMT,
LstDmdDt DATETIME, LstCaptDt DATETIME, LstAccrDt DATETIME,
LstIntCalcDt DATETIME, RoutExec UDCH1, ValueDt DATETIME,MatAmt UDLDGRAMT,
IntContinue UDCH1, ForeClsDt DATETIME, LstCompDt DATETIME,
TotOS UDLDGRAMT, ARCStat UDCH1, LstPenaltyCalcDt UDDT, SlNo INT IDENTITY(1,1)
) –<<< Temp. table for storing ledger records >>>—
CREATE TABLE #TmpCapitalize
(
BrID VARCHAR(16), PrdIDVARCHAR(16), AcctID VarChar(16),
CyID VARCHAR(16), MnLdgrInsGrpID VARCHAR(10), MnLdgrInsGrpSlNo VARCHAR(10),
OprType VARCHAR(25),OprBrID VARCHAR(10), JVNo VARCHAR(16),
FASTxnID VARCHAR(16), BusnDt DATETIME,OptnType VARCHAR(25),
OptnTypeID VARCHAR(10),SettlementID VARCHAR(10),
TXNID VARCHAR(25),TXNAMT UDLDGRAMT,
TXNBrID VARCHAR(10), TXNPrdID VARCHAR(10), TXNAcctID VARCHAR(16),
TXNJVNo VARCHAR(16)
) CREATE TABLE #TmptIntParamsDetl
(
BrID VARCHAR(16) NULL,PrdID VARCHAR(16),
AcctID VARCHAR(16) NULL,IntRateType VARCHAR(10),
IntCalcType VARCHAR(10), IntCompFreq VARCHAR(10),
CompFrqType VARCHAR(25), CalcFrqType VARCHAR(25),
IntCalcFreq VARCHAR(10), ProdDaysCalcType VARCHAR(10),
IntRepayFreq VARCHAR(10), IntRepayType VARCHAR(10),
IntPayMode VARCHAR(10), IntCapitalizeFreq VARCHAR(10),
IntAccrualFreq VARCHAR(10), IntProdDays INTEGER,
IntBaseTypeID CHAR(1), IntDeviation NUMERIC(12,4),
IntDemandFreq VARCHAR(10), CapitalizeFreqType VARCHAR(10),
AccrualFreqType VARCHAR(10), DemandFreqType VARCHAR(10)
) CREATE TABLE #TmptIRHdr
(
BrID VARCHAR(10) Null,PrdID VARCHAR(16),AcctID VARCHAR(10) Null,
IRID INTEGER, CyID VARCHAR(16),EffDate DATETIME,IRType VARCHAR(10),
DurSlabType VARCHAR(10),AmtSlabType VARCHAR(10),PerSlabType VARCHAR(10),
IRSlNo Int Identity(1,1)
) CREATE TABLE #TmptIRDetl
(
BrID VARCHAR(16),PrdID VARCHAR(16), AcctID VARCHAR(16),
IRID INTEGER,DurSlabID INTEGER, AmtSlabID INTEGER,
PercSlabID INTEGER, DurSlabUpperYrs INTEGER,
DurSlabLowerYrs INTEGER NULL,
DurSlabUpperMns INTEGER NULL,DurSlabLowerMns INTEGER NULL,
DurSlabUpperDays INTEGER NULL,DurSlabLowerDays INTEGER NULL,
AmtSlabUpper UDLDGRAMT,AmtSlabLower UDLDGRAMT,
PercSlabUpper NUMERIC(8,4),PercSlabLower NUMERIC(8,4),
ROI NUMERIC(8,4),PenalROI NUMERIC(8,4),Penalty NUMERIC(8,4),
ExpiryROI NUMERIC(8,4),PreClsROI NUMERIC(8,4),ForeClsROI NUMERIC(8,4)
) CREATE TABLE #TmptPrdParams
(IsReqMonthBaln UDCH1, ODGracePeriod UDSINT, PreClsDev NUMERIC(12,4), PrdType UDVC10,
PrdClosedDt UDDT,ForeClStat UDCH1,ODIntAllowPerd UDINT) INSERT INTO #TmptPrdParams
(IsReqMonthBaln,ODGracePeriod , PreClsDev, PrdType, PrdClosedDt,ForeClStat,ODIntAllowPerd)
SELECT IsReqMonthBaln, ODGracePeriod, IsNull(PreClsDev,0), PrdType, PrdClosedDt,
IsNull(ForeClStat,’N’),IsNull(ODIntAllowPerd,0)
FROMSAM_tProductMst PrdMst, SAM_tPrdParams PrdParams
WHEREPrdMst.PrdID= @p_PrdID
ANDPrdMst.PrdID = PrdParams.PrdID — BEGIN TRAN –<< Start the New Transaction
–<< GET THE ACCOUNT DETAILS
INSERT INTO #TmptAcctDetails
(BrID, PrdID, AcctID, AcctOpenDt, AcctOpnAmt, TenureDays, TenureMonths,
TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt,
NxtDmdDt,NxtAccrDt,NxtCaptDt,NxtIntCalcDt,IsRaiseCurrDem,PrdSplRtID,
IntDev,IntDevPerc,AcctCyID,ExpiryDt,InstallAmt,LstIntCalcDt,RoutExec,ValueDt,MatAmt,
IntContinue,ForeClsDt,LstCompDt,LstDmdDt,LstAccrDt,LstCaptDt,TotOS,ARCStat,LstPenaltyCalcDt)
SELECT BrID,PrdID,AcctID,AcctOpenDt,AcctOpnAmt,TenureDays,TenureMonths,
TenureYears,HasintDepdn,HasSplRates,HasIntParams,IsNull(HasFixedIntRt,’N’),
NxtDmdDt,NxtAccrDt,NxtCaptDt,NxtIntCalcDt,IsRaiseCurrDem,PrdSplRtID,
IntDev,IntDevPerc,AcctCyID,ExpiryDt,InstallAmt,LstIntCalcDt,RoutExec,
ValueDt,MatAmt,IntContinue,ForeClsDt,LstCompDt,LstDmdDt,LstAccrDt,
LstCaptDt,TotOS,ARCStat,LstPenaltyCalcDt
FROM dbo.IRC_fFetchAcctDetl(@p_BrID,@p_PrdID,Null,@m_ValueDt,@p_Purpose, 0,0) –<< For EOT Error Log Purpose >>–
SELECT @m_AcctCnt = COUNT(AcctID) FROM #TmptAcctDetails –<<Added by EDA on 20.09.2005(for validating the transacted account)
IF NOT EXISTS(SELECT ‘X’ FROM #TmptAcctDetails)
BEGIN
— ROLLBACK TRAN
–SET @m_ErrMsgID =’V070′
RETURN
— GOTO Error
END
SELECT @m_HasZeroMinSlNo = MIN(SlNo), @m_HasZeroMaxSlNo = MAX(SlNo)
FROM #TmptAcctDetails WHILE @m_HasZeroMinSlNo <= @m_HasZeroMaxSlNo
BEGIN
SELECT @m_AcctID = AcctID, @AcctOpenDt=AcctOpenDt ,@AcctOpnAmt=AcctOpnAmt,@TenureDays=TenureDays,
@TenureMonths=TenureMonths,@TenureYears=TenureYears,
@HasintDepdn=IsNull(HasintDepdn,0),@HasSplRates=HasSplRates,@HasIntParams=IsNull(HasIntParams,0),
@m_HasFixedIntRt = IsNull(HasFixedIntRt,’N’),
@m_NxtDmdDt = NxtDmdDt, @m_NxtAccrDt = NxtAccrDt,
@m_NxtCaptDt = NxtCaptDt, @m_NxtIntCalcDt = NxtIntCalcDt
FROM #TmptAcctDetails
WHERE SlNo = @m_HasZeroMinSlNo INSERT INTO #TmptIntParamsDetl
SELECT DISTINCT @p_BrID, @p_PrdID,AcctID, IntRateType,IntCalcType,IntCompFreq,
CompFrqType, CalcFrqType,IntCalcFreq,ProdDaysCalcType,IntRepayFreq,IntRepayType,IntPayMode,IntCapitalizeFreq,
IntAccrualFreq,IntProdDays,IntBaseTypeID,IntDeviation,IntDemandFreq,
CapitalizeFreqType, AccrualFreqType, DemandFreqType
FROM dbo.SAM_fFetchIntParams(@p_BrID,@p_PrdID,@m_AcctID,@HasIntParams) –<< GET THE INTEREST RATE HEADER INFO. — << RMA20060525 PS Change Begins
Delete From #TmptIRHdr
— << RMA20060525 PS Change Ends INSERT INTO #TmptIRHdr
SELECT DISTINCT @p_BrID,@p_PrdID,AcctID,IRID,CyID,EffDate,
IRType,DurSlabType,AmtSlabType,PerSlabType
FROM dbo.SAM_fFetchIRDetl(@p_BrID,@p_PrdID,@m_AcctID,@HasintDepdn,@m_HasFixedIntRt) — IF NOT EXISTS(SELECT ‘x’ FROM #TmptIRHdr
— WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID) –< GET THE INTEREST RATE DETAILS INFO. — << RMA20060525 PS Change Begins
Delete From #TmptIRDetl
— << RMA20060525 PS Change Ends INSERT INTO #TmptIRDetl
SELECT DISTINCT @p_BrID, @p_PrdID, AcctID, IRID, DurSlabID, AmtSlabID, PercSlabID,
DurSlabUpperYrs, DurSlabLowerYrs, DurSlabUpperMns, DurSlabLowerMns,
DurSlabUpperDays, DurSlabLowerDays, AmtSlabUpper,
AmtSlabLower, PercSlabUpper, PercSlabLower,
ROI,PenalROI,Penalty,ExpiryROI,PreClsROI,ForeClsROI
FROM dbo.SAM_fFetchIRDetl(@p_BrID,@p_PrdID,@m_AcctID,@HasintDepdn,@m_HasFixedIntRt) –<<COMMENTED BY EDA ON 16.01.2006 NOT NECESSARY FOR PRODUCT LEVEL ROUTINE
— –<<< IF MIN DUE IS APPLICABLE THEN CALL THE FUNCTION >>>—
— SELECT @m_IsReqMinIntDue = IsReqMinIntDue FROMSAM_tProductMst WHERE PrdID = @p_PrdID
— IF @m_IsReqMinIntDue = ‘Y’ AND @p_Purpose = ‘DEMAND’ BEGIN –<<EDA16JAN06
— SET @m_NxtDt = (SELECT CASE WHEN @p_Purpose = ‘DEMAND’ THEN @m_NxtDmdDt
— WHEN @p_Purpose = ‘ACCRUAL’ THEN @m_NxtAccrDt
— WHEN @p_Purpose = ‘CAPITALISE’ THEN @m_NxtCaptDt
— WHEN @p_Purpose = ‘ONTXN’ THEN @m_NxtIntCalcDt
— END)
—
— SET @m_CalcToDt = dbo.IRC_fGetMinIntDue(@p_PrdID,@AcctOpenDt,@m_LstIntCalcDt,@m_CalcToDt)
— END — << CALCULATE INTEREST ACCOUNT WISE
EXEC IRC_sAcctIntCalc @CalcToDt = @m_CalcToDt,@BrID = @p_BrID,
@PrdID = @p_PrdID, @AcctID = @m_AcctID,
@BusnDt = @m_BusnDt,@LoginID = @p_LoginID,
@p_Purpose = @p_Purpose SET @m_HasZeroMinSlNo = @m_HasZeroMinSlNo + 1 INSERT INTO #TmptCalcSlabFinal
(BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,TotIntOD,TotOthOD,DurSlabID,
AmtSlabID,PerSlabID,DurSlabType,AmtSlabType,PerSlabType,ROI,PenalROI,
PenaltyROI,IRID, Principal,Interest,Penal,Penalty,AcctCyID)
SELECT BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,TotIntOD,TotOthOD,DurSlabID,
AmtSlabID,PerSlabID,DurSlabType,AmtSlabType,PerSlabType,ROI,PenalROI,PenaltyROI,IRID,
Principal,Interest,Penal,Penalty,AcctCyID
FROM#TmptCalcSlab
WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID — IF NOT EXISTS(SELECT ‘X’ FROM #TmptCalcSlab
— WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID)
— BEGIN
— — ROLLBACK TRAN
— SET @m_ErrMsgID =’V080′
— — RETURN
— –GOTO Error
— END IF @p_Purpose =’REVERSAL’ BEGIN
–<< FETCH TXNS FOR (FIRST DAY OF THE GIVEN LSTDMDDT – LSTDMDDT)
— –<<MODIFIED BY EDA ON 20.10.2005 (FOR LOAN REVERSAL)
DECLARE @m_IntAmt UDLDGRAMT SELECT @m_IntAmt = SUM(IsNull(Interest,0)) FROM #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID UPDATE #TmptCalcSlabFinal
SET INTEREST = @m_IntAmt – DEMND.DemAmt,
PRINCIPAL = 0
FROM #TmptCalcSlabFinal Slab,
(SELECT SUM(IsNull(Interest,0)) As IntAmt, SUM(IsNull(DemAmt,0)) As DemAmt FROM LON_tDemandHdr DH, LON_tDemandDet DD,
#TmptCalcSlabFinal Slab
WHERE DH.BrID = @p_BrID AND DH.PrdID = @p_PrdID AND DH.AcctID = @m_AcctID
AND DH.PK_ID = DD.DemHdr_FK
AND Slab.BrID = DH.BrID AND Slab.PrdID = DH.PrdID AND Slab.AcctID = DH.AcctID
AND DH.BusnDt >= Slab.FromDt AND DH.BusnDt <= Slab.ToDt AND DD.OptnTypeID = ‘2’) DEMND
WHERESlab.BrID = @p_BrID
ANDSlab.PrdID = @p_PrdID
ANDSlab.AcctID = @m_AcctID
ANDSlab.SlabNo = (Select Max(SlabNo) From #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID) UPDATE #TmptCalcSlabFinal
SETInterest = 0,
Principal = 0
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID
ANDSlabNo NOT IN (Select Max(SlabNo) From #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID) END
END –> END OF LOOP SELECT @m_CmpFreq = IntCompFreq, @m_IntCalcFreq = IntCalcFreq,
@m_CaptFreq = IntCapitalizeFreq, @m_AccrFreq = IntAccrualFreq,
@m_DmdFreq = IntDemandFreq, @m_CapitalizeFreqType = CapitalizeFreqType,
@m_AccrualFreqType = AccrualFreqType, @m_DemandFreqType = DemandFreqType,
@m_IntProdDays = IntProdDays
FROM#TmptIntParamsDetl
WHERE BrID = @p_BrID
ANDPrdID= @p_PrdID IF @p_Purpose = ‘DEMAND’ BEGIN
EXEC SAM_sDemand_INS @p_BrID, @p_PrdID, @m_BusnDt, @p_LoginID,@m_ValueDt –<< CALCULATE THE NEXT DEMAND DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”,@m_DmdFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_DemandFreqType,@m_IntProdDays,
@m_AccrFreq, @m_BusnDt — –<< ONCE CAPITALISE ROUTINE COMPLETE SET THE VALUE AS EMPTY
— UPDATE LON_tAcctBaln
— SET DmdRoutExec = CASE DmdRoutExec WHEN ” THEN ‘D’ WHEN ‘D’ THEN ‘S’ Else ” End
— WHEREBrID = @p_BrID
— ANDPrdID = @p_PrdID
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’) –<< RE-INITIALISE THE ACCRUAL AFTER DEMAND OR CAPITALISE(ADDED BY EDA ON 05.12.2005)
EXEC IRC_sResetAccrual @p_BrID, @p_PrdID,Null,@m_ValueDt,@p_Purpose
END
ELSE IF @p_Purpose = ‘ACCRUAL’ BEGIN
EXEC IRC_sGetAccruDetls @p_BrID, @p_PrdID, Null –<< CALCULATE THE NEXT ACCRUAL DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”, @m_AccrFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_AccrualFreqType,
@m_IntProdDays, @m_AccrFreq, @m_BusnDt
END
–<<< CALL THE CAPITALISE PROCEDURE IF CALCULATION DATE IS EQUAL TO CAPITALISE DATE >>>–
ELSE IF @p_Purpose = ‘CAPITALISE’ BEGIN
EXEC SAM_SCAPITALISE_INS @p_BrID, @p_PrdID, NULL, @m_BusnDt, @p_LoginID, @m_ValueDt, Null, Null– , @p_Purpose –<< CALCULATE THE NEXT CAPITALISE DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”, @m_CaptFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_CapitalizeFreqType,
@m_IntProdDays, @m_AccrFreq, @m_BusnDt –<< ONCE CAPITALISE ROUTINE COMPLETE SET THE VALUE AS EMPTY
— UPDATE LON_tAcctBaln
— SET DmdRoutExec = CASE DmdRoutExec WHEN ” THEN ‘D’ WHEN ‘D’ THEN ‘S’ Else ” End
— WHEREBrID = @p_BrID
— ANDPrdID = @p_PrdID
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’) –<< RE-INITIALISE THE ACCRUAL AFTER DEMAND OR CAPITALISE(ADDED BY EDA ON 05.12.2005)
EXEC IRC_sResetAccrual @p_BrID, @p_PrdID,Null,@m_ValueDt,@p_Purpose
RETURN
END
ELSE IF @p_Purpose = ‘REPORTS’ Or @p_Purpose = ‘ONSCR’ BEGIN
SELECT BrID, PrdID, AcctID, dbo.SAM_fCurrRndFormat(SUM(IsNull(Interest,0))) As INTEREST
FROM #TmptCalcSlabFinal
GROUP BY BrID, PrdID, AcctID
Order by BrID,PrdID,AcctID
END
ELSE IF @p_Purpose = ‘REVERSAL’ BEGIN
–<< FOR REVERSAL CASE DEMAND HAS TO BE DONE, BEFORE REVERSAL
EXEC SAM_sDemand_INS @p_BrID, @p_PrdID, @m_BusnDt, @p_LoginID,@m_ValueDt
EXEC SAM_sAutoReversal @p_BrID, @p_PrdID,Null,@m_BusnDt, @p_LoginID, @m_ValueDt –<< ONCE REVERSAL ROUTINE COMPLETE SET THE VALUE AS EMPTY
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’)
END
ELSE IF @p_Purpose = ‘REPCALC’ BEGIN –<<EDA06FEB06
SELECT BrID, PrdID, AcctID, TotOS, Principal,
dbo.SAM_fCurrRndFormat(IsNull(Interest,0)) As INTEREST,
dbo.SAM_fCurrRndFormat(IsNull(Penalty,0)) As Penalty,
dbo.SAM_fCurrRndFormat(IsNull(Penal,0)) As Penal
FROM #TmptCalcSlabFinal
Order by BrID,PrdID,AcctID
END Error:
IF @@Error <> 0 Or IsNull(@m_ErrMsgID,”) <> ” BEGIN
–<< For EOT Error Log Purpose >>–
SET @m_KeyValues = @p_BrID + ‘|’ + @p_PrdID
EXEC SAM_sInsertTaskStatus @p_BatchType =’GRP’,@p_KeyFieldNames = ‘BrID|PrdID’,
@p_KeyFieldValues = @m_KeyValues, @p_Count = @m_AcctCnt,
@p_NthRecord = @m_HasZeroMinSlNo,@p_DateTime = @m_SetCurDtTime,
@p_Status=’FAILED’, @p_UserID = @p_LoginID
END
— ELSE BEGIN
— COMMIT TRAN –<< COMMIT THE COMPLETED TRANSACTION
— END
SET NOCOUNT OFF
END Regards
Anil
Never Give Up

Hi, At first glance i saw you are doing
Delete From #TmptIRDetl
Delete From #TmptIRHdr instead of this can you use truncate table #TmptIRDetl
which will be much faster than delete
also it is better to drop temporary tables at the end

Dear Friend I did the changes mentioned by you,but still the execution of the sp from the front end is consuming time Deep Regards
Anil Never Give Up

truncate is more efficient than delete but on the matter of explaining the difference between QA and client code.
1. do not tamper with the network packet size, leave default, you are not helping 2. learn to look at the execution plan, for estimated plan, you will need to create the temp tables first 3. try changing the vb code commandtype to text, then build the entire command as a string
ie,
exec sp_name @p1=’a’, @p2=1 etc
this may sound silly, but this is exactly what QA is doing, and you are not

also look in SQL profiler for all activities while running or making call to sp from
.net code

Dear Ranjith

quote:Originally posted by ranjitjain also look in SQL profiler for all activities while running or making call to sp from
.net code

Profiler Data is captured Below Event Class RPC:Completed
Start Time 2006-06-26 14:54:48.763
End Time 2006-06-26 15:17:30.000
Text Data exec IRC_sPrdIntCalcSKG @p_Parameter = N’8|10|30-04-2006|30-04-2006|30-04-2006|AKK|ACCRUAL’ Application Name .Net SqlClient Data Provider
Reads 47496424
Writes 296
Duration 1326969 Another Interesting Thing is that If I do not pass Any Paramters from Front End To backend but instead hard code it in the back end, the Sp will be called within 4 mts time from the front end. Never Give Up

Dear Friends Somebody kindly assist me in resolving this issue.
It is sitting on my last nerve left
Deep Regards
Anil Never Give Up

You have either lots of user-defined data types – which I don’t think would be a problem – or you’re not in Microsoft SQL Server. Other than that, none of your temp tables has a PK or any indexes. Unless you’re dealing with only small numbers of rows in these temp tables, you might want to look into PKs and indexes. Add indexes after inserting the data for improved performance.

Ah, and why are you not typing the parameters in your VB code?

]]>