@Ali Nasser thank you so much for this thoughtful response!
- I did see the new LIMIT and OFFSET SQL functionality, but we aren't yet on 2025 but we are soon to be there.
- Thanks for the reference to swizzling - this is indeed I think what tends to slow things down.
- I did not know you could compute properties like that! I will have to experiment with this. I can see it working for many properties. It does become a bit cumbersome in the class definition as they get larger and larger. I think we could use a mix of the triggers and the query runtime calcs, because some things static but others might changed based on other data.
- Ok I admit I thought twice when I said you can't call methods from the SQL queries and I do recall seeing SQLProc keyword documentation and I think I need to dig into this more along with the calculated data.
- My ancedetoal though on base query plus ObjectScript was that the base query would run REALLY quick to just return a base set of ObjectIds that is the key for all the ObjectScript processing. I just did try this approach and you're right, it does take a lot more processing.
- Since we are replicating the DataTables client side behavior, yes, we are trying to search the whole row for "Sally" in that example. Right now I'm hacking this with a concatenated string in my ObjectScript processing and a search text, but it sounds like full-text search is a specialty and more delicate kind of processing that I shouldn't try to hack like I am.
We use a LOT of embedded SQL in our application and I have had to move to dynamic to make this work, however since dynamic queries aren't cached, I guess I thought they were less performant. But compared to a base embedded query with additional ObjectScript processing, I think you have given me some ideas to go back and make this work with Dynamic SQL without any additional COS processing.
Thank you for your help here! How wonderful!
I'm not sure this is the answer you're looking for, but I would loop through the result set and store the data in a local array and then loop through the array and output the data with the calculated column set as you wish. The array would allow you to loop through each ID to find the latest to mark as new. This is an ObjectScript approach along with SQL. I wonder if you could just re-save the data back to the table once calculated so you can still project it as needed to other systems.
@Ali Nasser digging into this more this morning I am trying to determine the best use cases be between SQLProcs and Calculated data.
There are some obvious differences like do you want something to be calculated when saved or updated.
But in general since a lot of my data exists, it's going to be calculating on the fly (I suppose I could write a script to save/update all the existing records for force a calculated field). I start to wonder though, are calculated fields and SQLProcs savinging me query performance or am I just offloading my post processing into the query itself? Does that make sense?
The reality is that our swizzled references will knock us down a few pegs no matter how I configure it but I do hope wrapping it all in the query can be more efficient. I have a feeling we'll see some gains from the filtering and sorting end since I'll be able to simply select this, that, this and that and then the filters and sorts are working with that table data already calculated.