Post reply

create cursor in sql

  • August 16, 2018 at 2:36 am

    #360431

    Hi everyone ,

    I want to create simple update cursor in sql.
    following sql take almost 1 hour to execute .please help. 
    UPDATE [DUMP_JUL2018] SET [VFLAG] ='DELIN' FROM [DUMP_JUL2018] A INNER JOIN [SPOOL] B
    ON A.AGRNO=B.AGRNO
    DUMP_JUL2018 = 30 lac rows  and SPOOL =10 lac row

    OR another way to fast update.

    regards 
    Santosh Ghogare

    Best Regards
    Santosh Ghogare

  • August 16, 2018 at 3:23 am

    #2002010

    Please update on

    Best Regards
    Santosh Ghogare

  • August 16, 2018 at 4:09 am

    #2002013

    Hi,

    You have to go with UPDATE statement only here. But in case you have any index present on that table (which is going to be updated), you can disable those by

    ALTER INDEX <IndexName> ON <TableName> DISABLE;

    prior to doing the update. Once the update is completed, you may need to rebuild the index(es).

  • August 16, 2018 at 4:29 am

    #2002015

    Santosh

    How many rows are being updated?  Please post the actual (not estimated) execution plan.

    John

  • August 16, 2018 at 4:29 am

    #2002016

    only one index available on updated table on AGRNO and if remove this index can effect on my query.

    Best Regards
    Santosh Ghogare

  • August 16, 2018 at 4:34 am

    #2002017

    I do not see any use of the other table (SPOOL), then why are you putting a JOIN with that?

    In case you need to check whether the AGRNO is mapped or not, you can do that using an EXISTS also.

  • August 16, 2018 at 4:42 am

    #2002020

    You can do it in batches so it won't affect other processes:

    CREATE INDEX IX_DUMP_JUL2018_1 ON [DUMP_JUL2018] (AGRNO,[VFLAG])
    CREATE INDEX IX_SPOOL_1 ON [SPOOL] (AGRNO)
    GO
    DECLARE @ROWCOUNT int=-1
    WHILE @ROWCOUNT <> 0 BEGIN
      UPDATE TOP(1000) A
      SET A.[VFLAG] ='DELIN'
      FROM [DUMP_JUL2018] A
      INNER JOIN [SPOOL] B
       ON A.AGRNO=B.AGRNO
      WHERE A.[VFLAG] <>'DELIN'
      SET @ROWCOUNT=@@ROWCOUNT
    END
    GO
    DROP INDEX IX_DUMP_JUL2018_1 ON [DUMP_JUL2018]
    DROP INDEX IX_SPOOL_1 ON [SPOOL]

  • August 16, 2018 at 7:45 am

    #2002064

    Updating 3 million (30 lakh) rows shouldn't take an hour.
    I would suggest that you change your query to one that uses EXISTS

    UPDATE A
      SET [VFLAG] ='DELIN'
    FROM [DUMP_JUL2018] A
    WHERE EXISTS( SELECT 1 FROM [SPOOL] B WHERE A.AGRNO=B.AGRNO);

    If the problem persists, post DDL for tables and indexes, as well as actual execution plan as a .sqlplan file. Read more about how to do it in here: How to Post Performance Problems - SQLServerCentral

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 8 posts - 1 through 7 (of 7 total)

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