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.
On Wed, 9 Mar 2022 16:37:44 -0800 (PST), Rich FordI'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.
<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 aHow many rows in the temp table? Does the temp table have an index on
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 key column ('column1')?
The where clause is joining on a Unique index but the table has 16That's a lot to change for each updated row. There isn't much you can
indexes in total.
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
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
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 72:47:36 |
Calls: | 6,657 |
Calls today: | 3 |
Files: | 12,203 |
Messages: | 5,332,378 |
Posted today: | 1 |