@Steve @Marc_Dillon @Bahbus
Ok so I did some performance testing on my use case to compare using a utility table versus using MAXROW(). The timings were all very consistent. This test was adding an Order that copies 10 template rows into the Order Detail records. The timings were all in the neighborhood of 1:15 from the time the Order row appeared until the final child row was inserted. This was in a non-deployed app.
In between MAXROW() trial runs I added 1000 Order rows (later a second 1000 Order rows). These did not have any impact so this usage of MAXROW() using _rownumber appears to be taking advantage of indexing. But this may not be the case for all instances of MAXROW() against developer issued columns.
There was one big issue with the MAXROW() usage. In one of my trials, MAXROW() picked up the Order ID from the previous order instead of the current order. Since I was watching the row insertions, what I noticed is that there was a delay between when the sheet row was inserted and the row data appearing. I can only assume that delay meant the new Order row wasn’t available at the time MAXROW() ran.
Even more unexpected, each copied child row STILL received that previous Order ID even though the current Order row was indeed inserted by the time the 2nd child was copied. Could the MAXROW() result have been cached?
On the other hand, in the “utility” table approach I am adding the current Order ID as a new row and then deleting it after the children are copied. Though this seems closer to a concrete approach, it can still suffer problems if the row add fails. I did run a test by adding 6 different Orders back-to-back such that the last one was entered before the first completed. I had no issues but this doesn’t mean there won’t be at some point.
I see benefits of both approaches. MAXROW(0 is easy and just as performant. But since the “utility” table method seems closer to a more ideal solution and I feel I have more control over potential issues, I will be implementing that approach instead.
Thanks to you all for your input!!