Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Run time of Cursor Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 3:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 3, 2014 8:03 PM
Points: 51, Visits: 439
hi all

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanks
Selva


--------------------------------------------------------

I am Learner -- SQL
Post #1372627
Posted Monday, October 15, 2012 3:42 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
selvakumar.sms (10/15/2012)
hi all

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanks
Selva


45 cursors?!

I'm not even going to ask what your sproc does. It could be a very complicated process you're doing over there or it could be Jeff Moden's worst nightmare lol. Are these cursors modifying data row by row or are they being used to drive a complicated ETL system or something?

On a serious note, use a logging table where you insert a row for each beginning and end of a cursor. You can then review the result of the log table for times of each cursor.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1372629
Posted Monday, October 15, 2012 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 11:02 PM
Points: 18, Visits: 274
Hi Dude,

Instead of using CURSOR you can try WHILE loop like below example.
If possible dont use CURSORs in the procedure.

DECLARE @tblMain AS TABLE (dateC DATETIME, valC FLOAT)
DECLARE @tblMain2 AS TABLE (ID INT IDENTITY, dateC DATETIME, valC FLOAT)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '31 aug 2010', 1.0)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '07 sep 2010', 1.5)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '14 sep 2010', 7.2)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '21 sep 2010', 6.3)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '28 sep 2010', 6.8)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '30 sep 2010', 6.812)

INSERT INTO @tblMain2( dateC, valC )
select DateC, ValC from @tblMain ORDER BY dateC

DECLARE @i AS INT
DECLARE @cnt AS INT
SET @i = 1
SET @cnt = 0
SELECT @cnt = COUNT(ID) FROM @tblMain2
select DateC, ValC from @tblMain2
WHILE (@i < @cnt)
BEGIN
UPDATE @tblMain2 SET valC = valC / 2 WHERE id = @i
SET @i = @i + 1
END
select DateC, ValC from @tblMain2


SQL Sever Performance tuning
Post #1372637
Posted Monday, October 15, 2012 5:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,075, Visits: 6,441
sql-programmers (10/15/2012)
Hi Dude,

Instead of using CURSOR you can try WHILE loop like below example.
If possible dont use CURSORs in the procedure.



A well written forward-only readonly cursor will perform similarly to a WHILE loop. Changing this stored procedure to using WHILE loops instead of cursors will cost a great deal of time and run the risk of introducing errors - and may not improve performance at all.
A stored procedure using 35 cursors suggests naive coding - but it may not be the case; maintenance routines commonly (and correctly) use cursors. Without seeing the code, you're guessing.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372651
Posted Monday, October 15, 2012 5:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,075, Visits: 6,441
selvakumar.sms (10/15/2012)
hi all

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanks
Selva


It would help us a great deal if you could post the code - or part of it, if it's lengthy. If you choose to post only a part of it, make sure you include at least a couple of the cursor loops so folks can get some idea of what it's supposed to do.

If it's not realistic to convert the sp to set-based coding, then there may still be some scope for improving the performance of the sp by changing the definition and use of the cursors.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372653
Posted Monday, October 15, 2012 5:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 3, 2014 8:03 PM
Points: 51, Visits: 439
hi all

this is my small part if code, i dont know which cursor taking more time to consolidate the data.

USE [databasename]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Below is the modified SP for the Grammage accounts not flowing issue in Bill Cancellation



alter PROCEDURE [dbo].[TQConsolidateDataForARTransaction]
-- @BusinessDate as Datetime /* POSS-SYNCH: Line Commented */
@LocationCode as varchar(20)
,@LastExtractedSequenceNo int /* POSS-SYNCH: New parameter */
,@ExtractSequenceNo int /* POSS-SYNCH: New parameter */
,@BusinessDate datetime /* Added on 15-Jul-08. Data will be fetched based on this date */
AS
Declare
@iMaxRecordNo as int
,@iStatus as int
,@Amount as Decimal
,@AccountNo as int
,@DocNo as int
,@CNNo as int
,@Fiscalyear as int
,@LineItemNo as int
,@PaymentCode as varchar (20)
,@CustomerCode as varchar(20)
,@ServiceChargeReasonID as varchar(20)
,@OtherChargesReasonID as varchar(20)
,@OtherCharges as decimal
,@ServiceCharges as decimal ,@DocDate as datetime
,@Type as varchar (20)
,@MemoLine as varchar(20)
,@TempCNNo as int
,@Reference as varchar(20)
,@GFRecieptNo as int
--,@BusinessDate as datetime
,@GHSAccountPrefix as varchar(3) -- Added on 31-Dec-07
,@IsGoldScheme as bit -- Added on 31-Dec-07
,@AmountWithDecimal as DECIMAL(18,2)

