Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Cursors - Help Needed

    I am new to this SQL Server Environment and I am really Fed Up of these Cursors but I didn't find a right Solution to overcome this issue, please Help me in this ...

    I have build three Cursors as follows, to update my table, its working fine but takes to much time to execute/update the table ... and I really don't know what to do with this ...

    --------------------------------------------------
    CREATE PROCEDURE rptPhaseD

    @YearMonth as int

    AS

    Declare @Code as int
    Declare @PaidMO as nvarchar(10)

    Begin

    DECLARE rptPhaseD_Cursor CURSOR FOR

    SELECT gpo_id AS Code, COUNT(mno) AS PaidMO
    FROM masterdata
    WHERE (year_month = @YearMonth) AND (service_id = 'B') and (status = 'D') -- Here I am setting the 'Status' as U and Null for other Two Cursors
    GROUP BY gpo_id
    ORDER BY gpo_id

    OPEN rptPhaseD_Cursor

    FETCH NEXT FROM rptPhaseD_Cursor into @Code, @PaidMO

    WHILE @@FETCH_STATUS = 0

    Begin

    Update rptPhase
    Set PaidMO = @PaidMO where Code=@Code and YearMonth = @YearMonth
    FETCH NEXT FROM rptPhaseD_Cursor into @Code, @PaidMO

    End

    CLOSE rptPhaseD_Cursor
    DEALLOCATE rptPhaseD_Cursor

    End
    GO
    --------------------------------------------------

    To execute all of the three cursors I am calling another Stored Procedure as;

    --------------------------------------------------
    CREATE PROCEDURE Phase

    @YearMonth as int

    AS

    Begin
    Execute rptPhaseD @YearMonth
    Execute rptPhaseU @YearMonth
    Execute rptPhaseNull @YearMonth

    End
    GO
    --------------------------------------------------

    I have a total amount of 10 billion records and the Procedures I am calling takes a long time to execute and I have to do it daily. Please help me in this ...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    10 billion records is a lot - especially when you are going over them one by one... Let's see if we can find you a set-based solution.

    Now, this should get you started, but then again my head isn't working at all today, so hopefully someone can help you fix it just right anyway
    Code:
    UPDATE rptPhase
    SET    PaidMO = x.PaidMO
    FROM   rptPhase
     INNER
      JOIN (
            SELECT masterdata.gpo_id
                 , Count(mno) As PaidMO
            FROM   rptPhase
             INNER
              JOIN masterdata
                ON masterdata.gpo_id = rpt_Phase
            WHERE  masterdata.year_month = @YearMonth
            AND    masterdata.service_id = 'B'
            AND    masterdata.status = 'D'
            GROUP
                BY masterdata.gpo_id
           ) As x
        ON x.gpo_id = rptPhase.Code
    WHERE  rptPhase.YearMonth = @YearMonth
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I sometimes notice you are better off snapping data to a temp table then joining, especially, for some reason, with SQL 2005.

  4. #4
    Join Date
    Mar 2009
    Posts
    349
    mebbe.

    I would say that depends on hardware and configuration. Temp on another disk etc...

  5. #5
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by PMASchmed
    I sometimes notice you are better off snapping data to a temp table then joining, especially, for some reason, with SQL 2005.
    So you can do a better job than the optimizer?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The optimizer is not always right.
    Computers are not always right.
    Humans are not always right either.
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2009
    Posts
    349
    watch out georgie. you are arguing with a guru.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    guru a guru
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •