SQL Server Performance

Updating Millions of records Efficiently

Discussion in 'Analysis Services/Data Warehousing' started by asvforce, Oct 5, 2004.

  1. asvforce New Member

    I have two tables in MS SQL SERVER, as following

    1. Table1 with 1000 Records with three fields a, b and c

    2. Table2 with 13+ Million Records with three fields x, a, b, c, d, e and f

    Now i want to update the fields Table2.b and Table2.c with Table1.b and Table1.c respectively by field "a" of Table1 and Table2.

    Currently if i am using a join condition in update statement as given below:
    "update Table2 set b = Table1.b, c = Table1.c from Table1, Table2
    where Table1.a = Table2.a"

    This update is taking many hours to do update.

    Can u please suggest if i have any other options to update the table much faster than this?

    Thanks in Advance.
    Ashish.


    Ashish Patel
    (Programmer)
    Vadodara - India
  2. Luis Martin Moderator

    Kindly don't duplicate posts.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. Adriaan New Member

    Officially, you should be using the JOIN syntax:

    update Table2 set b = Table1.b, c = Table1.c
    from Table1 INNER JOIN Table2
    ON Table1.a = Table2.a

    Not sure if it will improve the performance. What kind of indexes do you have on these two tables? Sounds as if one or two may be missing.
  4. Luis Martin Moderator

    Brad, Satya please lock this one.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  5. satya Moderator

Share This Page