Subscribe to this thread
Home - General / All posts - Active Column Calculation
jshannon
19 post(s)
#23-Aug-14 01:36

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

Sloots

678 post(s)
online
#23-Aug-14 16:28

I would go for an update query such as:

update [tablename] set [result] = sqrt([area]/[parcels])) * ([parcels]+2))*1.3

Cheers,

Chris


http://www.mppng.nl/manifold/pointlabeler

tjhb
10,094 post(s)
#23-Aug-14 23:07

(There are two extra right brackets.))

jshannon
19 post(s)
#23-Aug-14 23:27

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

tjhb
10,094 post(s)
#23-Aug-14 23:57

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.

jshannon
19 post(s)
#24-Aug-14 06:17

Excellent, thanks for the guidance!


Regards, Jason T. Shannon

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.