My application involves a multi-step algorithm that requires numerous "parent to children" lookups (states to counties, for example).
My question: is it possible to create a computed column, view, or UDF that will automatically yield the parent (or child) values for a given record without having to perform a costly "join, update and write to disc" operation - and that yields *significant* performance benefit?
Conceptually I need to do this:
UPDATE A SET A.ThisRecord = SUM(B.ThisRecord_Kids) FROM ThisTable AS A JOIN ThisTable AS B ON A.ThisRowID = B.ThisParentID
Obviously, this can be achieved by carrying the records in a table, and stepping through each step of the algorithm and writing the results to disc before advancing to the next step... what I'm curious to do is to compute "ThisRecord" on-the-fly and pass it onto the next step in the algorithm without writing to disc.
I'm doing about 1.5 million records at a time.
Any ideas? Alternate approaches?