Updating table takes very long time
- by rrejc
Hi all,
I have a table in MsSQL Server 2008 (SP2) containing 30 millios of 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 containg xml (up to 100000 characters).
Table doesn't have any primary keys or indexes (its is a staging table). So atm 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.
Many thanks!
