| Author |
Topic |
|
aarem
Starting Member
5 Posts |
 Posted - 2014-06-29 : 06:23:43
|
| Dear Expert, I am trying to execute stored procedure and inserting the result in a temporary table in a Cursor. There are 5000+ records in a Cursor. And it is taking very long time (6 minutes). I have tried to optimize this process using While Loop instead of Cursor and used Index too in temporary table but no improvement. How can i optimize this process. Kindly suggest. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-29 : 07:21:43
|
Rewrite your code to a set-based solution.It will probably finish much faster. Probably in a matter of seconds.Show us the code.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
aarem
Starting Member
5 Posts |
Posted - 2014-06-29 : 23:43:07
|
| The stored procedure is as below:CREATE Procedure sp_MTEST @SummaryOnlyTF NVARCHAR(1),@GlPrType NVARCHAR(1),@Filter NVARCHAR(MAX),@AppType NVARCHAR(1) ,@FLD VARCHAR(3),@REPTYPE NVARCHAR(1),@FORGRID VARCHAR(1) AS SET NOCOUNT ON Declare @ORGname nvarchar(42) Declare @Brname nvarchar(30) Declare @BrStr nvarchar(6) Declare @ccydiv nvarchar(3) select @OrgName= OrgName from orgparms Select @Brname = BrName, @BrStr=BrStr from Brparms select @ccydiv = ccydiv from ccy declare @Acc nvarchar(11) declare @IntAmt numeric(18,3) declare @NextActDate Datetime DECLARE @FutureDays INTEGER DECLARE @PenAmt NUMERIC(18,3) DECLARE @IntODueAmt NUMERIC(18,3) DECLARE @TaxAmt NUMERIC(18,3) DECLARE @TrnChgAmt NUMERIC(18,3) DECLARE @AcrIntAmt NUMERIC(18,3) DECLARE @AcrPenAmt NUMERIC(18,3) DECLARE @AcrChgAmt NUMERIC(18,3) DECLARE @DebitIntAmt NUMERIC(18,3) DECLARE @CreditIntAmt NUMERIC(18,3) DECLARE @gCurrRunDate Datetime DECLARE @cCurrRunDate Datetime DECLARE @ValueDate DATETIME DECLARE @InclEOM SMALLINT DECLARE @EomDate DATETIME DECLARE @DaysCount NUMERIC(18,3) DECLARE @VID NVARCHAR(2) SELECT @VID = CASE WHEN @FLD = '1' THEN '61' WHEN @FLD = '2' THEN '62' WHEN @FLD = '3' THEN '63' WHEN @FLD = '4' THEN '64' WHEN @FLD = '5' THEN '65' END Declare @Runstatus numeric(1) select @Runstatus = Runstatus from BrParms select @cCurrRunDate = CurrRunDate from BrParms select @gCurrRunDate = CurrRunDate from BrParms select @incleom = incleom from orgparms SET @ValueDate = DATEADD(DAY, @InclEOM, @gCurrRunDate) Set @EomDate = (select EOMdate from calendar C, Brparms B where Month(B.currrundate) = C.monthnumber and Year(B.CurrRundate)=C.YearNumber) SET @DaysCount = DATEDIFF(DAY, @gCurrRunDate, @ValueDate) SET @DaysCount = case when @gcurrRundate = @Eomdate and @Runstatus<>1 then @DaysCount else 0 end Set @gcurrRundate = case when @gcurrRundate = @Eomdate then @Valuedate else @gcurrRundate end CREATE TABLE #TempA ( Cid NVARCHAR(6) , Acc NVARCHAR(11) NOT NULL, AppType NVARCHAR(1) , BalAmt NUMERIC(18,3) , AvailBalAmt NUMERIC(18,3) , IntRate NUMERIC(18,3) , AmtForClearing NUMERIC(18,3) , AmtForInt NUMERIC(18,3) , NormalIntAmt NUMERIC(18,3) , BonusIntAmt NUMERIC(18,3) , ODIntAmt NUMERIC(18,3) , TaxOnIntAmt NUMERIC(18,3) , Rules NVARCHAR(24) ,PRIMARY KEY (Acc) ) /*CREATE NONCLUSTERED INDEX [IX_Person_Test_LastName] ON [DBO].[#TempA] ( [ACC] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/ IF @AppType = '1' BEGIN -- SELECT T1.*,T2.phone1, ltrim(rtrim(isnull(T2.line1,''))+' '+rtrim(isnull(T2.line2,''))+' '+rtrim(isnull(T2.line3,''))) as addr INTO #TempTable1 --FROM --( SELECT identity(int,1,1) as rowID, @cCurrRunDate as systemdate,@OrgName as Orgname, rtrim(@BrName)+' - '+rtrim(@BrStr) as Brname, 1 as Apptype, s.Acc,s.Chd,s.glcode, s.OpenDate, S.ccytype,S.prtype,P.fulldesc as Prdesc, S.MatDate, '['+C.Cid+']' as Cid, G.fulldesc, A.phone1, c.mobile1,c.mobile2, c.mobile1+mobile2 as phone2, C.taxcode,S.Custtrndate as lasttrndate,S.balamt/C1.ccydiv as Balamt, case when DateDiff(mm,s.Opendate,s.Matdate)<0 then null else DateDiff(mm,s.Opendate,s.Matdate) end as Term, S.intrate,S.inteffdate,S.Accstatus,S.ContractAmt/C1.ccydiv as Contractamt,S.AcrintAMT,S.IntBalAmt, S.cumintpdamt/C1.ccydiv as cumintpdamt,S.cumtaxwamt/C1.ccydiv as cumtaxwamt, CAST (0 as numeric (18,3)) as Mdint, --LTRIM(RTRIM(C.DisplayName)) as name, LTRIM(RTRIM(ISNULL(C.Name1,''))) +' '+LTRIM(RTRIM(ISNULL(C.Name2,''))) AS NAME, ltrim(rtrim(isnull(a.line1,''))+' '+rtrim(isnull(a.line2,''))+' '+rtrim(isnull(a.line3,''))) as addr , Tx.taxrate,CAST (0 as numeric (18,3)) as TaxA,LOOKUPID = @VID,'CIFCODE'= CASE WHEN @FLD = '1' THEN ltrim(rtrim(c.CIFCODE1)) WHEN @FLD = '2' THEN ltrim(rtrim(c.CIFCODE2)) WHEN @FLD = '3' THEN ltrim(rtrim(c.CIFCODE3)) WHEN @FLD = '4' THEN ltrim(rtrim(c.CIFCODE4)) WHEN @FLD = '5' THEN ltrim(rtrim(c.CIFCODE5)) WHEN @FLD = '6' THEN ltrim(rtrim(c.CIFCODE6)) WHEN @FLD = '7' THEN ltrim(rtrim(c.CIFCODE7)) WHEN @FLD = '8' THEN ltrim(rtrim(c.CIFCODE8)) WHEN @FLD = '9' THEN ltrim(rtrim(c.CIFCODE9)) END INTO #TempTable1 FROM SVACC s , CIF c , Relacc R, Gllink G , strprocPrimaryAddress A , Ccy C1,Prparms P, (select code,taxrate from Tabletx) Tx where s.Acc = R.Acc and C.CID = R.CID and C.cid*= A.cid and R.Type = '010' and G.code= S.glcode and G.tableid='10' and S.ccytype=C1.code and --s.accstatus<>'99' S.ACCSTATUS NOT IN('99','C3') --and S.balamt>0 and S.balamt>=0 and S.prtype = P.prtype and P.apptype='1' and C.taxcode=Tx.code order by rowID,s.glcode,s.acc --)T1 LEFT JOIN strprocPrimaryAddress T2 ON T1.CID=T2.CID order by T1.glcode,T1.acc/*CREATE NONCLUSTERED INDEX [IX_Person_Test_LastName0] ON [DBO].[#TempTable1] ( [ACC] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] */Update T set T.Intrate = R.Intrate from #TempTable1 T, Rates r where T.prtype = R.prtype and R.apptype ='1' and R.ToDate>=@cCurrRunDate and T.balamt between r.fromamt/@ccydiv and r.toamt/@ccydiv and R.prtype in (select prtype from prparms where dbo.pos('G', rules)>0 and apptype = '1') DECLARE CURSOR_0 CURSOR LOCAL FOR SELECT Acc FROM #TempTable1 ORDER BY ACC OPEN CURSOR_0 FETCH CURSOR_0 into @Acc while (@@FETCH_STATUS = 0 ) begin INSERT INTO #TempA EXEC DBO.sp_Test @Acc, @gCurrRunDate UPDATE T SET T.MDINT = T.ACRINTAMT+T1.NORMALINTAMT FROM #TEMPTABLE1 T, #TEMPA T1 WHERE T.ACC =T1.ACC AND T.ACC=@ACC FETCH CURSOR_0 into @Acc end CLOSE CURSOR_0 DEALLOCATE CURSOR_0 UPDATE #TempTable1 SET TAXA =round( MDINT*TAXRATE/100,0) SELECT * FROM #TempTable1 ORDER BY ACC END |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-30 : 05:05:44
|
This is the culpritINSERT INTO #TempA EXEC DBO.sp_Test @Acc, @gCurrRunDate We need to know why you have decided to execute this stored procedure per row instead of treating it like a set.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
aarem
Starting Member
5 Posts |
Posted - 2014-07-01 : 06:14:52
|
| Exjactly I know this particular statement is the CULPRIT. But how shall I process this other way round without changing the code in the procedure "DBO.sp_Test"There are 6000 records in a Temporary Table i.e. #TempTable1. We need to get the value from the stored Procedure " DBO.sp_Test" for each Acc field in each row of this table. So how shall I do it like a set instead of iterating through the record. Kindly suggest. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-01 : 07:13:38
|
You can't. The person who wrote the procedure sp_Test did not know much about set-based thinking and wrote the procedure to deal with rows instead of sets.The change you need is a paradigm change. Either utilize the full power of your database or keep thinking small.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
aarem
Starting Member
5 Posts |
Posted - 2014-07-01 : 07:28:13
|
| I didn't get you. What do u mean by "The change you need is a paradigm change. Either utilize the full power of your database or keep thinking small." Is there any possibility to get it done without changing the stored Procedure? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-01 : 07:38:31
|
quote:
Originally posted by aarem
Is there any possibility to get it done without changing the stored Procedure?
No.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
Query Optimization