bulk load delayed properties

This is a follow up on Add Prefetch Method to bulk load delayed properties
Hi
The problems I am having with delayed loading of columns is worse than I thought: If I understand it correctly, if I have a delayed loaded column that is shown in an XtraGrid, then group by the column, it seems to fire off one SQL statement for every row in the bound source. For us, this take a matter of minutes. The thing is, if we were able to say "populate all the values for this column for this collection of objects in one go", it would take a matter of seconds.
I appreciate that it is the intention to provide a bulk column-load in the future, but in the meantime, is there anything you can suggest (or perhaps tell me when the bulk column load will be developed?) Unfortunately, I cannot make use of the XPServerCollectionSource and having the XtraGrid in server mode.
Many thanks,
Mark.
Hello Mark,
Can you please provide more information on your data infrastructure. What data does your delayed loaded column represent? Can you avoid using delayed loading for this column?
We need this information to make more appropriate decisions while implementing the new functionality.
Thank you, Alex.
-----------------
This is a bit annoying. When I raised this bug https://www.devexpress.com/Support/Center/ViewIssue.aspx?issueid=CQ49964
Stan told me to raise this suggestion… which was accepted TBD. Now you're telling me you've discussed it and it's not clear enough?
Well, it's actually incredibly simple to show the problem (although the solution may require more thought!).
I have an object with a great many properties. There are also a great many rows, in the database, and it takes a very long time to bring all rows and all columns into memory. So I am making use of XPO's delayed loading for properties.
The problem is that if, for example, I wish to bind a huge collection of these objects to an XtraGrid, and then GroupBy a delayed column, Xpo proceeds to load the value for each row individually rather than in bulk. The same goes for iterating through the collection in code, accessing a delayed property.
i.e. for each object, it calls "SELECT DelayedColumn FROM table WHERE pk='oid'".
I would like to be able to tell it to load _all_ the DelayedColumn values for an XPCollection…
i.e. myXPCollection.LoadDelayedColumn("DelayedColumn");
which would translate to
"SELECT DelayedColumn FROM table WHERE pk IN ('oid1', 'oid2',…)"
Also, since this IN list could become huge (and there are some limitations to this size depending on the data, one should be able to specify a max Id list size…
so, if (silly example) it was set to 3, then multiple queries would be fired:
"SELECT DelayedColumn FROM table WHERE pk IN ('oid1', 'oid2', 'oid3')"
"SELECT DelayedColumn FROM table WHERE pk IN ('oid4', 'oid5', 'oid6')"
etc. until all objects have their delayed properties loaded.
I hope this is enough food for thought.
Mark.
-----------------
In addition, it would be nice to be able to specify on the Delayed Attrbute to load all values for a column when it is first accessed:
[Delayed(BulkLoadOnFirstAccess=True)]
This would fire the SQL as previously described. It would do this for all objects of this type currently in the cache.
Another nice parameter would be tell XPO to start loading the delayed property for newly cached objects once the property had been accessed:
[Delayed(AlwaysLoadAfterAccess=True)]
Hello Mark,
We do understand how the bulk loading of delayed columns should work internally. However, to introduce public methods that perform the bulk loading, we need a real-life example. Real-life examples that we've encountered so far were easily worked out either by using the XPView component or server mode. We need information on why you cannot use the XPView component and server mode, what data your delayed column represents and all the related information. This will allow us to make a more appropriate decision that will suit your needs.
Thank you, Alex.
Ok, I can't really send you lots of proper data. I hope it's enough to imagine a table with 30,000 rows and 90 columns. The user wishes to be able to pick and choose which columns to view in an XtraGrid, it may even be all of them.
I have set up my class, have linked it to the uderlying SQLCE database, and have set up an XPOServerCollectionSource to ensure only the viewed data is loaded. All good. The data displays almost instantly, the user can sort, group and filter very quickly! Bravo DevExpress :)
There are some buttons on the screen that launch processing tasks on the same set of objects that the user is looking at… this where I hit problems.
If I do not delay the loading of lots of my lesser used columns, it takes quite a while to load the XPCollection (assuming I need to work on all 30,000 objects). If I do delay columns, it's very much quicker… until the user hits a codepath that requires to: foreach(XPObject obj in XPCollection) { proccesValue(obj.DelayedProperty); } … this then grinds to a halt - it actually takes longer to complete this loop than it took to load _all_ 90 columns without delaying any of them!
So, the answer for now? Well it would seem to me that I have to ensure that for all possible codepaths that process properties, those properties are _not_ delayed. Unfortunately, we have a pretty complex app… this means loading many properties each time for that 1% of the time they will be used.
Well, this was the reasoning behind my request for a bulk load of a delayed property - I could ask XPO nicely to load all values for a delayed property _before_ i needed to process the collection, it would be hugely faster! Even nicer, if XPO could, which the magic of reflection, handle this for me… well the world's my oyster :)
Hello Mark,
As far as I understand your situation, your object has many delayed properties, but just a few of them are needed for processing. If so, please specify a unique group name in the Delayed() attribute for the required properties or remove the Delayed() attribute either. In this case, only the required property will be loaded, while loading of other properties is still delayed and does not impact the performance of your processing code.
If I misunderstood you, please provide me with the implementation of your persistent class and with the code used for processing the XPCollection. We will try to reproduce your situation and will find a solution.
Thank you,
Nick
Why do you have such difficulty understanding this? I have spent a long time explaining a simple situation for which I have provided a clear solution.
I cannot believe the people that have implemented such a powerful O/R system as this fail to understand my situation. If you decide not to implement this because (a) it's too complex (which I doubt), or (b) you can't see the benefit to your customer base as a whole (which I would be suprised at), I shall regretfully look for other solutions.
Incidentially, it would be useless for me to provide the object in question, without providing the rest of the application, and a lengthy tutorial.
You are only partly correct in your understanding. My object does indeed have many delayed properties. MANY of them are needed for processing, but not all at once.
Please try to image: I have an object with 90 properties, 80 of which are delayed. There are are great many _different_ areas of the applications that make use of different subsets of these properties (for the sake of argument, 20 core functions access all of the 80 delayed properties, but each core function may only access 1 or two of them).
Now, perhaps I misunderstand the documentation, but named groups for delayed properties only ensures that all the properties in the same group for ONE OBJECT are loaded when one of them is accessed. This still means that when I process 30,000 objects, I get 30,000 SQL statements which takes a very very long time.
Again, to make it clear, when a user hits an area of the application for which will be processing a set of delayed properties on a LARGE collection of objects, I really do need to have a more efficient way of loading these properties. I have already suggested an implemnentation, that I believe would work, but am happy to discuss other possibilities.
Hi Mark,
>Please try to image: I have an object with 90 properties, 80 of which are delayed. There are are great
>many _different_ areas of the applications that make use of different subsets of these properties (for
>the sake of argument, 20 core functions access all of the 80 delayed properties, but each core function
>may only access 1 or two of them).
Thanks for the additional information. I have realized your situation and now I'm absolutely sure that a dynamically created XPView with the required properties is the perfect solution in your situation. If needed, I'm ready to create a sample project for you that illustrates this approach.
Thank you,
Nick
Ok. Thanks :) I am very glad you've found a solution, and would be most grateful for a sample project. I shall modify our code to use this mechanism, should it prove to be appropriate.
Hi again. Just re-read the XPView section of the Xpo docs.
I have to say, I'm not sure this is the solution: as it says in the docs "XPView is only intended for displaying data" - this is not what I am doing at all.
As said in a previous post, I am using the (very excellent) XpServerCollection combined with an XtraGrid in server mode to view my objects.
It would appear to me that if I use an XPView, the data is not cached and I do not get to use my domain objects.
(sorry if I'm being ignorant here… am happy to be enlightened!)