0

I have a big table with 100 000 000 rows. It contains a tree with one root. The tree implemented as 2 fields:

id int
parent_id int

No additionals data about this tree.

I want to add new column to the table

hier hierarchyid

and fill it with hierarchy values.

I created a script but it works ~ 15 hours. It is not ok. It should be executed in <3 hours. How to do it? Thnx.

ZedZip
  • 5,794
  • 15
  • 66
  • 119

1 Answers1

0

I've run into this before. Adding a column to a table means effectively means redefining it's storage on the file system, so if the table is huge and full, that redefinition means a zillion little table reads and writes as SQL struggles to move the data from the old table definition to the new one - and thus it takes forever.

I think the fastest method would be to create a new table with the added column, then, after exporting the data in chunks from the old table, BCP the data (not insert) into your new table. The advantage of the BCP is that it's super fast.

Brian
  • 3,653
  • 1
  • 22
  • 33
  • ok, thnx. But in any case this new column should be filled with values. It can be done layer by layer starting from the Root. If I use UPDATE t is very long. I.e. for update one level of tree 2 000 000 rows get 20 minutes. – ZedZip Jul 12 '13 at 11:15
  • I understand ... no easy answers for one. From my experience, it usually takes a few steps to get a big job like this done. You might look into the SSIS stuff in SQL to see if you can do the insert and fill that fill all at the same time. – Brian Jul 12 '13 at 11:24