Hi Michael,

I'm not an expert nor a programmer so take the below with your most skeptical grains of salt:

When it comes to SQL pagination of results, do you know about the new SQL server-side pagination in 2025.1? This can help you limit what you are sending to the client. If you are on older versions then you can use the TOP clause to control the number of returned results

"I would admit our tables aren't best optimized for SQL in the sense that we don't utilize parent-child relationship in tables rather we point to other tables as property object references and often index on those pointers. I don't think this is uncommon, yeah?  I think it becomes an issue when we are accessing a property by the pointer pointing to another pointer and so on until we make the object reference we want."

I can't speak to how common this is but what you are describing here is impacted by swizzling. It can become an issue by taking up a lot of memory as you load deeply nested objects. How big of an issue depends on the size of your data, how long are objects kept in memory, how capable is your system etc.

Two more issues, you need to watch out for, are concurrency and privilege checking, but these aren't specific to accessing nested objects

"- I assume this next concept is common: depending on the table, some of the data reported to the client is calculated rather than specifically derived from the table or pointers. Or the data itself may be a link so there's an <a>Data Name</a> around it. This means if we need to filter data based on the calculated data we can't rely on just the SQL.  We call a base query and then load the resulting object IDs into an array and then loop through the array and process, transform and filter the data in ObjectScript. "

You can have computed properties so that instead of querying on the field that has "<a>Data</a>", you instead do a SQL query on a computed field that does the processing you need on "<a>Data</a>"

You can also do that processing before your data is saved so that your INSERTS or UPDATES only save the processed data, and you save yourself some compute time when querying your data. If you want something elaborate then you can setup triggers to execute that processing step on UPDATEs or INSERTs

"- Maybe that previous point isn't so common?  Do I need to buckle down and learn how to get a lot fancier in SQL? We have methods that transform data in ObjectScript, but I don't think I can call those within ISC SQL.  But really, at the end of the day, the SQL translates to a bunch of $ORDER-ing through indexes and such to get the data we need, right?  So by calling a base query and then further processing in ObjectScript, I'm just doing what the SQL would have been doing in the guts.  I know I'm oversimplifying, but does that make sense?"

You can call ObjectScript methods from SQL (and vice versa). If the methods you are using are class methods, then they can be exposed as stored procedures by adding the SqlProc keyword to the method signature. You can also create a stored procedure (that uses either SQL or ObjectScript in its code body).

What you are saying makes sense but I don't think is true. If you call a base query then use ObjectScript to further process the results then aren't you looping over the data twice? The first time is when you get it and the second when you are processing it in ObjectScript. I think that by using computed fields and sql procs, then your SQL query will be more efficient than using a base query + objectscript processing.

"A use case for example..."

I'm a little confused by this. Does the user have to specify which field does they want to look for "Sally" in or do they have the freedom to just say "Return all rows with all of their fields if any of their fields have the word Sally in it". If we are talking about the former then you can use Dynamic SQL to construct SQL queries on the fly that only target the columns that need to be filtered. If it is the latter then I think we're getting into the territory of full-text search or even vectors/embeddings and that's out of my league