ASP.NET、SQL 2005“分页”

发布于 2024-07-26 03:35:57 字数 399 浏览 6 评论 0原文

这是该问题的后续: ASP.NET 下一个/上一个按钮在表单中显示单行

正如上面页面上所述,页面上有一个上一页/下一页按钮,一次检索一行。

总共有大约 500,000 行。

当我“翻页”每个订阅号时,表格中就会填写订阅者详细信息。 我应该在 SQL Server 上使用什么方法?

使用 ROW_NUMBER() 函数似乎有点矫枉过正,因为它必须对所有 ~500.000 行进行编号(我猜?),那么还有哪些其他可能的解决方案?

提前致谢!

This is a followup on the question:
ASP.NET next/previous buttons to display single row in a form

As it says on the page above, theres a previous/next button on the page, that retrieves a single row one at a time.

Totally there's ~500,000 rows.

When I "page" through each subscribtion number, the form gets filled with subscriber details. What approach should I use on the SQL server?

Using the ROW_NUMBER() function seems a bit overkill as it has to number all ~500.000 rows (I guess?), so what other possible solutions are there?

Thanks in advance!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

屋檐 2024-08-02 03:35:58

ROW_NUMBER() 可能是您的最佳选择。

来自这篇 MSDN 文章: http://msdn.microsoft.com/en-us /library/ms186734.aspx

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

只需用所需行号的参数替换 50 和 60 即可。

ROW_NUMBER() is probably your best choice.

From this MSDN article: http://msdn.microsoft.com/en-us/library/ms186734.aspx

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

And just subsititute 50 and 60 with a parameter for the row number you want.

记忆之渊 2024-08-02 03:35:58

Tommy,如果您的用户有时间每行一页翻阅 500,000 行,那么他/她是独一无二的。

我想我在这里所说的是你也许能够提供更好的用户体验。 什么时候 - 页数太多? 构建搜索功能。

Tommy, if your user has time to page through 500,000 rows at one page per row, then he/she is unique.

I guess what I am saying here is that you may be able to provide a better UX. When - Too many pages? Build a search feature.

无尽的现实 2024-08-02 03:35:58

有两种可能的解决方法(为此,使用从 201 开始、每页 100):

SQL

SELECT TOP 100 * FROM MyTable WHERE ID > 200 ORDER BY ID

LINQ to SQL

var MyRows = (from t in db.Table 
              order by t.ID ascending
              select t).Skip(200).Take(100)

如果您的 ID 字段具有聚集索引,请使用以前的。 如果不是,这两者将花费相同的时间(LINQ 返回 500,000 行,然后跳过,然后获取)。

如果您要按非 ID 的内容进行排序并且已将其编入索引,请使用 ROW_NUMBER()

编辑:因为OP不按ID排序,所以唯一的解决方案是ROW_NUMBER(),这是我放在最后的子句。

在这种情况下,该表未建立索引,因此请参阅 此处了解如何建立索引以提高查询性能的想法。

There are two potential workarounds (for this purpose, using a start of 201, pages of 100):

SQL

SELECT TOP 100 * FROM MyTable WHERE ID > 200 ORDER BY ID

LINQ to SQL

var MyRows = (from t in db.Table 
              order by t.ID ascending
              select t).Skip(200).Take(100)

If your ID field has a clustered index, use the former. If not, both of these will take the same amount of time (LINQ returns 500,000 rows, then skips, then takes).

If you're sorting by something that's NOT ID and you have it indexed, use ROW_NUMBER().

Edit: Because the OP isn't sorting by ID, the only solution is ROW_NUMBER(), which is the clause that I put at the end there.

In this case, the table isn't indexed, so please see here for ideas on how to index to improve query performance.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文