Updating table takes very long time

Question

I have a table in SQL Server 2008 (SP2) containing 30 million rows, table size 150GB, there are a couple of int columns and two nvarchar(max) columns: one containing text (from 1-30000 characters) and one containing xml (up to 100000 characters).

Table doesn't have any primary keys or indexes (its is a staging table). So I am running a query:

UPDATE [dbo].[stage_table] 
SET [column2] = SUBSTRING([column1], 1, CHARINDEX('.', [column1])-1);

the query is running for 3 hours (and it is still not completed), which I think is too long. Is It? I can see that there is constant read rate of 5MB/s and write rate of 10MB/s to .mdf file.

How can I find out why the query is running so long? The "server" is i7, 24GB of ram, SATA disks on RAID 10.

Updated:

table contains one int column, two nvarchar(20) columns and two nvarchar(max) columns. Column1 and Columns2 in the update clause above are nvarchar(20) columns. The "big" columns are not updated.

Many thanks!

Answer 1

Honestly, that's a huge amount of work that you're doing (text searching and replacing on 150 gigabytes). If the staged data originated outside the database you might consider doing the text operations there, without any of the database overhead.