SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1...


Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1 columns in a 1000 column wide table ?

Author
Message
koustav_1982
koustav_1982
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 215
I have a table with 4 columns and 10 million rows in which I want to update 1 column
I have another table with 100 columns and 10 million rows in which again, I want to update 1 column.

Which one among these 2 would perform better.
Chris Harshman
Chris Harshman
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26926 Visits: 6245
How many pages are in each table?
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115987 Visits: 21708
koustav_1982 - Thursday, December 14, 2017 12:53 PM
I have a table with 4 columns and 10 million rows in which I want to update 1 column
I have another table with 100 columns and 10 million rows in which again, I want to update 1 column.

Which one among these 2 would perform better.


Are you updating the 10 million rows?
If the update is only to a few rows, the performance shouldn't be much different, but I suggest that you still test.
One thing that might affect and it's more probable in a wider table is the presence of multiple indexes that contain the updated column.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
drew.allen
drew.allen
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45077 Visits: 14686
koustav_1982 - Thursday, December 14, 2017 12:53 PM
I have a table with 4 columns and 10 million rows in which I want to update 1 column
I have another table with 100 columns and 10 million rows in which again, I want to update 1 column.

Which one among these 2 would perform better.

The question I have is why does it matter? Presumably you're going to update both columns regardless, so does it really matter which one will perform better?

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)

Group: General Forum Members
Points: 272873 Visits: 33814
Pure speculation and more than a couple of assumptions... The chance for a page split is higher when updating the wider table, so over time, accumulated, the update cost is going to be higher for the wider table than for the narrow one.

However, 100 column table? Are we talking fact table or a "normalized" system? If the latter, U R DOIN IT RONG.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57997 Visits: 8500
Impossible to say. It depends primarily on (1) how much free space is available in each page already, (2) as noted already, how many affected indexes there are on each table.

If the first table had no free space per page, and the second table had a lot, the first would likely be more overhead.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)

Group: General Forum Members
Points: 616610 Visits: 45187
I believe the answer will be that it takes less time and fewer resources to update X rows in a narrow table than it does to update the same X number of rows in a wide table because the pages being updated do have to be read and a page is the smallest amount of data that can actually be read. So, the more narrow the table, the more rows you can fit on a page, the fewer the number of pages that will need to be read.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115987 Visits: 21708
There's too much speculation and little details with no further replies from the OP.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)SSC Guru (616K reputation)

Group: General Forum Members
Points: 616610 Visits: 45187
Luis Cazares - Saturday, December 16, 2017 12:23 PM
There's too much speculation and little details with no further replies from the OP.


I don't know about that, ol' friend. If you stick only with the basics mentioned in the original post, there's no speculation required.

I DO agree that the OP appears to have abandoned this post, though.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115987 Visits: 21708
Jeff Moden - Saturday, December 16, 2017 12:35 PM
Luis Cazares - Saturday, December 16, 2017 12:23 PM
There's too much speculation and little details with no further replies from the OP.


I don't know about that, ol' friend. If you stick only with the basics mentioned in the original post, there's no speculation required.

I DO agree that the OP appears to have abandoned this post, though.

I would still like to know how many rows would be updated, if there are indexes using that column, if it's a varying length type, etc.
I agree with the replies, but there's nothing conclusive. Also, I don't have the intention to create a 100 column table to test. At least, not for someone else.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search