Run time of Cursor

  • 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

  • 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?! :w00t:

    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[/url]

    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

  • 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-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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Yeah, it does look more like Jeff Moden nighmare :w00t:

    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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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[/url]

    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

  • Abu Dina (10/15/2012)


    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.

    Excellent point well made. You wouldn't want to change all of those cursor blocks in one go - testing nightmare.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Really very thanks for all, i will work on it to increase the performance.

    thanks alot.

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

    I am Learner -- SQL

  • Really very thanks for all, i will work on it to increase the performance.

    thanks alot.

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

    I am Learner -- SQL

  • To answer the original question...

    Make a small modificaton to the code around each cursor. Put a SET @StartTime = GETDATE() at the beginnning of each cursor an PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) along with the message of your choice to identify the duration at the end of each cursor.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the help. we sort out the problem.we rework on the cursor which taking more time.

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

    I am Learner -- SQL

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply