Extended datasource APIs with the ASPxGridView

14 May 2008

Recently there have been a number of technical questions raised about how the ASPxGridView works, with large datasets in general, and with LINQ in particular.

Let's go back to basics first, and especially the user experience that our grids afford. I'm not talking here about how to develop with them, what you have to link up and where, just the end-user features and benefits. For a start there's sorting (click on the column header), grouping (drag the column header to the grid's grouping panel and drop), filtering (type in a simple criteria as text). There's also extra information that can be shown: the column and group totals and so on.

Unbeknownst to the user but still crucial are the paging capabilities of the grid: the user doesn't have to wait extended lengths of time for data to be shown in the grid as they manipulate the grid. Of vital importance with this paging is that it still works well when the user is grouping records. By that I mean that if the user groups on City, say, and the grid displays a page of cities, all collapsed, then the paging subsystem only returns those city records, and not all the detail records making up the groups. Expand a city, and the paging subsystem returns the records needed to expand that group and fill the grid.

So, back at the beginning of last year (2007), when we were designing this new grid, we determined pretty quickly that we'd need an intelligent paging subsystem that could help with all the user experience scenarios we envisaged. In particular, we really wanted to make sure that our story with grouping was compelling, since the grids out there just weren't. And since we had much of the functionality in XPO (eXpress Persistent Objects — our ORM solution — already, it made sense to use it, and so the famous IListServer interface was born along with its first implementation.

A lot of customers saw that IListServer was good, but they wanted other implementations to cover other data sources and scenarios. At the beginning of this year (2008), we released our implementation of it that used a LINQ provider, and thereby supporting all manner of data sources.

Now, with LINQ being available, the question arises whether we should even do this intelligent paging at all. The standard LinqDataSource does efficient paging, so why not use that? Cut out the middleman!

As it happens, although it does sound like an attractive idea, it won't work. Let me rephrase that. Not "work", but "work well enough". In the last couple of days, as an experiment, the guys on the team implemented support of the standard LinqDataSource inside ASPxGridView. And it works very well for the basic feature of paging records into the grid. It doesn't work at all well for grouping and filtering, and certainly doesn't work for creating summary or total values.

So what should we do? Should we add an option to switch the grid between this hobbled version and the fully functional version (I'm thinking about it from the end-users viewpoint here)? Or should we just accept that our grid's end-user functionality is of prime importance, even if the developer has to jump through a couple more hoops to get there?

Of course, this is not the final word. We'll continue to investigate this issue to see if there is some other solution we can explore. So, even if the current answer is "business as usual", stay tuned.

18 comment(s)
Andrea Lombardi

Julian,

I'm using ASPXGridView in a project and let me say that I've "wrestled"  with this issue a lot.

I believe that the problem stems from the way we look at the grid. If you consider the ASPXGridView primarly as a tool for reporting scenarios your concerns about groups, summaries and filters "might" be valid but in many cases I use the grid to display records and then "act" on them (CRUD and other "operational" use cases). In those scenarios I don't need, nor want, to use groups and filters but only the ability to sort and page.

So from my point of view ASPXGridView needs a "ligthweight" "modus operandi". This is my primary concern about this, otherwise great, component.

I hope to have been able to explain my points.

Sincerly,

Massimo

15 May, 2008
Robert O'Brien

I agree with Massimo. As a developer, I would rather always start with a basic 'lightweight' grid (menu, treelist, navbar or whatever) and gradually enable features as I need them. I find it frustrating to have to disable multiple features in order to end up with a basic grid. It makes more sense to me to have to enable (or opt in to) the more advanced features in order to use them, rather than turn them off because they are not needed. Think Windows Vista security - completely tied down by default (in theory anyway), and it's up to the administrator to grant access to resources as required.

That goes for grid databinding too - I need paging and sorting far more frequently than I need filtering, summaries, and grouping in particular. If I enable an advanced feature, and I need to do a bit more coding to get it to work then so be it, but please don't ask me to add extra markup or code to make the grid less complicated!

Give me a plain cheese and tomato pizza - if I want extra toppings I'm quite happy to take the time to add them.

Regardless, the ASPxGridView is an outstanding grid control and I wouldn't swap it for any other.

Many thanks,

Ben S

15 May, 2008
Frans Bouma

"Of vital importance with this paging is that it still works well when the user is grouping records. By that I mean that if the user groups on City, say, and the grid displays a page of cities, all collapsed, then the paging subsystem only returns those city records, and not all the detail records making up the groups. Expand a city, and the paging subsystem returns the records needed to expand that group and fill the grid."

I think one major reason it doesn't work properly is that there are different ways to look at how paging and grouping should work together: is paging applied BEFORE the grouping or AFTER the grouping? If paging is applied BEFORE the grouping, the grid groups the data that's visible in the grid and nothing further. If the grouping is applied AFTER grouping, the grouping has to be part of the query. There's no other way around this.

The thing is that if you have 100K rows or perhaps a couple of million rows, and you want to manipulate a couple of rows, using a screen which has your grid on it, paging is VITAL. No paging mechanism means NO performance at all: you definitely don't want to pull 1million rows into the grid. (we have customers who work with databases which get 10million new rows a DAY in some tables, you can't possibly ask these people to put paging on a lower priority than grouping).

So back to grouping and the query at hand. When paging is necessary (read: required, otherwise nothing works, see my example above) and grouping is something which should be done, because the user dragged 'city' to the grouping bar, it's essential that the grouping is part of the query, as the PAGING is applied AT the query. Only in that situation it's possible to obtain a page from the resultset from the db server.

But... grouping how the grids want to do this (in hierarchical sets) CANT be part of the query. The point is that grouping as it is used in a grid, is a hierarchical projection, like Linq grouping is done: for every key (the grouped element, e.g. city) a list of elements is kept. However, SQL produces a flat resultset.

I'm not sure if XPO's linq provider can do this, but if you do:

var q = from c in ctx.Customers

          group c by c.Country into g

          select g;

you get all customers grouped by country. However this is a hierarchical fetch: first the keys (country, using a groupby) then the customers, and they're merged together by the engine (at least, that's the most efficient way). There's NO WAY you can get this in 1 query, unless you make assumptions how a group by is executed on the server.

