Geek Answers Handbook

Updating a table takes a lot of time

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

There are no primary keys or indexes in the table (this is an intermediate table). So I run the query:

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

the request is completed within 3 hours (and it is not yet completed), which, I think, is too long. It? I see a constant read speed of 5 MB / s and a write speed of 10 MB / s to a .mdf file.

How to find out why a request has been running for so long? "Server" - i7, 24 GB of RAM, SATA drives on RAID 10.

Updated:

Table

contains one int column, two nvarchar (20) columns, and two nvarchar (max) columns. Column 1 and Columns2 in the update clause above are nvarchar (20) columns. Large columns are not updated.

Many thanks!

+5
source share
6 answers

Honestly, this is a huge job that you do (text search and replacement for 150 gigabytes). If phased data originated outside the database, you might want to consider doing text operations there without any database overhead.

+3
source

​​ - , SQL, , . SQL CLR, , , SUBSTRING([column1], 1, CHARINDEX('.', [column1])-1).

+1

, - , - . , , , 10 000 , .

10 000 , "" , , " ".

, .

, .

+1

. , , Larry Lustig , . :

  • 2 .
  • , ( , .
  • 2, 10 000 , column2 null. , , , , , .
+1

SQL Server, , . , , .

, Oracle, - , , (, ).

, . , , . ,

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

:

create table stage_table2 as
   select column1
         ,substring(column1, 1, charindex('.', column1)-1) as column2
         ,column3
         ,column4
     from stage_table;

drop table stage_table;

alter table stage_table2 rename to stage_table;
-- re-create indexes and constraints, optionally gather statistics

nologging, , , :) , - Oracle, , SQL Server. -, . , "" CTAS.

, , , . - , :

, 5 / 10 / .mdf.

2- . 5 / 150 , 8,5 . , 0% , .

+1

, - , - . , , , 10 000 , .

10 000 , "" , , " ".

, .

At least this gives you a decent training ground.

0
source

More articles:


All Articles