Performance on Computed Columns

• sivaramakrishnan G

  SSC Enthusiast

  Points: 190

  More actions

  January 24, 2014 at 5:58 am

  #288810

  Dear All,

  I am thinking to implement Computed columns in my project.The situvation is as follows.

  Actual Amount so far paid for the employee from April month to current Paymentdate-1 month

  Projected Amount is current month amount multiply by no of months upto March month.

  Taxable amount is actualamount+Projectedamount.

  CREATE TABLE dbo.Computed_Columns

  (

  EMPLOYEE_ID NUMERIC(18),

  TAX_COMPONENT_CODE VARCHAR(20),

  ACTUAL_AMOUNTNUMERIC(23,3),

  PROJECTED_AMOUNT NUMERIC(23,3),

  TAXABLE_AMOUNT AS ACTUAL_AMOUNT+PROJECTED_AMOUNT

  );

  I have implemented the above logic its working fine.My Question is what about the performance impact in my situvation i amgoing to test the employee load of 50000.Employee will associate 15 components hence 750000

  approximately.The count will increase depends on the employee count.Is it advisable to use the computed columns on this situvation.

  Kindly Guide me.

  ComponentActualProjected Taxable

  BASIC 125000013750000 15000000

  BONUS 2500000 250000

  CONV 62500687500 750000

  Regards

  Siva

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 24, 2014 at 6:44 am

  #1683118

  nothing beats actually seeing it for yourself.

  here's exactly what i did.

  for computed columns, the cost isn't a lot, but it's there.

  the key is to look at the actual execution plan.

  attached are two different plans form an example i slapped together for this; i put in about 258K of fake data into your table;

  in one version, the table definition is as you described.

  in the second, i added the PERSISTED parameter to the calculated column definition, which made the cost of the computed column disappear from the query;

  IF OBJECT_ID('[dbo].[Computed_Columns]') IS NOT NULL

  DROP TABLE [dbo].[Computed_Columns]

  GO

  CREATE TABLE [dbo].[Computed_Columns] (

  [EMPLOYEE_ID] NUMERIC(18,0) NULL,

  [TAX_COMPONENT_CODE] VARCHAR(20) NULL,

  [ACTUAL_AMOUNT] NUMERIC(23,3) NULL,

  [PROJECTED_AMOUNT] NUMERIC(23,3) NULL,

  [TAXABLE_AMOUNT] AS ([ACTUAL_AMOUNT]+[PROJECTED_AMOUNT]) PERSISTED)

  --some fake data

  INSERT INTO [Computed_Columns]

  SELECT TOP 1000000

  ABS(CHECKSUM(NEWID()))%50000+1 AS [EMPLOYEE_ID],

  CHAR(ABS(CHECKSUM(NEWID()))%26+65)

  + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS [TAX_COMPONENT_CODE],

  CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY) AS [ACTUAL_AMOUNT],

  CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY) AS [PROJECTED_AMOUNT]

  FROM sys.columns t1 cross join sys.columns t2

  --enable actual execution plan and run this:

  select * from [Computed_Columns]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!