A computed column (formula) that contains a JOIN?

a1x,

Thanks for the response. What I need to do is to compute the value, and make the freshly computed value available to the next step of the algorithm. I'm currently doing this by UPDATING before stepping forward. What I'm hoping to be able to do is to only SELECT that value (and thereby avoid the write and increase performance).

I need to do this on 1.5 million records simultaneously - not one at a time. Also, I have built a UDF to accomplish this, but it didn't perform faster than the original approach using the UPDATE statement.


Thanks for the help...

-ash

hi ashblack,

you could use temporary tables to hold the data after the first step and pass that table to the next step of the algorithm ..

ike

another idea is to hold the accumulated data in the parents table with a trigger on the child-table, so every update in the child updates the parent-table-sum field for that child as well.. then you have redundant data, but that might speed up your calculations
it depends on how your database is mainly used, if you have a lot of updates, this might slow down the usual work with the database a lot .. and it depends on how often you need to run that algotithm

It depends on what "the next step of the algorithm" is.
It seems to me that you have focused the question on one step of your process but the problem might be that there is a better process.  If you can combine your "steps" into a single SQL statement you might not need the intermediate update at all.
--
JimFive

ikework, JimFive,

Thanks for your input, much appreciated.

ikework, over the weekend I designed an approach using a temporary table to hold the components of the algorithm, but found that in the long run there was no performance gain in that route... I think that the amount of data being processed by the algorithm is so large that it is impossible to avoid the interim write statements (iow, not enough physical memory). Please balk if that doesn't make sense to you!

Also, JimFive, I appreciate what you're saying about designing the algorithm to avoid writes, but in truth, each step of the algorithm has already been optimized in that direction.  Most steps involve the summation of values based on parent-child (row) relationships and then compuations occur based on relationships between those values . Iow, I'm using a big relationship-lookup during each step of the algorithm already...

If you still think I'm missing something, I'm looking forward to hearing about it.  Thanks again!

Ashblack,
Based purely on your example it is certainly possible to create a view:

CREATE VIEW PreCalc AS
SELECT ThisParentID, Sum(ThisRecord_Kids) as ThisRecord FROM B
GROUP BY ThisParentID

And then join this view into your process.

However, I suspect your problem is a bit more difficult than that solution provides for.  If so, could you sum up what data you have to start with and what you want to see at the end of your process.
--
JimFive