Updating table takes very long time

Posted by rrejc on Stack Overflow See other posts from Stack Overflow or by rrejc
Published on 2011-01-08T16:31:44Z Indexed on 2011/01/08 16:54 UTC
Read the original article Hit count: 200

Filed under:
|

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2008