SQL Server Performance

Updating Datawarehouse history table - Help reqd.

Discussion in 'Analysis Services/Data Warehousing' started by kathiravan, Jan 5, 2004.

  1. kathiravan New Member

    Hi All,

    I am in the process of updating one of the datawarehouse history tables having 30 million records.

    The table doesnt have any primary key nor has any clustered index to facilitate faster updates. The update takes more than 55 hrs. The script is just updating 4 of the fields in one update statement without any WHERE clause.


    NOTE: The script went into PAGEIOLATCH mode and remaind in that status for a very long
    time and finally after 55 hrs I could able to see some progress in terms of committing the transaction.

    Can you guys please help me to fine tune my onetime update script?

    Your help is really appreciated.

    Regards,
    Kathir
    Ph: 203-205-1113


    kathiravan
  2. satya Moderator

    There are many factors which affect the performance of your database ranging from hardware to good design. You can schedule a window to implement an index on the columns involved in the UPDATE query.

    Any triggers involved during this query?

    For reference review information from this Technethttp://www.microsoft.com/technet/tr...chnet/itcommunity/chats/trans/SQL/sql0909.asp link to fine tune the queries.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. kathiravan New Member

    There is no triggers involved. Index will not help as there is no where clause in the UPDATE query....

    Here is the query:

    UPDATE REL039_DW..Customer_History
    SET MailingStreet1='123 Test St',
    MailingStreet2='123 Test St',
    PropStreet1='123 Test St',
    PropStreet2='123 Test St'

    Thanks
    Giri


    quote:Originally posted by satya

    There are many factors which affect the performance of your database ranging from hardware to good design. You can schedule a window to implement an index on the columns involved in the UPDATE query.

    Any triggers involved during this query?

    For reference review information from this Technethttp://www.microsoft.com/technet/tr...chnet/itcommunity/chats/trans/SQL/sql0909.asp link to fine tune the queries.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    kathiravan
  4. Twan New Member

    It may be quicker to do a

    select MailingStreet1=convert( orig_col_datatype, '123 Test St' ),
    MailingStreet2=convert( orig_col_datatype, '123 Test St' ),
    PropStreet1=convert( orig_col_datatype, '123 Test St' ),
    PropStreet2=convert( orig_col_datatype, '123 Test St' ),
    >>other columns<<
    into REL039_DW.dbo.Customer_History_new
    from REL039_DW.dbo.Customer_History
    go

    exec sp_rename "Customer_History", "Customer_History_Old"
    exec sp_rename "Customer_History_New", "Customer_History"

    exec sp_recompile "Customer_History_Old"
    exec sp_recompile "Customer_History"

    go

    -- if all ok

    drop table Customer_History_Old
    go

    This will avoid any row moving, space reallocation, etc. You'd have to create any indexes, constraints, etc. on the new Customer_History, and has the advantage of leaving the source table usable for other selects (although not for updates). You would need to have sufficient free disk/database space

    Cheers
    Twan
  5. satya Moderator

    Also collect PERFMON counters and PROFILER trace to assess the information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page