0

For adding values to a new column, I have the following options:

  1. Using default to add value during ALTER TABLE command.
  2. Doing ALTER TABLE without default, then fetching all the records and iterating over all of them to save them with the new value.

I am interested to know how much performance difference should I expect?

I am adding a new column to a table with 14+ million records.

The column needs a default, unfortunately, I'll need to change a lot of code to allow putting default value. However, it's easier to iterate over all the records and save them with the value for the new column.

Before making my decision, I am looking for metrics of performance differences between the two options.

I am working on an application to be used in multiple machines, different environments and DBMS. I wouldn't be installing the application, it's the client's job. The application has scripts to aid them with the same. I understand that without system specifications it's hard to make predictions.

Is making broad predictions possible? As in, accessing all records requires O(n) time, similarly information on time requirements of using default during ALTER TABLE and other processes will also help.

Improve this question
3
  • What are the performance metrics of your storage, RAM, CPU? Will you be performing the action locally or remotely? Will there be a client involved? Currently your question is very broad.
    – John K. N.
    Sep 25, 2018 at 19:36
  • What do your benchmarks show?
    – mustaccio
    Sep 26, 2018 at 13:33
  • Which particular DBMSes are you interested in? The major ones have optimizations for particular cases such as these. For recent versions of Postgres and Oracle at least, use the default option.
    – Colin 't Hart
    Sep 26, 2018 at 19:01

1 Answer 1

Reset to default
0

The primary difference you need to be concerned about is whether or not a table lock is acceptable for an extended period of time. One approach acquires a lock against the table and one acquires locks on rows (if done correctly).

You might also consider, if possible, creating a view against the original table that also includes the new column (and value). Export the view data using BCP. Then, drop and recreate the table with the new column and use BCP to import. You could probably take advantage of being minimally logged.

Many popular databases offer a free version for non-production use. If you want to understand the performance difference your best bet is to run tests on the most common platforms that you expect your clients to use.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.