Joining to large tables to perfrom update

I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:

> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.