优化分页&使用 EnableCaching = true 的 ObjectDataSource 进行排序

发布于 2024-08-22 03:32:14 字数 1905 浏览 6 评论 0原文

我正在使用使用 Linq-To-SQL 类备份的 ODS(ObjectDataSource) 来填充页面上的 Gridview。

考虑到性能 - 我禁用了 Gridview 的 Viewstate 并在 ODS 中启用了缓存。

除此之外，我还优化了 Linq-to-SQL 类中的 Search 方法以使用 .skip & 。 .take 方法仅获取“一页”记录。

现在的问题是，由于缓存，ODS 无法自行对记录集进行“排序”。正如该线程的详细描述：

当我在自定义分页和排序中启用缓存时，GridView 排序不起作用

人们建议使用自定义排序并实现“比较器”，但我相信这会破坏我的性能优势。

http://forums.asp.net/t/1344883.aspx

我是准备好在排序时进行数据库旅行，但是当缓存打开时如何将其分离？

仅供参考，我已经有一个 AJAX 更新面板，其中有 Gridview (EnableViewstate = false) 和 ODS (EnableCaching=true)。希望我走在正确的道路上...建议值得赞赏。

我必须使用“自定义排序”在应用程序端执行排序（即添加额外的方法以启用对通用对象集合的排序）。这个解决方案是不可接受的，因为它要求我从数据库中提取所有记录，然后对它们进行排序！

首先，我不相信应用程序可以比数据库做更好/更快的排序。其次 - 这破坏了我因优化分页而获得的整体性能优势 - 我使用 .skip() 和 .take() LINQ 方法仅获取“一页”记录。

好吧，最后我不得不发明一个解决方案我自己的。可能仅限于我的同类场景但肯定是很多更容易并且还保留了两个分页的优化为以及数据缓存。

我的解决方案： 我点击了 Gridview 的“排序”事件。如果我允许 ODS 尝试自行对缓存数据进行排序，则会触发“自定义排序”错误。相反，现在我手动执行排序并取消排序事件。为此，我只需在 ODS 中显式设置“orderBy”参数，并将其设置为 Gridview 的“排序”事件中的新排序表达式。这将刷新网格，最后我会这样做：

odsOrganization.SelectParameters["orderBy"].DefaultValue = GetSortExpr(e.SortExpression);
...
e.Cancel = true;

这告诉 ODS 取消排序（我之前已经执行过取消活动 - 如上所述）。它是就像我欺骗了 ODS 并处理后台排序。谢谢 ODS 认为 'SelectParameters["orderBy"]' 有更改并执行一次选择再次。 I 上一个排序自动存储在 'odsOrganization.SelectParameters["orderBy"].DefaultValue' 我可以在后续中使用它迭代。

我仍在测试这个，但令人惊讶的是，只需更新参数的 DefaultValue ODS 就会返回来获取数据。这会保留缓存，直到用户执行排序（它从缓存中获取数据以进行其他操作）并返回到数据库进行排序。希望它对我有用！

原文

I'm using an ODS(ObjectDataSource) backed-up with a Linq-To-SQL class to populate Gridview on my page.

Considering the performance - I've disabled the Viewstate of the Gridview and enabled caching in the ODS.

Apart from this, I've also optimized the Search method in the Linq-to-SQL class to use the .skip & .take methods to fetch only a 'pageful' of records.

Now, the problem is that due to caching the ODS is unable to 'sort' the record set on its own. As detailed on this thread:

GridView sorting doesn't work when I Enable Caching in Custome Paging and sorting

People recommend to use custom sort and implement 'Comparer' but I believe this ruins my performance benefits.

http://forums.asp.net/t/1344883.aspx

I'm ready to make a DB-trip while sorting but how to separate that when the Caching is turned-on?

FYI, I already have an AJAX update panel in which I've got this Gridview (EnableViewstate = false) and ODS (EnableCaching=true). Hope I'm on the right path ... suggestions are appreciated.

I've to perform sorting on app side using the 'Custom-sort' (that is add extra methods to enable sorting on a generic collection of objects). This solution was not acceptable because it demanded that I pull ALL the records from the DB and then perform sorting on them!

Firstly, I don't believe app can do better/faster sorting then DB. And secondly - this ruins the whole performance benefit that I'm getting due to optimized pagination - I'm using .skip() and .take() LINQ methods to fetch only a 'pageful' of records.

Well, finally I had to invent a fix of
my own. It might be limited to my kind
of scenario but for sure its much
easier and also preserves the
optimization of both pagination as
well as data-caching.

MY SOLUTION:
I've tapped the Gridview's 'Sorting' event. The 'custom-sort' error triggers if I allow the ODS to try to do the sorting on its own on the cached data. Instead of that, now I perform sorting manually and cancel the sorting-event.
For this - I just have to make the 'orderBy' parameter explicit in ODS and set it to the new sort-expression in Gridview's 'Sorting' event. This will refresh the Grid and at the end I do:

odsOrganization.SelectParameters["orderBy"].DefaultValue = GetSortExpr(e.SortExpression);
...
e.Cancel = true;

this tells the ODS to cancel sorting
(which I already performed before
canceling the event - as above). ITs
like I'm cheating the ODS and handling
the sorting in background. And thanks
to ODS that it senses that the
'SelectParameters["orderBy"]' has
changed and performs a select once
again. I The previous sort
automatically gets stored in the
'odsOrganization.SelectParameters["orderBy"].DefaultValue'
which I can use in sub-sequent
iterations.

I'm still testing this one but its amazing that just by updating the parameter's DefaultValue ODS goes back to fetch the data. This preserves the cache until the user performs sort (it takes data from cache for other operations) and goes back to the DB to sort. Hope its going to work for me!

分享到QQ

分享到微博