,@LineType as varchar (20) --Added For ASSM on 22-apr-2010
,@DiscountOnLabourCharges as Decimal--Added For ASSM on 22-apr-2010
,@DiscountOnMaterialCharges as Decimal--Added For ASSM on 22-apr-2010
,@DiscountOnStoneCharges as Decimal--Added For ASSM on 22-apr-2010
,@LabourCharges as Decimal--Added For ASSM on 22-apr-2010
,@MaterialCharges as Decimal--Added For ASSM on 22-apr-2010
,@SalesTax as Decimal--Added For ASSM on 22-apr-2010
,@ServiceTax as Decimal--Added For ASSM on 22-apr-2010
,@StoneCharges as Decimal--Added For ASSM on 22-apr-2010

/* Dummy date variable to store businessdate selected from query.
This variable is not used. This was earlier used for capturing delta oracle data */
Declare @BusinessDateTemp as datetime -- # EOD Data Fetch 15-Jul-08

SET @LineItemNo=0
IF not exists (SELECT * FROM TemplateARTransactions WHERE LocationCode = @LocationCode ) /*POSS-SYNCH: Included LocationCode in WHERE condition */
SET @iMaxRecordNo = 1
ELSE
SELECT @iMaxRecordNo = (MAX(RecordNumber) + 1) FROM TemplateARTransactions WHERE LocationCode = @LocationCode /*POSS-SYNCH: Included LocationCode in WHERE condition */

-- # EOD Data Fetch 15-Jul-08
DECLARE ARTransaction_Cursor CURSOR FOR


SELECT
CreditNote.Amount
,CreditNote.FiscalYear
,RefGHAccountDocNo
,CreditNote.DocNo
,CreditNote.DocDate /*POSS-SYNCH: This will be the current business date for the record*/
FROM
GH
INNER Join GHDetails ON
GH.DocNo=GHDetails.RefGHAccountDocNo
and GH.FiscalYear=GHDetails.REFAccountCodeFiscalYear
and GH.LocationCode=GHDetails.LocationCode
INNER Join CreditNote ON
GHDetails.DocNo=CreditNote.RefDocNo
and GHDetails.FiscalYear=CreditNote.RefFiscalYear
and GHDetails.Locationcode=CreditNote.Locationcode
INNER join LocationMaster ON
CreditNote.LocationCode=LocationMaster.LocationCOde
WHERE
CreditNote.DocDate=@BusinessDate -- # EOD Data Fetch 15-Jul-08
AND OwnerInfo<>'Level 3'
AND GH.Status<>0
AND GH.LocationCode=@LocationCode
And CreditNoteType='GH'
AND RefDocType = 'GH' -- Added on 04-Dec-06 as part of bug fix
/*POSS-SYNCH: Sequence logic based fetching included*/
-- AND GH.OracleSeqNo > @LastExtractedSequenceNo -- # EOD Data Fetch 15-Jul-08
-- AND GH.OracleSeqNo <= @ExtractSequenceNo -- # EOD Data Fetch 15-Jul-08


OPEN ARTransaction_Cursor

SET @LineItemNo=1
FETCH NEXT FROM ARTransaction_Cursor
INTO @Amount,@FiscalYear,@AccountNo,@CNNo,@BusinessDateTemp /*POSS-SYNCH: @BusinessDate added */

WHILE @@FETCH_STATUS = 0
BEGIN

IF @CNNo>0
BEGIN

