Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Is there any way to solve long cancelling time on a long-running query?

    I'm dealing with data with 11 millions rows. Everytime when I need to update a certain columns where source table is another 11-millions-rows table, it takes forever, and hence, we try to cancel it. However, the roll-back time irritates us too. When we tried to cancel a updating query which has been running for 9 hours,it took another 9 hours to be cancelled. Is there anyway to tackle the long cancelling hours? Somebody please enlightens us!

  2. #2
    Join Date
    Sep 2011
    Posts
    71
    Hello ,Just i have an idea :To speed your update process try it and give me the feedback

    update your target table from that staging table using the MERGE command
    Check out the MSDN docs and a great blog post on how to use the MERGE command.

    Basically, you create a link between your actual data table and the staging table on a common criteria (e.g. a common primary key), and then you can define what to do when

    the rows match, e.g. the row exists in both the source and the target table --> typically you'd either update some fields, or just ignore it all together
    the row from the source doesn't exist in the target --> typically a case for an INSERT
    You would have a MERGE statement something like this:

    MERGE TargetTable AS t
    USING SourceTable AS src
    ON t.PrimaryKey = src.PrimaryKey

    WHEN NOT MATCHED THEN
    INSERT (list OF fields)
    VALUES (list OF values)

    WHEN MATCHED THEN
    UPDATE
    SET (list OF SET statements)
    ;
    Of course, the ON clause can be much more involved if needed. And of course, your WHEN statements can also be more complex, e.g.

    WHEN MATCHED AND (some other condition) THEN ......
    and so forth.

    MERGE is a very powerful and very useful new command in SQL Server 2008 - use it, if you can!

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Updating 11 million rows should not take very long.

    Post the update query you are using.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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