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


Slow performance with ALTER COLUMN


Slow performance with ALTER COLUMN

Author
Message
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4853 Visits: 3232
I am working for a company that has a 3rd party vendor application.

The Table that having an ALTER Column run against it is a 300 column heap table (import only).

The vendors application issues a number of ALTER TABLE ALTER COLUMN (sometimes 50-60 separate columns) for each import, The change is the data type length eg Decimal (18,2) to (30,10).

These seem to be taking a long time to execute especially when the table has around 4GB of data in it.

Is it possible that the problem is being caused because the data requires extra pages so the engine is spooling the existing table into the TempDB, and back again once the space has been allocated or is it a meta data only operation?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50189 Visits: 21151
Jason-299789 (5/29/2014)
I am working for a company that has a 3rd party vendor application.

The Table that having an ALTER Column run against it is a 300 column heap table (import only).

The vendors application issues a number of ALTER TABLE ALTER COLUMN (sometimes 50-60 separate columns) for each import, The change is the data type length eg Decimal (18,2) to (30,10).

These seem to be taking a long time to execute especially when the table has around 4GB of data in it.

Is it possible that the problem is being caused because the data requires extra pages so the engine is spooling the existing table into the TempDB, and back again once the space has been allocated or is it a meta data only operation?


Not really answering your question, but surely altering the table with data in it is liable to failure, if any columns ever shrink?

If this is an 'import table', I would have expected it to be empty immediately preceding each import ...

But I guess the vendor has reasons.

If the columns are only ever getting bigger, why not make them all huge right now and do away with the ALTER issue?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4853 Visits: 3232
Thanks for the reply Phil. I would agree about setting them to a max size and leaving it, but we have no control over the database. It seems the 3rd Party application had a number of templates for import from Excel/CSV and these vary depending on the supplier of the original template, thus the changes to columns.

I personally wouldn't develop that or after load process the data, then truncate the table and start on the next import format.

From what I've read the DECIMAL data type is a Fixed length, which makes sense, so there is likely to be a lot of disk IO when making a change to a column to increase as data after the column has to be shuffled, while on the decrease side, a scan is performed to ensure that the data will fit into the new scale and precision.

It doesn't help that 40+ columns can change and they are each being run as an ATLER TABLE ALTER COLUMN.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50189 Visits: 21151
Jason-299789 (5/29/2014)
Thanks for the reply Phil. I would agree about setting them to a max size and leaving it, but we have no control over the database. It seems the 3rd Party application had a number of templates for import from Excel/CSV and these vary depending on the supplier of the original template, thus the changes to columns.

I personally wouldn't develop that or after load process the data, then truncate the table and start on the next import format.

From what I've read the DECIMAL data type is a Fixed length, which makes sense, so there is likely to be a lot of disk IO when making a change to a column to increase as data after the column has to be shuffled, while on the decrease side, a scan is performed to ensure that the data will fit into the new scale and precision.

It doesn't help that 40+ columns can change and they are each being run as an ATLER TABLE ALTER COLUMN.



It seems that you have no control whatsoever over the schema of this beast. So maybe instead you can do something about the data which is in it at the time that the ALTERs get executed?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4853 Visits: 3232
Thanks Phil, I think that might have to be the solution, I'll have chat with the manager to check on the process that they follow when importing data and see if it can be amended to make it more efficient.

ultimately the import application should truncate the table for each load then run the scripts, so that's another avenue to explore.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
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