Update 500 Million row table

[Rich Ford]

We are trying to update 4 columns on a 500 million row table with data from a Temp table.

I.E.

Update table1 a
SET a.column1 = b.column1
a.column2 = b.column2
a.column3 = b.column3
a.column4 = b.column4
FROM table2 b
where a.column1 = b.column1;

The where clause is joining on a Unique index but the table has 16 indexes in total.

This was running for over 18 hours and had to be killed.

[George Neuner]

On Wed, 9 Mar 2022 16:37:44 -0800 (PST), Rich Ford
<richf...@gmail.com> wrote:

>We are trying to update 4 columns on a 500 million row table with data
>from a Temp table.
>
>I.E.
>
>Update table1 a
>SET a.column1 = b.column1
> a.column2 = b.column2
> a.column3 = b.column3
> a.column4 = b.column4
>FROM table2 b
>where a.column1 = b.column1;

How many rows in the temp table? Does the temp table have an index on
the key column ('column1')?

>The where clause is joining on a Unique index but the table has 16
>indexes in total.

That's a lot to change for each updated row. There isn't much you can
do to improve run time except make sure your statistics are up to date
[statistics will affect the plan].

>This was running for over 18 hours and had to be killed.

What you can do depends on the /actual/ issue: if it's just that the
long running transaction is interfering with other queries, then there
are some things that can be done.

If what you really need is for the update to get done faster ...
indexing the temp table will help, but if that doesn't do it for you,
then you may be in trouble unless you can throw more memory or faster
storage at it.

George

[Andy Wallace]

I'm very late to the party, but I wonder if the problem is that you're updating an indexed column (column1). You don't need to change the contents of a.column1.
Andy

[George Neuner]

On Wed, 4 May 2022 15:25:51 -0700 (PDT), Andy Wallace
<arwal...@gmail.com> wrote:

>I'm very late to the party, but I wonder if the problem is that you're
>updating an indexed column (column1). You don't need to change the
>contents of a.column1.
>Andy

The OP didn't mention indexes at all, saying only that the update was
(pre)staged in a temp table and that it was taking a very long time.

Updating the index for typical fixed length key: e.g., long integer,
UUID, even char(n) for small 'n', etc. - shouldn't add /that/ much to
the running time.

I'm now wondering if the OP has [or had, since this is 2 months old
now] indexes on many/all the columns in the permanent table.

George