Home -
General /
All posts - Active Column Calculation
jshannon19 post(s) |
Hi Everyone. I'd like to ask some advice on the best way to calculate the following formula in a large table. I have a table which contains a column called "Area" and a column called "Parcels". I want to add another column to the table called "Result" which would be the calculated result of the following formula: ----- CUT HERE ----- ((SQRT(x/y))*(y+2))*1.3=z Where x=area (from a column called "Area") and y=number of parcels (from a column called "Parcels") and z=result (to be added to a column called "Result"). ----- CUT HERE ----- The table contains over 1 million records, which is much larger than what I am used to working with. Should I use a standalone script for this, or is it possible to use an Active Column? If I use an Active Column, what would be the best setting for "Compute" given I'd only have to run this on occasion (once per week). I'd really appreciate any help on the script or even general direction since I've been pulling my hair out on this one. Thank you in advance!
Regards, Jason T. Shannon |
|
(There are two extra right brackets.))
|
jshannon19 post(s) |
OK, so I decided to use SQL (as recommended), and this is where I landed: SELECT 1.30*(SQR(Area]/[Parcels])*([Parcels]+2)) AS [Result] FROM [Table] ORDER BY [UniqueID]; That seems to create the result-set with the right result. Next I will try the UPDATE to populate the column correctly - as recommended. Thanks for your help, folks! So SQL works great for this. In what instance may one use Active Column rather than SQL for this kind of thing, and at what cost to performance? What are the benefits?
Regards, Jason T. Shannon |
|
SQL is faster, both because it is closer to the metal (it doesn't need to go via a COM object model) and because its logic addresses objects a set of objects at a time. An active column can be more convenient for some simple things, but SQL usually wins for those who enjoy it.
|
jshannon19 post(s) |
Excellent, thanks for the guidance!
Regards, Jason T. Shannon |