快捷導(dǎo)航

C#拼接SQL語句用ROW_NUMBER實現(xiàn)的高效分頁排序

更新時間：2012年05月23日 15:53:36 作者：

C#拼接SQL語句，SQL Server 2005+，多行多列大數(shù)據(jù)量情況下，使用ROW_NUMBER實現(xiàn)的高效分頁排序

如果項目中要用到數(shù)據(jù)庫，鐵定要用到分頁排序。之前在做數(shù)據(jù)庫查詢優(yōu)化的時候，通宵寫了以下代碼，來拼接分頁排序的SQL語句

 
/// <summary> 
/// 單表（視圖）獲取分頁SQL語句 
/// </summary> 
/// <param name="tableName">表名或視圖名</param> 
/// <param name="key">唯一鍵</param> 
/// <param name="fields">獲取的字段</param> 
/// <param name="condition">查詢條件（不包含WHERE）</param> 
/// <param name="collatingSequence">排序規(guī)則（不包含ORDER BY）</param> 
/// <param name="pageSize">頁大小</param> 
/// <param name="pageIndex">頁碼（從1開始）</param> 
/// <returns>分頁SQL語句</returns> 
public static string GetPagingSQL( 
string tableName, 
string key, 
string fields, 
string condition, 
string collatingSequence, 
int pageSize, 
int pageIndex) 
{ 
string whereClause = string.Empty; 
if (!string.IsNullOrEmpty(condition)) 
{ 
whereClause = string.Format("WHERE {0}", condition); 
} 

if (string.IsNullOrEmpty(collatingSequence)) 
{ 
collatingSequence = string.Format("{0} ASC", key); 
} 

StringBuilder sbSql = new StringBuilder(); 

sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ',')); 
sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex); 
sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence); 
sbSql.AppendFormat(" {0} ", key); 
sbSql.AppendFormat(" FROM {0} ", tableName); 
sbSql.AppendFormat(" {0} ", whereClause); 
sbSql.AppendFormat(" ) AS [_TempTable_] "); 
sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key); 
sbSql.AppendFormat("WHERE [_RowNum_] > {0} ", pageSize * (pageIndex - 1)); 
sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC "); 

return sbSql.ToString(); 
} 

/// <summary> 
/// 給字段添加表名前綴 
/// </summary> 
/// <param name="tableName">表名</param> 
/// <param name="fields">字段</param> 
/// <param name="separator">標(biāo)識字段間的分隔符</param> 
/// <returns></returns> 
public static string PrependTableName(string tableName, string fields, char separator) 
{ 
StringBuilder sbFields = new StringBuilder(); 

string[] fieldArr = fields.Trim(separator).Split(separator); 
foreach (string str in fieldArr) 
{ 
sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator); 
} 

return sbFields.ToString().TrimEnd(separator); 
} 

假設(shè)有如下產(chǎn)品表：

復(fù)制代碼代碼如下:

 
CREATE TABLE [dbo].[Tbl_Product] 
( 
[ID] [int] IDENTITY(1, 1) 
NOT NULL , 
[ProductId] [varchar](50) NOT NULL , 
[ProductName] [nvarchar](50) NOT NULL , 
[IsDeleted] [int] NOT NULL 
CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) , 
CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC ) 
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] 
) 
ON [PRIMARY] 

Tbl_Product->ID（序號，非空，自增）
Tbl_Product->ProductId（產(chǎn)品Id，主鍵）
Tbl_Product->ProductName（產(chǎn)品名稱，非空）
Tbl_Product->IsDeleted（虛擬刪除標(biāo)記，非空）
調(diào)用BasicFunction.GetPagingSQL("Tbl_Product", "ID", "ID,ProductId,ProductName", "IsDeleted=0", "ProductName ASC, ID DESC", 5, 5)，BasicFunction為分頁排序方法所在的靜態(tài)類，生成的分頁排序SQL語句如下（已手動調(diào)整了格式）：

復(fù)制代碼代碼如下:

 
SELECT Tbl_Product.ID , 
Tbl_Product.ProductId , 
Tbl_Product.ProductName 
FROM ( SELECT TOP 25 
[_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) , 
ID 
FROM Tbl_Product 
WHERE IsDeleted = 0 
) AS [_TempTable_] 
INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID 
WHERE [_RowNum_] > 20 
ORDER BY [_TempTable_].[_RowNum_] ASC 

查詢的字段列表，去掉了不關(guān)心的字段（這里為IsDeleted，因為條件里面IsDeleted=0，查出來的產(chǎn)品都是沒被刪除的）；
排序依據(jù)，在調(diào)用該方法時，應(yīng)盡量確保排序的依據(jù)可以唯一確定記錄在結(jié)果集中的位置（這里添加了輔助排序依據(jù)，ID DESC，如果產(chǎn)品重名，添加的晚的排在前面）；
性能優(yōu)化的一點兒建議：如果字段的值是計算出來的，如：總價=單價*數(shù)量，而此時需要總價大于多少的記錄，還得拿總價遞增或者遞減排序，如果不要臨時表，數(shù)據(jù)量大的時候，就等著買新電腦吧！你問我為什么要買新電腦，哦，因為你會把現(xiàn)在的電腦砸掉！O(∩_∩)O~
另外一點兒建議，使用ROW_NUMBER時，切記一定要和“TOP n”一起使用，n等于int.MaxValue都比不加“TOP n”時要快。
最后，拜托哪位好心人士給測試下性能，拜托了，本人數(shù)據(jù)庫菜鳥，不太懂得數(shù)據(jù)庫的性能測試。

我只知道我對我寫的分頁排序還是很有信心的，(*^__^*) 嘻嘻！

首發(fā)：博客園->劍過不留痕

您可能感興趣的文章: