Upcoming XPO/XtraGrid feature: server-side grouping, sorting, filtering, ...

02 February 2007

We have this really fantastic feature coming up in release 7.1 of DXperience, that I want to tell you about. I had a rather hard time coming up with a title for this post and it’s still rather long (the post as well as the title) – I think it’s not too hard to understand what the feature is about, but it’s hard to summarize in so few words that they fit a blog post title easily.

One problem that people have in .NET (and actually in most other general purpose programming environments that I’m aware of) has to do with the perfect way of handling data that is stored on a server, usually in relational databases. The problem is that there is no such perfect way, and all the technologies that try to tackle the issue are usually really good for one part of the problem and really bad for another.

Eh?

Two of our products can be used to demonstrate the issue: XPO and the XtraGrid. Both have to do, in the widest sense, with the handling of large amounts of data. XPO deals with the questions of storage – where is that data located, how can I get it from its storage location into my application, how do I handle it once it’s there, and so on. The XtraGrid is all about interactive work with data: view it, arrange it, sort, filter and group it. So what’s the problem? It is that the two products don’t work together the way they could. XPO has great capabilities for filtering data, using criteria that are translated into SQL code. The XtraGrid does filtering, too, but at the same time it doesn’t make assumptions about the source of data that it’s working with, and so it implements its own filtering functionality. The diversity with which we are confronted in today’s programming world is the source of the problem here, and the shortcomings are most visible when the amount of data you’re working with is large.

Let me make it clear that this is not a problem that’s particular to our products. If you’re going for standard Microsoft technology, you have the same problem – ADO.NET allows you to construct complex filtering queries, and the DataGridView has a certain degree of interactive functionality, but the two don’t work together any more than our products do. I’m sure I’m missing something, but I’ve only once before seen a product that could facilitate the kind of technical interaction of layers that I’m talking about here (hi Jason!).

Moving along…

Now we’ve set the stage, what is the feature? Rather simple to explain, suddenly: it’s a technology that allows our grid to make use of the server-side functionality that XPO (or any other data layer) exposes. Interactive sorting of data in the grid is automatically translated into an “order by” that is executed on the server. Same for grouping and filtering, and the XtraGrid never retrieves more information than it actually needs.

At the center of our implementation is a new interface, IListServer, that the XtraGrid can use if configured to do so. XPO 7.1 comes with a component called XPServerCollectionSource, which implements this interface. Hooking the whole thing up is easy: drop an XPServerCollectionSource on your form and configure it, very similar to an XPCollection. Bind the XtraGrid and set the GridControl.ServerMode property to true – done. Easy, isn’t it? From now on the XtraGrid will call the methods of IListServer each time one of the critical operations must be performed on the data, and the XPServerCollectionSource uses the XPO infrastructure to have the operation executed server-side.

Of course it is possible to implement IListServer yourself, and we will possibly provide other such implementations in the future. So the feature is not inherently an XPO only thing – we had to start somewhere. Go ahead and have a look once 7.1 becomes available – your own implementation could be the first.

Great, I'm going to use that all the time...

Well, you probably shouldn’t. As nice as it is, it comes with a few drawbacks that I also want to tell you about. Here’s a quick and possibly incomplete list:

  • Grouping, sorting and filtering work on the server. Haha, I hear you say, you just said that was a good thing. Well, yes it is, but at the same time it can be a problem – there are a number of events in the XtraGrid, that allow you to influence how these functions are performed. But not any longer, when server-side functionality is employed for the purpose. In that case the rule for these functions is: your client can’t do what your server can’t do.
  • Out of the box, it works only with XPO. Personally I like that, but granted, there are people out there who don’t use XPO. That’s why we developed the architecture in such a way that it would be possible to provide a solution to these people as well – the solution itself is not available yet.
  • The implementation of the XPServerCollectionSource doesn’t currently support write access. This is in our plans for future development, so it’s not intended final behaviour. Nevertheless an important thing to know for the time being.

Summary

A great and very interesting feature, that’s what I think. It has the potential to greatly enhance performance in those cases where large amounts of data need to be handled in an interactive control. It’s AJAX for Windows Forms, is what one of our developers said. It’s a virtual grid mode based on a single interface, if you like that metaphor better. In any case I think it’s very powerful technology and we probably haven’t realized yet what potential this abstraction has. Be sure to try it out and let us know what you think.

I’m going to take a short break next week – please do leave comments, but be aware that I’ll reply only when I get back.

