Updating Datawarehouse history table - Help reqd.

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

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

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