我有超过200万条记录的数据库，我需要进行分页显示我的Web应用程序，其中必须有一个，每页10笔DataGrid 。
我已经tryied使用ROW_NUMBER()但这样一来会选择所有2万条记录，然后只得到10条记录。 我也tryied使用TOP 10 ，但我将不得不拯救第一和最后一个ID来控制页面。 我读过使用DataAdapter.Fill()将选择所有内容，然后让10条记录，我需要。
这是最好的办法吗？ 我应该使用DataAdapter.Fill() 或者使用SQL Server的功能ROW_NUMBER() 或者尝试使用TOP 10 ？
ALTER PROCEDURE [dbo].[SP_tblTest_SelectSpecificRecordsWithCTE]
@FromRow int = 1000000,
@PgSize int = 10
AS
BEGIN
;WITH RecordsRN AS
(
select ID, colValue, ROW_NUMBER() over(order by colvalue) as Num from tblTest
)
SELECT ID Value, colValue Text FROM RecordsRN WHERE Num between @FromRow AND (@FromRow+@PgSize-1)
END
这是我使用分页查询。 使用它，U将得到在4-5秒乌尔所需的10条记录。 我感到在3秒内得到10条记录，并在我的数据库总记录是10万美元，不使用排名前10位只会带来相同的10个记录每次。 在我的情况下，我保持页面大小和会话起始行号（@FromRow）和我这两个值传递到下面给定存储过程并得到结果。 进一步，如果你使用的是SQL 2012，你可能想使用OFFSET和获取下一个10行之类的话。 在谷歌搜索关于关键字OFFSET，你会看到在上面你所期望的结果。
谢谢
使用ROW_NUMBER()和执行（作为一个静态的效用函数GetPaginatedSQL在我的代码），自动包装原始的SQL查询为有限/分页之一。
这是我使用的一个：
namespace Persistence.Utils
{
public class SQLUtils
{
/// <summary>
/// Builds a paginated/limited query from a SELECT SQL.
/// </summary>
/// <param name="startRow">Start row</param>
/// <param name="numberOfRows">Number/quatity of rows to be expected</param>
/// <param name="sql">Original SQL (without its ordering clause)</param>
/// <param name="orderingClause">MANDATORY: ordering clause (including ORDER BY keywords)</param>
/// <returns>Paginated SQL ready to be executed.</returns>
/// <remarks>SELECT keyword of original SQL must be placed exactly at the beginning of the SQL.</remarks>
public static string GetPaginatedSQL(int startRow, int numberOfRows, string sql, string orderingClause)
{
// Ordering clause is mandatory!
if (String.IsNullOrEmpty(orderingClause))
throw new ArgumentNullException("orderingClause");
// numberOfRows here is checked of disable building paginated/limited query
// in case is not greater than 0. In this case we simply return the
// query with its ordering clause appended to it.
// If ordering is not spe
if (numberOfRows <= 0)
{
return String.Format("{0} {1}", sql, orderingClause);
}
// Extract the SELECT from the beginning.
String partialSQL = sql.Remove(0, "SELECT ".Length);
// Build the limited query...
return String.Format(
"SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS rn, {1} ) AS SUB WHERE rn > {2} AND rn <= {3}",
orderingClause,
partialSQL,
startRow.ToString(),
(startRow + numberOfRows).ToString()
);
}
}
}
上面的函数可能会改善，但是初步实现。
然后，在你的DAO，你应该是只是让这样的事情：
using (var conn = new SqlConnection(CONNECTION_STRING))
{
using (var cmd = conn.CreateCommand())
{
String SQL = "SELECT * FROM MILLIONS_RECORDS_TABLE";
String SQLOrderBy = "ORDER BY DATE ASC "; //GetOrderByClause(Object someInputParams);
String limitedSQL = GetPaginatedSQL(0, 50, SQL, SQLOrderBy);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
cmd.CommandText = limitedSQL;
// Add named parameters here to the command if needed...
adapter.SelectCommand = cmd;
adapter.Fill(ds);
// Process the dataset...
}
conn.Close();
}
希望能帮助到你。
我用下面的模式（自动地）生成分页子查询：
select top (@takeN) <your-column-list>
from (
select qSub2.*, _row=row_number() over (order by SomeColumn Asc, SomethingElse Desc)
from (
select top (@takeN + @skipN) <your-column-list>
from (
select <your-subquery-here>
) as qSub1
order by SomeColumn Asc, SomethingElse Desc
) as qSub2
) qSub3
where _row > @skipN
order by _row
注在这种模式：
@skipN行，然后进入下一个@takeN行。 _row的结果，你可以更换<your-column-list>与* ; 我用的是明确的列清单，因为它可以让我在子集运行时的一组列可以是非常有用，如只找到primay键列等。 order by子句应该是相同的; SQL Server的optmizer一般是足够聪明，明白这一点。 复制是的一个副作用top用于截断结果条款; top不上无序子查询合法的。 而顶部是有用的帮助查询优化器明白这个查询可能返回几行。 @takeN和@skipN而不是页码+基于尺寸的参数是相当小的。 首先，这是一个更灵活一点，并在查询简单一些，其次，它起着至的SQL Server的优势更好一点：数据库是不是特别灿烂约在首位优化这些类型的查询，并且希望像这样的外，简单顶部子句使得平凡的优化器了解可能的行的最大数量。 一般情况下，我会尽量避免在SQL做计算，我可以在代码中做的一样好，因为它往往混淆优化（虽然@ PAGECOUNT的特定情况下* @页面大小的实验已经表明，它不是一个大问题） 需要注意的是SQL Server 2012中支持新的偏移...获取条款正是这个方案是要简单得多。
