DevExpress ASPxGridView with Large Data

0
votes

I'm fixing up a website which makes extensive use of the DevExpress ASPxGridView control to search against tables on a SQL Server. The results are then paged and parsed. Some of the search results can be fairly large and I suspect in those cases the built in pager for the ASPxGridView with the native SqlDataSource is prohibitively slow. On one query I observed the network traffic between the SQL Server and the IIS Server to be 1.5 GB over the span of just under a minute. Running the same query with an ORDER BY (SELECT NULL) OFFSET x ROWS FETCH NEXT y ROWS ONLY took a split second. Taking advantage of this SQL side paging on the control is another matter.

It seems my limitations and trade offs are the following:

  1. The DevExpress ASPxGridView built in pager can't have the number of items explicitly set.
  2. The independent DevExpress ASPxPager doesn't support client side events.
  3. Replacing the SqlDataSource with LinqServerModeSource or XpoDataSource still has to parse the entire result set from the SQL Server to get the full count and will probably suffer the same performance issues.

I would like suggestions if any of following are possible solutions:

  1. Is there any way to force the DevExpress ASPxGridView built in pager to use an external count?
  2. Is there any way to wrap the DevExpress ASPxPager with say an Asp.Net UpdatePanel or DevExpress ASPxCallbackPanel to support client side events?
  3. Can the LinqServerModeSource or XpoDataSource collect results from the SQL query as the ASPxGridView is paged with the total count provided by other means?
1
asp.netdevexpressaspxgridview
Have you tried this? - Filip
That's what question 3 was about since in all the DevExpress examples the datasource is a static table but in this case the datasource is a query or stored procedure with some full text indexing. - Alton XL
I think you have to create view based on that query if you want to use server mode. Although you could check that with devex support. - Filip
The conditions of the full text search are very dynamic so a fixed view wouldn't be possible. - Alton XL
The other challenge with using LinqServerModeSource is that the database changes although the server remains the same. - Alton XL

1 Answers

0
votes

As mentioned here, it is possible to use SQL server side paging with ASPxGridView and ObjectDataSource by setting the ASPxGridView.DataSourceForceStandardPaging = true. DevExpress links to this example application on GitHub which is a bit broken, but easy to fix.

It works but, as they say:

[...] it is not possible to filter the ObjectDataSource datasource when the ASPxGridView.DataSourceForceStandardPaging is used.

Currently I'm trying to cheat the ASPxGridView by not setting DataSourceForceStandardPaging, but still use server side paging with ObjectDataSource: Perhaps it is possible to insert dummy rows into the result set in ObjectDatasource's SelectMethod, which then makes ASPxGridView "think" that is has retrieved all rows from the database, so that the pager can count the correct total rows number.

But I am not yet done with it... most probably the SubstituteFilterand CustomColumnSort events must then be handled to prevent the ASPxGridView from filtering and sorting the dummy records out.