18 comment(s)
Lu. Po.
Only one word: GREAT!
This is the kind of interaction beetween components that I want to see....
3 February, 2007
Steven Rasmussen
This sounds great!!!  Although I'm not how useful it will be for me in the near future until the write access is implemented.  There have been many times where I've wanted to use the filtered view of a grid as a datasource for a report or chart.  I could be mis-understanding but I believe that this will fix that.  Because the filtering of the grid actually takes place in the datasource and not just the view, I should theoretically be able to take the filtered datasource and use it to create reports and charts.  Please correct me if I am mistaken.
3 February, 2007
Lu. Po.
Just a clarification. Sometimes I use xtragrid to visualize a large amout of data (xpcollection) and child views.... this is not as fast as desidered.
The words "XtraGrid never retrieves more information than it actually needs" does mean that XtraGrid/XPServerCollectionSource use "paging" to perform the scroll of the grid ?
4 February, 2007
SPPatel
Hello,  I want to put the button in one of the column of the grid. But how can i do i don't know so,Please help me to solve this problem.

Also i want to get the item of a perticular cell when i will click on this perticulat cell. So Also help me to solve this problem.
6 February, 2007
Geoff Davis
SPPatel,

This is a community blog and probably not the best place to ask for support especially on a subject that is not relevant to this post. Can you please forward your questions to support@devexpress.com who will be more than happy to answer your questions.
7 February, 2007
Jascha
Sounds like an excellent scalability feature. Will it be rolled out to the other DevEx components (treelist, pivotgrid and so on)? And will this feature be optionally utilised by XAF?
7 February, 2007
Robert Kozak
Hey Oliver.

This sounds great and exactly what we need and I don't think its limitation will hinder us much since w use XPO and the grid together, and the grid is read only so we don't have to worry about writing back to the database except for some edit forms we use to edit the currently selected record (object).

Three questions:

1. Does it keep in sync if we do modify the an XPOObject in a form from the currently selected row?

2. What are the speed issues if our server is inside our WAN but separted by a T1 or a 20mbit connection? When I do a sort or filter does it go back to the server and do a full refresh?

3. Is there anything in place to help out in our LAN environment where one user (User A) edits a record and another user (User B) has a grid open. Will the grid be able to refresh itself automatically without polling after a change is made? Will User B's grid be able to show the change on the grid?

-- Robert
9 February, 2007
David Shannon
Wow!  Fantastic is too mild a word!  I've been hoping for this for quite a while, but I can't believe you actually did it.

We often work with readonly grids to visualize fairly large datasets, and my users really like the control (and speed) they get from XtraGrid's sorting and filtering.  But they don't like the initial load time.  I spent an hour yesterday explaining why it works that way.  I can't wait to explain that it doesn't work that way anymore.

I just hope that there will be sufficient events and hooks to allow us to specify what to do when the grid first starts up, or what to do when the user removes all filters (which we might want to allow but only if confirmed), or what to do when the user...

Again, this sounds realy great and I can't wait to see it...
15 February, 2007
Dan Collier
What a mind reader!
Fantastic, this is great news.  As a maintenance programmer rewriting Client Code into Stored Procedures for the last 8 years for performance gains, due to tables getting larger and people wanting to keep their data forever.  There has always been an awkward gap between the Client and Databases.   Need more Vertical Developers (Database designer AND Client Coder) to really tackle these issues.
16 February, 2007
Oliver Sturm (DevExpress)
Hi guys,

Thanks for your comments, and my apologies for the delay of my reply. So let's see.

Steven Rasmussen: "I've wanted to use the filtered view of a grid as a datasource for a report or chart" - I haven't actually tried whether that works, but I imagine it could, yes. Apart from that, have you reported your idea as a suggestion to our support center? I wonder if that couldn't be done rather easily using our data controller, regardless of the type of your data source...

Lu. Po.: "does ... XtraGrid/XPServerCollectionSource use "paging" to perform the scroll of the grid" - yes, sure - I'm not sure that "paging" is a generic word that refers to all situations where you fetch only a part of a data source, but if that helps you understand the feature, okay :-)

SPPatel & Geoff: Interesting discussion there <g>, thanks for helping out, Geoff.

Jascha: "Will [this] be rolled out to the other DevEx components" - not sure yet, interesting question. I guess we're kind of testing the waters with the XtraGrid and there's of course a lot still to do, but after that I could imagine other components making use of this, yes.
"Will this feature be optionally utilised by XAF?" - Yes, definitely. We are trying to make it available for the upcoming beta 2 of XAF, unless we stumble upon unexpected problems.