Now, for your grid, how to solve this? First it has to be made clear what the page numbers mean. If I have 100,000 customers in a table and I group them on City, and there are 1000 different cities,all with on average 100 customers in them, and I show 10 rows per page, is the paging per set of 10 cities? Or is a group header also a row, or is the # of customers on the page shown  always 10 and it could be that there are 3 group headers, or probably 1 or 0 ?

Personally I find that a question which is already impossible to answer in 1 way. You always will run into people who will want to page over the KEYS, not over the ROWS in the keys (for example because they're expanded on demand).

There's a shortcut solution though. :) Sorting. It requires that you define paging with grouping as: if the page size is 10, there are always 10 customers in the grid, regardless of the # of groups they're in. This works, because the groupings in a grid are simple: a set of fields, no expressions.

Say you have these 100,000 customers. You want to group them on Country and City, and sort them on company name. All you now have to do is sort on: Country, City, CompanyName, and then simply PAGE over the set of 100,000 customers.

If you now get page 123, your grid will simply check which country and city are in the page shown and will add the grouping bars. As the data is in the right order, this always works.

-------------

About the LinqDataSource: it's for Linq to Sql only. So despinte effort put into supporting that: it won't bring you support for other datasourcecontrols out there, which ARE supported by all other grids out there.

-------------

About Linq being the solution here: Linq is for fetching only. That's great for readonly data (but then I don't need your grid's features), but there's a missing part of the story when the data has to be updated and saved. How are you planning to do that? The problem isn't new: all datasourcecontrols have already solved this. However, the ASP.NET architecture requires that the data used in the grid (via the datasourcecontrol) is INSIDE the datasourcecontrol as that datasourcecontrol receives the ExecuteUpdate etc. calls.

-------------

About summaries/total values: In my forum post about this same topic, I suggested how to do this: raise an event. The main problem here is: the data set is very large, e.g. more than a couple of hundred rows. As soon as that's known, you can toss out any client-side code you want to add, because it won't work: you can't pull all data to the client to calculate some value, with 100K rows, that's simply unacceptable. (I hope that the reason why that's unacceptable is clear). Summary and total values are scalars: calculated from the total set of data. Typically these can be perfectly calculated in a routine in the BL for example. Also: some architectures REQUIRE that these kind of values are calculated by code which is already there, and not some grid. How is this going to work with the grid? Is it possible to hook a routine to the grid for total/summary calculation? If so, you've solved your problem: IF paging THEN manual summary/total calculations. Of course, if the datasourcecontrol implements an interface you provide, you can use that.

-------------

What I find a bit sad in this whole discussion (which is dragging on for a while now), is that paging is apparently something that is an option. No, Julian, paging is something that is ESSENTIAL for some applications to be able to even work because of the vast amount of data they have to work with. And if something is essential, you can't compromise to ignore it: you HAVE TO deal with it.

I truly hope DevExpress solves this properly. If not, well, it's obvious we can't continue advising to pick DevExpress' grid controls for large applications, as essential parts of the application requirements can't be met.

15 May, 2008
Frans Bouma

"If the grouping is applied AFTER grouping,"

... should have been

if the paging is applied AFTER grouping...

15 May, 2008
Troy

Personally, I am in the boat that Summaries are hardly ever used. Summaries typically are used for reporting mechanisms and is that not what the XtraReports Suite is all about?

It seems that a grid is more about CRUD and the main focus should be that, and perhaps in order to get the Summaries, etc. make the developer do more to get that, not make the developer do more to get the more mainstay every day behavior.

As Frans said, I have some tables with several million rows, and often time I might display a grid, expect to get back the first page, then do some filtering, find the row or rows I need to deal with, edit, update and be done with it. Bring back a few millions rows is nuts when using a standard datasource control. Summaries are not necessary for that, grouping is used in a limited fashion.

I think the feature set of the ASPxGridView is great, just may need to be tweaked to design it to be used in the everyday behavior (CRUD, Paging, etc) by default, and some extra code to add the bells and whistles. By no means am I saying remove any features, just find a way to make it useable on large datasets in a more open way. XPO and Linq are not the ORMs out there.

Not to mention, the current LinqServerModeDataSource being read only means that even with the Linq implementation, the developer is forced to write even more code handling the RowInserting, RowUpdating and RowDeleteing events simply to make CRUD operations work. If a developer has to do that, doesn't the defeat the purpose of a DataSource Control that makes the "look mom, no hands" no coding approach non-existent?

As stated above, I hope this issue is dealt with properly. If you do take the time to work through this issue, then you can truly boast you have a performant grid with large datasets. While today, that may be true for read only operations, and I rarely ever use a grid in read only mode. I use reports for that.

15 May, 2008
Seth Juarez

I too was seriously disappointed with the design decision that was made in the grid. I purchase controls to take the hassle out of developing applications.

The grid, for me, is one of the most powerful tools to manage CRUD operations in an application. When it comes to displaying information ABOUT the data that is not present in the grid, I ALWAYS (well almost) prefer to put that in a report. The grid is a tool to facilitate data entry, reports are a tool to facilitate data understanding.

It is unfortunate that DevExpress does not see that since, hands down, you guys have the BEST, as far as I'm concerned, tools for creating reports. Why duplicate effort and put reporting features in an editing control?

If someone is truly interested in having summaries, etc. let them handle it the way they want through some sort of event or helper method.

As a side note, I recently switched from Telerik because of your tremendous ability to respond to customer needs and feedback. I hope I am not disappointed in this regard.

15 May, 2008
Neal

Do you want to make the min requirements for the component .NET 3.5?  I'm using .NET 3.5 and will use this and later, but I don't know that all of your ASPxGridView users are or will go to .NET 3.5.  So is LINQ going to tie your hands?

15 May, 2008
Mike Falcon (DevExpress)

Interesting comments... Of course we will support "standard" data source paging (which is supported only by ObjectDataSource and LinqDataSource).

In addition to this of course, we already have built-in support for full "server side" data browsing - you and your customers have almost no limits - you can do everything with data without writing a single line of code. You can group, sort and filter. Print or export. You can do all of these things and the best part is that it all works fast...With almost any size of data...Under serious load.

It works for our XPO objects and also it works really well for SQL Server databases (using our LinqServerModeDataSource). And almost any datasource provider can support these features - easily- by implementing our interface or via LINQ support.

15 May, 2008
Frans Bouma

"Of course we will support "standard" data source paging (which is supported only by ObjectDataSource and LinqDataSource)."

No, it's supported by _ALL_ datasource controls, including ours, as it is part of the parameters passed to ExecuteSelect. However your grid doesn't pass these parameters, in ANY way, hence _NO_ paging.

And when will this paging work again? Do you have an estimate when this will be fixed?

"In addition to this of course, we already have built-in support for full "server side" data browsing - you and your customers have almost no limits - you can do everything with data without writing a single line of code. You can group, sort and filter. Print or export. You can do all of these things and the best part is that it all works fast...With almost any size of data...Under serious load."

If you want to sell me your marketing stuff, save yourself the trouble. If I have to group on a set of 100K rows, and there's no paging passed, nowhere will you get any performance whatsoever, as there's no paging parameter passed to the datasourcecontrol.

If you're now claiming these parameters ARE passed, show me.

"It works for our XPO objects and also it works really well for SQL Server databases (using our LinqServerModeDataSource). And almost any datasource provider can support these features - easily- by implementing our interface or via LINQ support."

I'm sorry, but this is your solution to the real problem? That I have to implement your proprietary interface on my runtime library, and therefore have to ship a devexpress dll just because your grid sucks in the paging department?

What if I want to pull some entities with a filter, and some related entities from the db from a set of 10 million rows into the grid, edit them and persist them (which should call ExecuteUpdate/Insert() on the datasourcecontrol) ? Can your linq code do that? No, because it has no clue what to do with updates. That's the reason why there are datasourcecontrols.

15 May, 2008
Julian Bucknall (DevExpress)

All

As Mike says (he's one half of the architect/lead developer team for the ASPxGridView, by the way), some interesting comments.

I'm sorry to say I'm amazed at some of them that seem to imply we don't do paging of large datasets. Or that our paging only works in read-only mode. Or that our paging only happens for non-grouped records. Or that our grid is not performant with large datasets. Or...

All of those points are quite simply wrong and I'm baffled that they've come up.

Another point of view that came up is that the grid's functionality is somehow "excessive", that some large proportion of grid use doesn't need grouping or filtering or summaries and that the grid should be able to switch them off (well, yes, you can to a certain extent) or perhaps there should be a lighter-weight grid that doesn't even have these features, together with a heavier-weight grid that has some of them, followed by the super-duper ultragrid that has it all.

Well, yes, we could do the latter thing, but to be honest there are a lot more features and a lot more functionality that has been requested and that we want to do that has a higher precedence and priority than this what would undoubtedle be a major architectural change.

To be honest I would dispute that end-users want grids only for CRUD puposes, but hey I find MS Money's transaction grid annoying for those very reasons. Its developers have decided that it should be essentially a display of data for which analysis is not a requirement. So I can't analyze spending with a particular Payee on the grid by grouping on Payee (or maybe grouping on Month then Payee), I have to navigate to another page to do that. Bloody waste of time. Anyway, different horses for different courses.

More to come.

Cheers, Julian

15 May, 2008
Mike Falcon (DevExpress)

"If you want to sell me your marketing stuff, save yourself the trouble."

Actually I'm the lead developer. And I'm not trying to sell anything. My goal is to deliver the best UI components.

"If I have to group on a set of 100K rows, and there's no paging passed, nowhere will you get any performance whatsoever, as there's no paging parameter passed to the datasourcecontrol. "

Maybe you have to try our demo first?   BTW, our Windows demo shows what queries are generated and passed to the SQL server and what data is retrieved.

"If you're now claiming these parameters ARE passed, show me. "

Hmm. Where did I say that?  At the moment, this is all on a special build on my laptop since, as Julian says, we don't support the paging parameters yet. That's not to say we don't support paging, we do, and in a much larger sense than being debated here.

"I'm sorry, but this is your solution to the real problem? That I have to implement your proprietary interface on my runtime library, and therefore have to ship a devexpress dll just because your grid sucks in the paging department? What if I want to pull some entities with a filter, and some related entities from the db from a set of 10 million rows into the grid"

If the only goal is to use the grid to navigate 10 million rows by clicking on pager - than, yes, I suppose we suck. But, I don't know anybody who wants to see 1 million pages, one after the other. My goal as designer is to enable our and your customers to write applications where the end-users can browse and analyse tabular data as they want. Our current solution enables  them to do all that, including the data manipulations, with LINQ to SQL. Adding the paging parameters to the grid has already been done (in a special build) but using it means that the advanced capabilities of the grid are disabled. We are discussing internally what to do about this special experimental build now: document and release it?  Or experiment with it some more?

15 May, 2008
Alan Schrank

Julian, you are saying that the ASPxGridView does pass paging information to the datasource control as part of ExecuteSelect in a way that other third party datasource controls can be used with the grid to return just the page of information requested?  Or something else?

15 May, 2008
Ray Navasarkian (DevExpress)

And Frans

If you really want to solve this problem, we are a phone call away. I asked Julian to track down your number to call you and do a conference call, but unfortunately one could not be located.

As far as those threads on your site suggesting that we are not trying to work with you because for some reason we are trying to push XPO....that's just silly. We've tried to work with you in the past and we are more than happy trying to do so in the future. It's easy to reach us Frans - our phone number is (702) 262 0609. If you dont want to pay for long distance, give us your phone number and we'll call you. Julian/Roman/and I are waiting here ready to listen.

To everyone else who is trying to use Frans' product...we are more than happy to accomodate other people's products wherever and whenever we can. There are things we can do and things we wont do...

15 May, 2008
Ray Navasarkian (DevExpress)

Alan,

What we are saying is that just because Frans thinks its right does not mean it is so. What we are trying to do is work with the guy instead of a shouting match...

What we are trying to avoid is what is happening on his forum where its become a discussion of our grand conspiracy to make decisions to hurt his product.

As I said, and I will repeat...we are willing to listen to Frans and try to help him. We are a phone call away and we are trying to work through things. If we can we will, and if we cannot we wont. I want to emphasize that just because Frans thinks its right does not mean it is so and we have to make business decisions that serve the needs of a much larger customer base than those using his product.

15 May, 2008
Alan Schrank

>>What we are saying is that just because Frans thinks its right >>does not mean it is so.

Ray,

I'm sorry if I wasn't clear in my question or my intention. I have no idea who is right and who is wrong or even if there is a right and a wrong.

I asked my question to try to see if I was misunderstanding Julian's response or Fran's statement.  To me, his response implied that the "standard" protocol is being followed by the grid, but Fran's is saying it isn't.

My current understanding (right or wrong) is that it doesn't (follow the protocol) but the DevX position is that, that protocol isn't needed, (at least not if you use XPO).  

I'm not clear, yet, on the non-xpo solutions for providing data to the grid.

15 May, 2008
Julian Bucknall (DevExpress)

Alan

There are numerous ways to use datasources with our AsPxGridView. Just use it as you would a standard gridview. You certainly don't have to use XPO. But, if you want the advanced features of the grid, if you want the highly performant display and analysis of your data, you have to use an innovative paging facility like XPO or our implementation that uses LINQ.

That's all.

Cheers, Julian

15 May, 2008
Julian Bucknall (DevExpress)

All

Please see this blog post (community.devexpress.com/.../quot-paging-grid-team-paging-grid-team-quot.aspx) for details of how we're addressing this issue.

As an addendum, those of you who've posted comments, either pro or con, to this thread will have noticed that I haven't released them for publication. I thought about releasing just those which were professional in demeanor, whether they were for our position or against it, but that would have meant doing some rigorous censoring. That just leaves a bad taste. I'll admit, for some of them, I can't say I've ever received such unprofessional comments in this job before, even when I've discussed some fairly contentious issues.

I would just like to point out that you always have a choice: if you don't like what any third-party vendor provides, if you disagree with their position or their stated goals, you can always find an alternate offering elsewhere. The ASP.NET control market is large; it is a commodity market, after all. That sentiment applies equally to us and to our competitors, which is why we promote the slogan: Download, Compare, Decide.

Cheers, Julian

19 May, 2008
Yevgeniy Goldberg

Julian,

Could you please point to the actual sample application (ASPxGridView with XPO) that you build in your tutorial.

Thanks!

-Eugene

11 August, 2008

Please login or register to post comments.