Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>