INSERT INTO
TemplateARTransactions
(
TransDate
,GLDate
,HeaderAttribute
,Reference
,LinkToLine
,Currency
,Source
,Type
,CustCode
,CustName
,SalesPerson
,MemoLine
,Quantity
,UnitPrice
,Amount
,TaxCode
,PONumber
,TransDescription
,LineType
,PaymentTerms
,Organisation
,Location
, LocationCode
,RecordNumber
,BusinessDate
,VersionNumber
,LastModifiedDate
)
VALUES
( @BusinessDate
, Convert(Datetime,@BusinessDate,105)
,@LocationCode+cast(@Fiscalyear as varchar(5)) + 'CN'+cast(@CNNo as Varchar(20))
,@LocationCode+cast(@Fiscalyear as varchar(5)) +'CN'+cast(@CNNo as Varchar(20))+'/'+CAST(@LineItemNo as Varchar(5))
, Null
,'INR'
,'EPOSS',
@LocationCode+'GH'
,Null,
@LocationCode+'GH
',@LocationCode
,'GH Closure'
,'1'
,@Amount
,@Amount
,Null
,'GH'+cast(@AccountNo as varchar (20))
,@LocationCode
,'Line'
,'D001'
,'B'+@LocationCode
,'B'+@LocationCode
,@LocationCode
,@iMaxRecordNo
,@BusinessDate
,Null
,@BusinessDate
)
SET @iMaxRecordNo=@iMaxRecordNo+1
SET @LineItemNo=@LineItemNo+1
END

FETCH NEXT FROM ARTransaction_Cursor
INTO @Amount,@FiscalYear,@AccountNo,@CNNo,@BusinessDateTemp /*POSS-SYNCH: @BusinessDate added */

END

CLOSE ARTransaction_Cursor
DEALLOCATE ARTransaction_Cursor

/*POSS-SYNCH: Conversion completed for ARTransaction_Cursor*/
-- # EOD Data Fetch 15-Jul-08
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

-- # EOD Data Fetch 15-Jul-08
------- To capture gh reverse wrong entry -------
DECLARE GHCancel_Cursor CURSOR FOR

SELECT
GHDetails.TotalAmount
,RefGHAccountDocNo
,PD.PaymentCode
,GH.FiscalYear
,GH.LastModifiedDate/*POSS-SYNCH: This will be the current business date for the record*/
FROM
GH
INNER Join GHDetails ON
GH.DocNo=GHDetails.RefGHAccountDocNo
and GH.FiscalYear=GHDetails.REFAccountCodeFiscalYear
and GH.LocationCode=GHDetails.LocationCode
INNER join LocationMaster ON
GH.LocationCode=LocationMaster.LocationCOde
INNER Join PaymentDetails PD ON
GHDetails.DocNo=PD.RefDocNo
and GHDetails.FiscalYear=PD.RefFiscalYear
and GHDetails.Locationcode=PD.Locationcode
WHERE
GHDetails.LastModifiedDate=@BusinessDate -- # EOD Data Fetch 15-Jul-08
AND GH.LocationCode=@LocationCode
AND GHDetails.Status=3
AND OwnerInfo<>'Level 3'
AND PD.DocType='GHPMT'
/*POSS-SYNCH: Sequence logic based fetching included*/
-- AND GH.OracleSeqNo > @LastExtractedSequenceNo -- # EOD Data Fetch 15-Jul-08
-- AND GH.OracleSeqNo <= @ExtractSequenceNo -- # EOD Data Fetch 15-Jul-08

OPEN GHCancel_Cursor


SET @LineItemNo=1
FETCH NEXT FROM GHCancel_Cursor
INTO @Amount,@AccountNo,@PaymentCode,@FiscalYear,@BusinessDateTemp /*POSS-SYNCH: @BusinessDate added */

WHILE @@FETCH_STATUS = 0
BEGIN


INSERT INTO
TemplateARTransactions
(
TransDate
,GLDate
,HeaderAttribute
,Reference
,LinkToLine
,Currency
,Source
,Type
,CustCode
,CustName
,SalesPerson
,MemoLine
,Quantity
,UnitPrice
,Amount
,TaxCode
,PONumber
,TransDescription
,LineType
,PaymentTerms
,Organisation
,Location
, LocationCode
,RecordNumber
,BusinessDate
,VersionNumber
,LastModifiedDate
)
VALUES
(@BusinessDate
, Convert(Datetime,@BusinessDate,105)
,@LocationCode+cast(@Fiscalyear as varchar(5))+'GH'+cast(@AccountNo as Varchar(20))
,@LocationCode+cast(@Fiscalyear as varchar(5))+'GH'+cast(@AccountNo as Varchar(20))+'/'+CAST(@LineItemNo as Varchar(5))
, Null
,'INR'
,'EPOSS'
,@LocationCode+@PaymentCode
,Null
,@LocationCode+'GH
',@LocationCode
,'GH Receipt Cancelled'
,'1'
,@Amount
,@Amount
,Null
,'GH'+cast(@AccountNo as varchar (20))
,@LocationCode
,'Line'
,'D001'
,'B'+@LocationCode
,'B'+@LocationCode
,@LocationCode
,@iMaxRecordNo
,@BusinessDate
,Null
,@BusinessDate
)
SET @iMaxRecordNo=@iMaxRecordNo+1
SET @LineItemNo=@LineItemNo+1


