I understand that it optimizes the server response time when associated objects are serialized as xdata.proxy entries, but the client then would have performance issues when accessing these associated objects, because the server would have to lookup the child object DB row again, and then lookup the associated proxied object(s).
Once we already have the full associated object ID field(s) in the child object, couldn't we use some (new feature) mechanism to brute force serialization as an xdata.ref entry without $expanding and without accesing those objects before returning (as I did in topic 21373) or using eager fetching?
It would preserve server performance, and enhance client performance when accesing deeper levels.
It's not that simple. In database the foreign key columns to the associated table (object) might not be the id of the object, for example. So it's impossible to return a xdata.ref reference without loading the object.
If you use FetchEager or $expand, at least the associated objects will be loaded in a single SQL statement.
Ok. I understand the point.
Not even an option to force this optimization when we as developers know which FK corresponds to the foreign table ID and which doesn't?
I thought it wouldn't be so rough. In my case I see most queries have between 5 and 8 joins, with full FK tables IDs available, and it would accelerate a lot. Makes a big difference, f/i a query on child table without joins: 4 secs vs the same with 7 joins, 18 secs.
My guess is that happens because you are explicitly loading each association separately, which indeed fires a new SQL for each of them.
But using FetchEager or $expand or similar approach, it uses a single SQL, have you checked the performance impact in that scenario?
No great deal. From 18 (loading separately) to 15 secs, using FetchEager (it's a query that returns 700+ rows, with 6 associated objects).
4 secs were acceptable, but not 15. While there's no way to generate canonical IDs references without the joins, I'll force the use of paging.
I kindly ask you that you provide us with a project reproducing the issue. Optimization requires profiling, it's never wise to optimize code just by guessing what is making it slow.
It should not be that small difference from bringing everything at once and with many SQL statements. So I have to investigate and profile and see exactly where is the bottleneck. If you send us the code, I will surely take a good look at it.