Robert Kozak: "Does it keep in sync if we do modify the an XPOObject in a form from the currently selected row?" - I'm not sure if we have a feature that would do precisely that, but you can always refresh the current view - the grid always fetches only as much data as you can actually see, so I don't see such a refresh being a problem even over slow connections. Can be optimized, yes... we'll see.
"[Speed issues] When I do a sort or filter does it go back to the server and do a full refresh? " - It goes to the server, yes. It refreshes, but as always it fetches only what is needed for the current display, so it depends on your perspective whether you want to call this a "full refresh".
"[Network change notification]" - No, this feature doesn't have anything to do with that. If you want server-based change notifications and your SQL server supports that, there's nothing keeping you from doing it... XPO doesn't do it for you, currently and probably in the nearer future, because it's not a feature commonly supported by the various backends and so it would take us a lot of work to do correctly. On some backends it's rather easy to do, but then you don't really need our help to do it - just explaining how we see the priorities in that regard.

David Shannon: "I just hope that there will be sufficient events and hooks ..." - I don't know, it depends on your definition of "sufficient" :-) I believe we should have the first 7.1 RC version out pretty soon, so you can try for yourself.

Dan Collier: Thanks for your feedback!
21 February, 2007
Konstantin
Hello Oliver,
That is a very nice feature.
Could you pls clarify the next:
You said that: "it fetches only what is needed for the current display"
What you meant?
So all records in dataset(query) will be sent to the client side
or only what is needed for the current display(e.g: Fill the vissible part of Grid)
What you are using in this case? Server side cursors?

Pls explain



21 February, 2007
Oliver Sturm (DevExpress)
Hi Konstantin,

> You said that: "it fetches only what is needed for the current display"
> What you meant?
> So all records in dataset(query) will be sent to the client side
> or only what is needed for the current display(e.g: Fill the vissible part of Grid)

It seems to me you're answering your own question there, don't you think? We fetch exactly the data we need for the display. I don't think we use server-side cursors, as not all our supported backends have them. I could be wrong though, maybe we have some specific optimizations in place that I'm not aware of.
21 February, 2007
Konstantin
Hello Oliver,
Thank you for your quick answer.
I asked my question, becouse it is realy very important what you are using to fetch exactly the data  need for the display.(especially when you are working with large amount of data)
Becouse I don`t know any method how it is possible to do it efficiently.
I will be much appreciated if you describe more deeply methods you are using.
Thanks in advance.
22 February, 2007
Geoff Davis
Hi Oliver,

Well i've bit the bullet and tried it out using the 7.1 Eval and I must say this new feature absolutely rocks! I had in the past display only a subset of data from XPO in my master grid and rely of the user to filter down their selection using various search criteria because of the amount of data available to them; loading it all at once was just too time consuming. Hell I even created my own version of the LookUpGridEdit that was customised in such a way that it only brought back a fixed number of objects triggered via a timer when the user entered some search criteria to lighten the burden.

Indeed write capability is a must. I always ProcessNew on the LookupGridEdit and update the XPCollection with the newly created object that is wrapped up in a UOW that persists back to the database when the user okay's the form or just forgets it if they cancel. I really do hope that it will be available for the 7.1 release ;-). Because of this I can't really checkout the LookUpGridEdit with this feature but i'm sure I would get the same results.

Good stuff though and kudos to the team for an excellent addition to the suite.

Geoff.
24 February, 2007
David Shannon
Hi Oliver,

Were any changes made to the XPO providers to handle the new interface?  I am using a couple of homemade providers with XPO now (in 6.3.3) and I am wondering if they will work with 7.1.1 and XPServerCollectionSource or if new capabilities were introduced that will need to be added.

Thanks...

David Shannon
2 March, 2007
Oliver Sturm (DevExpress)
Hi David
You shouldn't have to make any changes at all for 7.1 - the new features should work with your own custom providers out of the box. Please let us know if you do encounter any problems.
5 March, 2007
Tom Wagner
Is it still true that we can implement the IListSource ourselves?  I couldn't find any documentation that talks about how one goes about doing that.
12 March, 2007
Oliver Sturm (DevExpress)
Hi Tom -

I'm assuming you're talking about IListServer, as mentioned in this post, right? In that case, I think the following knowledge base article could be useful for you: https://www.devexpress.com/kb=A1022

Oliver
14 March, 2007

Please login or register to post comments.