Large table Update problems

[lbe]

I'm on SQL Server 2000 and I'm about to update two columns on a large (tall and wide) table in my DW. The tables look like this.

DW_INVOICE(TRANS_ID,INVOICE_DATE,AMOUNT,etc)
100 milj rows, 15 GB data
DW_INVOICE_CARD(TRANS_ID,DISCOUNT_PERC,DISCOUNT_AMOUNT,etc)
97 milj rows, 23 GB data

The relationship is 1 to 1 on TRANS_ID. I need to update 28 milj rows of DW_INVOICE_CARD with the information from DW_INVOICE and some other tiny tables.

I've tried to update a small (7 milj rows) subset of the tables but ran into all kinds of IO Latch-wait-disc and what-not-problems. (Not sure what happened there).

I need help on some other techniques to perform this update, or any large update. The design of the tables and the hardware setup are both rather crappy but I still need to do the update. Oh, did I mention that they're not partitioned either?

I know that "SELECT * INTO NewTable .." doesn't affect the Transaction Log, for instance. Can I create many small tables that are subsets of the large one and then with some excellent technique create a new version of the large one? E.g.:
SELECT *
INTO New_DW_INVOICE_CARD
FROM
(Select a,b,c,.. from subset1)
union all
(Select a,b,c,.. from subset2)
union all
...

I'd be very greatful for any expert help

 

[Hans63]

Hi Ibe,

Although I am not familiar with SQL-server. I can tell you that updating something like 25% of the records in a really large table seems to call for some CTAS (create table as select). Create a new tabel with all records and use a bulk copy (or bulk load) routine, together with a trunc command, to replace the big table. At least that is probably how I would do it in Oracle.
This way you don't need the rollback mechanism. I am not sure if you can get rid of the main portion of latches in this way.

Another way would be to alter some features of this big table, like partitioning, and update only the relevant partition. But you will still have the need for a big rollback-segment (redo-space or how it is named in SQL-server).

It seems like a challenge anyway.

 

[johnherman]

The CTAS solution is definitely the right one for Oracle. Unfortunately, I have not worked with SQL Server since version 2000 for quite awhile, and cannot tell you whether that would be the best solution. If would certainly be worth a try. BTW, Microsoft is dropping support for SS2000 in 2008, so you need to consider upgrading sometime next year.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright