update table: PK or unique idx?

Question

update table: PK or unique idx?

sgt500

Default port

Points: 1413
More actions
June 7, 2018 at 4:06 am

#369402

Hi,
There's this table with 300000000 rows and about 40 columns
the pk consists of three fields
massive updates are made on this table, for example:
update <all fields except those of pk> where
pk_field1 = N and
pk_field2 = N and
pk_field3 = N;
in the execution plan I have a clustered index update, which is correct since the clustered index is the table, but how can I improve the performance of this activity?
can the replacement of the pk with a unique indexes improve the situation?

ty

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Without seeing the query and the execution plan, I can do more than offer vague platitudes.

So, here's one. A primary key is a unique index. How can replacing a unique index with a unique index change the situation you're in? Do you mean should you have a non-clustered index? Probably not in this situation. If your direct path to the data (the best choice for the clustered index) is what is being used in the WHERE clauses of your UPDATE statements, and you're updating that data after accessing it, that's the best approach. Changing to a nonclustered index (unique or not) that then has to do secondary lookups to actually locate the data to be updated, is additional reads and processing to do the JOINs necessary to locate the data to be updated. So no, that will not speed things up.

What do the execution plans say the optimizer is doing to resolve your T-SQL?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sgmunson SSC Guru Points: 110640 More actions · Answer 2

sgt500 - Thursday, June 7, 2018 4:06 AM
Hi,
There's this table with 300000000 rows and about 40 columns
the pk consists of three fields
massive updates are made on this table, for example:
update <all fields except those of pk> where
pk_field1 = N and
pk_field2 = N and
pk_field3 = N;
in the execution plan I have a clustered index update, which is correct since the clustered index is the table, but how can I improve the performance of this activity?
can the replacement of the pk with a unique indexes improve the situation?

ty

Perhaps even more specifically, why would an update of a single row be a performance problem? If your "pk" is actually a primary key, then it's unique to one, and ONLY one, row in the table, so what you are describing being a performance problem seems patently absurd on its face. However, I'm also pretty sure that when you say "massive updates", you are glossing over the most important element of solving the problem - that of understanding what you actually have going on in enough detail to offer practical suggestions. As Grant already indicated, we have no detail that makes much sense without context that you just haven't supplied. Please post your UPDATE query as well as a table create statement for the table in question, and provide details on exactly what you mean by "massive updates".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 3

sgmunson - Thursday, June 7, 2018 9:02 AM
Perhaps even more specifically, why would an update of a single row be a performance problem?

FKs and/or bad page splits due to expAnsive updates of variable length columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Jeff Moden - Thursday, June 7, 2018 9:10 AM
sgmunson - Thursday, June 7, 2018 9:02 AM
Perhaps even more specifically, why would an update of a single row be a performance problem?
FKs and/or bad page splits due to expAnsive updates of variable length columns.

Excellent point. I knew from the original post that something was missing - single row updates aren't usually the cause of performance problems unless your famous RBAR is in play, but page splits due to expAnsive updates could definitely cause trouble in a "massive updates" environment. Of course, we still need the OP to "Splain', Lucy!"...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)