Efficient loading of joined data (version 2)

>> the performance benefit … is questionable
Well, allow me to question then :)
The main time that we see an ENORMOUS performance hit is in any type of WAN environment. Where a single query might load in 1 second, when such a "callback" occurs, depending on how many objects we're trying to load (say 50-100K for example), this may cause literally hundreds (or even thousands) of SLOW (because of the wan) executions, making a 1 second query now regularly take 30-45 seconds, or even minutes in some cases. With a ~0MS latency (as you'd expect in a modern LAN), executing 1000 queries can go VERY quickly (ignoring the SQL Server impact of course) - but if there's a 25, 50 or 100ms latency per call, that adds up VERY quickly.
Solving this problem does not have "questionable", but very _real_ benefits in a variety of situations and network environments.
These benefits get particularly pronounced when we're on a shared SQL server and our customer's DBAs are looking at our SQL calls and saying WTF???
>> will lead to a too complex query
Again - while this may be true in some situations, there are also a variety of situations where it is NOT true - and XPO takes an ENORMOUS performance hit by it's inefficiency. Specifically, take a scenario where there is just one base class and one sub-class, with inheritance mapped to the parent table. If the child class adds just one column, what could be:
SELECT A.Id, A.Field1, A.Field2, A.ObjectType, A.FieldFromSubClass
FROM A
Turns instead into:
SELECT A.Id, A.Field1, A.Field2, A.ObjectType
FROM A;
And then 10,000 calls with:
SELECT A.FieldFromSubClass
FROM A
WHERE A.Id IN (1, 2, … 100)
and then (101…200) etc.
I'm sorry - but this is NOT an efficient way to load this data. It essentially makes subclasses (arguably the most powerful feature in XPO vs. other ORMS available) an incredibly inefficient method for storing/retrieving data.
Again, I feel like this is a _real_ problem which, even though I've been working with XPO for over 5 years now, I did not realize was behaving in this way until literally weeks ago. It took extensive analysis of SQL Profiler data, and 3 separate tickets to your support department before I was able to understand what was happening. Each ticket lead me one step closer to what was happening, and why.
Thanks. I can't imagine that I'm the only one experiencing this - and I'd love to see a commitment to getting it resolved.
BTW - Another solution (in most/or at least many of our cases) would be efficient loading of persistent aliases. In this way, I could make the sub-class property delayed, but load the specific data _about_ that sub-classed object with nested Persistent aliases. That would be even better (from a performance perspective) in many cases. That being said, at this point, persistent aliases just exacerbate this problem.
Does that make sense?
Thanks - and I look forward to hearing from you. Take care.

EJ Alexandra I never realized that XPO wasn't able to load subclassed referenced properties in one go! I'll give it a try using my hand-written XpoSqlProfiler (I can't stand using the confusing XpoProfiler) to see if it indeed behaves like that in all cases. If it is true, then it is a showstopper for XPO as it will create a SELECT N+1 case for every referenced property that has child classes, actually making it unusable in the real world. We would have to constraint our designs to NEVER employ this kind of relationship, which I dare say is not a very good option. We should also bother DevExpress A LOT AND INCESSANTLY so that they hear us, because they have a tendency to work on the things they want, not on what their customers need (unless they make some noise). Also, DevExpress, mainly the XPO team, has enormous experience writing components for programmers, but they don't grasp real-world applications very much, and they sometimes fail to see problems that are showstoppers for real-world applications like this one. I'll get back with some testing results soon!

Thanks Felipe. Sounds like we're on the same page. I'm very interested to hear what you find.

Hi EJ.
Thank you for your feedback. It is sad to hear of your performance issues with our components, but we cannot suggest a solution based on the information you provided. Would you please provide a small sample project demonstrating the performance issue you are experiencing? We will do our best to find a solution. Possibly, the delayed loading of reference-type properties will play better in your situation.

Sorry, I'm confused. I did leave a project that demonstrates the problem???

The project you provided at the beginning of this thread demonstrates how XPO loads objects and I confirmed that this behavior is by design. This project does not demonstrate any real performance issue. If you are experiencing a performance issue in a real application, provide us with a sample demonstrating the issue, so we can find a solution for it.

If you look at SQL profiler, even adding just a few records clearly demonstrates the inefficiency as far as I can tell. Just multiply the results by 100,000 (the number of records we're frequently dealing with) to see the SCALE of the problem.
If you would like me to actually submit a new copy of the project, with a for loop from 1 to 100K around the main code, so that the problem is more easily understood, that's simple enough. All that would do though (imho) is to actually demonstrate that running hundreds of queries (instead of 1) is going to be less efficient, no?
Let me know if you actually need me to send an updated version of project - and I will be happy to do so, otherwise, the project you have seems to pretty clearly demonstrate the problem.

The issue you described looks imaginary. It is unlikely the fact that the entire collection of 100K objects with relatives needs to be loaded at once in a real application. Even if it is so, you can optimize the loading by using the Session.PreFetch method. If this collection is meant to be shown in a grid control, you can use a server mode data source, which loads data in small parts. To provide a solution for a performance issue you have in your real application we need a sample that demonstrates this real situation, rather than an artificial example.

>> The issue you described looks imaginary. It is unlikely the fact that the entire collection of 100K objects with relatives needs to be loaded at once in a real application.
Are you serious?
This would be fantastically more appropriate as a response if your tools provided paging. You're TOTALLY right. There should NEVER be a need to load 100K of anything in a "real" application.
The problem is that we are tied to DevExpress as a solution for both our data layer (XPO) and presentation (XtraGrid). Unless I'm missing a couple of MASSIVE pieces of functionality, the XtraGrid does NOT nativly support paging. If it did (again) your "imaginary" comment would be a LOT more appropriate. If it did, I could load 25 of the 100K, and there would be no problem. Since the grid (as far as I know) does NOT support paging - how exactly do you propose that we DON'T load all 100K items?
This is the ROOT problem that we would love to solve - so if that's an imaginary problem, please tell me the solution.

All DevExpress grid controls, including XtraGrid, support Server Mode. This is more than a simple paging because the "page" size is adaptive and this feature also includes grouping and summary calculation on the server side. The article link I provided in my previous message (Server Mode Concepts) describes this feature in detail. An XPO-based server mode data source is represented by the XPServerCollectionSource and XPInstantFeedbackSource classes. You can find examples of binding the grid to a server mode data source in the documentation and in our demos ("c:\Users\Public\Documents\DXperience 13.1 Demos\WinForms\CS\GridMainDemo\ServerMode"). Please let me know if you need any additional assistance with this solution.

I will look at the XPServerCollectionSource and see if that will be a solution to our ongoing performance issues (at our larger customer sites).
Do ServerCollections handle/calculate non-persistent data?