FETCH NEXT FROM GHCancel_Cursor
INTO @Amount,@AccountNo,@PaymentCode,@FiscalYear,@BusinessDateTemp /*POSS-SYNCH: @BusinessDate added */

END
CLOSE GHCancel_Cursor
DEALLOCATE GHCancel_Cursor

/*POSS-SYNCH: Conversion completed for GHCancel_Cursor*/
-- # EOD Data Fetch 15-Jul-08
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

---Cheque Dishonour
-- /*POSS-SYNCH Cnversioin NOT DONE for this part...*/
/* Removed as per request from Gopal.
Removel Date 02-Nov-06
Reference - FSD_ChequeBounce_1.0.doc */
/*
DECLARE Trans_Cheque_Cursor CURSOR FOR
SELECT
Amount
,InstrumentNo
,ChequeDate
,SapCode
FROM ChequeBounce
Inner Join LocationMaster on
ChequeBounce.LocationCode=LocationMaster.LocationCOde
where ChequeBounce.LocationCode=@LocationCode
and ChequeBounce.BusinessDate=@BusinessDate
and OwnerInfo<>'Level 3'

OPEN Trans_Cheque_Cursor
FETCH NEXT from Trans_Cheque_Cursor
BEGIN
@Amount
,@DocNo
,@DocDate
,@CustomerCode

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FiscalYear= FiscalYear
FROM DayMaster
where BusinessDate=@BusinessDate And LocationCode=@LocationCode

Set @LineItemNo=1

insert
BEGIN TemplateARTransactions
(
TransDate
,GLDate
,HeaderAttribute
,Reference
,LinkToLine
,Currency
,Source
,Type
,CustCode
,CustName
,SalesPerson
,MemoLine
,Quantity
,UnitPrice
,Amount
,TaxCode
,PONumber
,TransDescription
,LineType
,PaymentTerms
,Organisation
,Location
, LocationCode
,RecordNumber
,BusinessDate
, VersionNumber
,LastModifiedDate
)
Values
(
@DocDate
,Convert(Datetime,@DocDate,105)
,@LocationCode+cast(@Fiscalyear as varchar(5))+'CD'+cast(@DocNo as Varchar(20))
,@LocationCode+cast(@Fiscalyear as varchar(5))+'CD'+cast(@DocNo as Varchar(20))+'/'+cast(@LineItemNo as Varchar(20))
, Null
,'INR'
,'EPOSS'
,@LocationCode+'CHEQUE'
,@CustomerCode ,@LocationCode
,@LocationCode
,'Cheque Dishonour'
,'1'
,@Amount
,@Amount
,Null
,'CD'+Replace(Convert(Varchar,@DocDate,105),'-','')
,@LocationCode
,'Line'
,'D001'
,'B'+@LocationCode
,'B'+@LocationCode
,@LocationCode
,@iMaxRecordNo
,@BusinessDate
,Null
,@BusinessDate
)
Set @iMaxRecordNo=@iMaxRecordNo+1
Set @TempCNNo= @CNNo

FETCH NEXT from Trans_Cheque_Cursor
BEGIN
@Amount
,@DocNo
,@DocDate
,@CustomerCode
END
CLOSE Trans_Cheque_Cursor
DEALLOCATE Trans_Cheque_Cursor




help me out:)


--------------------------------------------------------

I am Learner -- SQL
Post #1372655
Posted Monday, October 15, 2012 6:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,075, Visits: 6,441
selvakumar.sms (10/15/2012)
hi all

this is my small part if code, i dont know which cursor taking more time to consolidate the data.



Thanks for posting this. The first cursor loop (ARTransaction_Cursor) can be easily converted to a set-based query as follows;
INSERT INTO TemplateARTransactions ( 
TransDate
,GLDate
,HeaderAttribute
,Reference
,LinkToLine
,Currency
,Source
,Type
,CustCode
,CustName
,SalesPerson
,MemoLine
,Quantity
,UnitPrice
,Amount
,TaxCode
,PONumber
,TransDescription
,LineType
,PaymentTerms
,Organisation
,Location
,LocationCode
,RecordNumber
,BusinessDate
,VersionNumber
,LastModifiedDate
)
SELECT
@BusinessDate
, Convert(Datetime,@BusinessDate,105)
,@LocationCode+cast(cn.FiscalYear as varchar(5)) + 'CN'+cast(cn.DocNo as Varchar(20))
,@LocationCode+cast(cn.FiscalYear as varchar(5)) +'CN'+cast(cn.DocNo as Varchar(20))+'/'+CAST(LineItemNo as Varchar(5))
, Null
,'INR'
,'EPOSS',
@LocationCode+'GH'
,Null,
@LocationCode+'GH
',@LocationCode
,'GH Closure'
,'1'
,cn.Amount
,cn.Amount
,Null
,'GH'+cast(RefGHAccountDocNo as varchar (20))
,@LocationCode
,'Line'
,'D001'
,'B'+@LocationCode
,'B'+@LocationCode
,@LocationCode
,MaxRecordNo
,@BusinessDate
,Null
,@BusinessDate

FROM (
SELECT
cn.Amount, -- @Amount
cn.FiscalYear, -- @FiscalYear
RefGHAccountDocNo, -- @AccountNo
cn.DocNo, -- @CNNo
cn.DocDate, -- @BusinessDateTemp
LineItemNo = ROW_NUMBER() OVER(ORDER BY GH.DocNo), -- check that this is the correct sort order
MaxRecordNo = x.iMaxRecordNo + ROW_NUMBER() OVER(ORDER BY GH.DocNo) -- and this
FROM GH
INNER Join GHDetails d
ON GH.DocNo = d.RefGHAccountDocNo
and GH.FiscalYear = d.REFAccountCodeFiscalYear
and GH.LocationCode = d.LocationCode
INNER Join CreditNote cn
ON d.DocNo = cn.RefDocNo
and d.FiscalYear = cn.RefFiscalYear
and d.Locationcode = cn.Locationcode
INNER join LocationMaster lm
ON cn.LocationCode = lm.LocationCOde
CROSS APPLY (
SELECT iMaxRecordNo = MAX(RecordNumber) + 1
FROM TemplateARTransactions
WHERE LocationCode = @LocationCode
) x
WHERE cn.DocDate = @BusinessDate -- # EOD Data Fetch 15-Jul-08
AND OwnerInfo <> 'Level 3'
AND GH.Status <> 0
AND GH.LocationCode = @LocationCode -- parameter
And CreditNoteType = 'GH'
AND RefDocType = 'GH'
and cn.DocNo > 0 --CNNo>0
) d

Untested - no sample data.

You can see that it's very simple to do. This should perform far faster than the cursor equivalent.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372685
Posted Monday, October 15, 2012 6:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
Yeah, it does look more like Jeff Moden nighmare

Or, may be, is taken from Oracle...

Your given example doesn't contain anything qualifying use of cursors (or WHILE LOOPs, which may even be slower than properly implemented cursors).

Having 45 cursors means you are trying to do some complex (or at least multi-step processing). In order to help we will need much more details and explanation of requirements. Please follow the link at the bottom of my signature which will lead you to the tips how to get bets help in shortest time on this forum.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1372688
Posted Monday, October 15, 2012 6:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,075, Visits: 6,441
selvakumar.sms (10/15/2012)
hi all

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanks
Selva


If you are unwilling to optimise all of this code as set-based queries, which most folks would recommend, then there is some scope for improving the performance of the cursors. I recommend you read this excellent article by Hugo Kornelis.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372691
Posted Monday, October 15, 2012 6:51 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
With 45 cursors in one sproc he will never know which ones are taking the longest unless he implements some logging.

This is why I suggested he inserts a record into a log table at the beginning and end of each cursor. He can then work out from his log table the cursors which are taking the longest time?

At least then he can concentrate on tackling the worst cursors rather than recoding them all to set based.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1372703
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse