Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Query Optimization

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
Go to Top of Page

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-30 : 05:05:44
This is the culprit
INSERT 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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -