• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#拼接SQL语句,SQLServer2005+,多行多列大数据量情况下,使用ROW_NUMBER实现的高效 ...

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

如果项目中要用到数据库,铁定要用到分页排序。

之前在做数据库查询优化的时候,通宵写了以下代码,来拼接分页排序的SQL语句。

 1 /// <summary>
 2 /// 单表(视图)获取分页SQL语句
 3 /// </summary>
 4 /// <param name="tableName">表名或视图名</param>
 5 /// <param name="key">唯一键</param>
 6 /// <param name="fields">获取的字段</param>
 7 /// <param name="condition">查询条件(不包含WHERE)</param>
 8 /// <param name="collatingSequence">排序规则(不包含ORDER BY)</param>
 9 /// <param name="pageSize">页大小</param>
10 /// <param name="pageIndex">页码(从1开始)</param>
11 /// <returns>分页SQL语句</returns>
12 public static string GetPagingSQL(
13     string tableName,
14     string key,
15     string fields,
16     string condition,
17     string collatingSequence,
18     int pageSize,
19     int pageIndex)
20 {
21     string whereClause = string.Empty;
22     if (!string.IsNullOrEmpty(condition))
23     {
24         whereClause = string.Format("WHERE {0}", condition);
25     }
26 
27     if (string.IsNullOrEmpty(collatingSequence))
28     {
29         collatingSequence = string.Format("{0} ASC", key);
30     }
31 
32     StringBuilder sbSql = new StringBuilder();
33 
34     sbSql.AppendFormat("SELECT  {0} ", PrependTableName(tableName, fields, ','));
35     sbSql.AppendFormat("FROM    ( SELECT TOP {0} ", pageSize * pageIndex);
36     sbSql.AppendFormat("                    [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence);
37     sbSql.AppendFormat("                    {0} ", key);
38     sbSql.AppendFormat("          FROM      {0} ", tableName);
39     sbSql.AppendFormat("          {0} ", whereClause);
40     sbSql.AppendFormat("        ) AS [_TempTable_] ");
41     sbSql.AppendFormat("        INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key);
42     sbSql.AppendFormat("WHERE   [_TempTable_].[_RowNum_] > {0} ", pageSize * (pageIndex - 1));
43     sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC ");
44 
45     return sbSql.ToString();
46 }
47 
48 /// <summary>
49 /// 给字段添加表名前缀
50 /// </summary>
51 /// <param name="tableName">表名</param>
52 /// <param name="fields">字段</param>
53 /// <param name="separator">标识字段间的分隔符</param>
54 /// <returns></returns>
55 public static string PrependTableName(string tableName, string fields, char separator)
56 {
57     StringBuilder sbFields = new StringBuilder();
58 
59     string[] fieldArr = fields.Trim(separator).Split(separator);
60     foreach (string str in fieldArr)
61     {
62         sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator);
63     }
64 
65     return sbFields.ToString().TrimEnd(separator);
66 }

假设有如下产品表:

 1 CREATE TABLE [dbo].[Tbl_Product]
 2     (
 3       [ID] [int] IDENTITY(1, 1)
 4                  NOT NULL ,
 5       [ProductId] [varchar](50) NOT NULL ,
 6       [ProductName] [nvarchar](50) NOT NULL ,
 7       [IsDeleted] [int] NOT NULL
 8                         CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) ,
 9       CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC )
10         WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
11                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
12                ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
13     )
14 ON  [PRIMARY]
  1. Tbl_Product->ID(序号,非空,自增)
  2. Tbl_Product->ProductId(产品Id,主键)
  3. Tbl_Product->ProductName(产品名称,非空)
  4. Tbl_Product->IsDeleted(虚拟删除标记,非空)

调用BasicFunction.GetPagingSQL("Tbl_Product", "ID", "ID,ProductId,ProductName", "IsDeleted=0", "ProductName ASC, ID DESC", 5, 5),BasicFunction为分页排序方法所在的静态类,生成的分页排序SQL语句如下(已手动调整了格式):

 1 SELECT  Tbl_Product.ID ,
 2         Tbl_Product.ProductId ,
 3         Tbl_Product.ProductName
 4 FROM    ( SELECT TOP 25
 5                     [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) ,
 6                     ID
 7           FROM      Tbl_Product
 8           WHERE     IsDeleted = 0
 9         ) AS [_TempTable_]
10         INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID
11 WHERE   [_TempTable_].[_RowNum_] > 20
12 ORDER BY [_TempTable_].[_RowNum_] ASC 
  • 查询的字段列表,去掉了不关心的字段(这里为IsDeleted,因为条件里面IsDeleted=0,查出来的产品都是没被删除的);
  • 排序依据,在调用该方法时,应尽量确保排序的依据可以唯一确定记录在结果集中的位置(这里添加了辅助排序依据,ID DESC,如果产品重名,添加的晚的排在前面);
  • 性能优化的一点儿建议:如果字段的值是统计出来的,通常是在视图中,且结果集很大,且需要对结果集分页排序,可以使用临时表规避不稳定的查询效率(关于不稳定的查询效率,会在之后的文章中示例);
  • 另外一点儿建议,使用ROW_NUMBER时,切记一定要和“TOP n”一起使用,n等于int.MaxValue都比不加“TOP n”时要快

最后,拜托哪位好心人士给测试下性能,拜托了,本人数据库菜鸟,不太懂得数据库的性能测试。

我只知道我对我写的分页排序还是很有信心的,(*^__^*) 嘻嘻!

首发:博客园->剑过不留痕,欢迎转载,前提是注明出处:http://www.cnblogs.com/return8023/archive/2012/05/20/2510983.html


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
圆角按钮c#发布时间:2022-07-14
下一篇:
C#Winform窗体界面”假死”后台线程阻塞解决办法–BeginInvoke发布时间:2022-07-14
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap