CODEWITHSUNDEEP
sql-server
Dewitt Harrison
Dewitt Harrison

Reputation: 51

Stored Procedure or Calculated Columns

This is a question of what is the best practice and best performance.

I have inherited a database that contains data for turbine engines. I have found 20 data points that are calculated from several fields from the turbine. The way it was done in the past is a view was create to pull data for some turbines and calculate some of the 20 data point. Then other views for the same turbines but different data point and then other views for different turbines and data point. So the same equations are used over and over.

I want to consolidate all of the equations (20 data point) into one place. My debate is either creating a user function that will do all 20 calculations or creating them as computed columns in the table. With a function it would calculate all 20 for each turbine even thou I might only need 2 or 3 for a view. But as a computed column it would only calculate the columns the view pulled.

Upvotes: 1

Views: 977

Answers (1)

Kritner
Kritner

Reputation: 13765

The answer is probably "it depends".

The factors when making this determination include:

  1. Is the column deterministic? (e.g. can you persist it or not)
  2. How often is data inserted into the table?
  3. How often is data retrieved from the table?

The trade offs for computed and specifically persisted computed columns are similar to that when considering an index on your table. Having persisted columns will increase the amount of time an insert takes on the table, but allows retrieval to happen faster. Whereas on the other end, computed columns (that aren't persisted), or a function you would have faster on the insert but slower on the retrieval.

The end solution would likely depend on the utilization of the table (how often writes and reads occur) - which is something that you would need to determine.

Personally, I wouldn't do a function for the columns, but rather I'd persist them, or write a view/computed columns that accomplished them, depending on the nature of the usage on the table.

Upvotes: 1

Related Questions