高效的上一页下一页分页实现思路

在进行开发的时候,最常用的功能是分页,分页的实现很简单,方法也很多,有使用存储过程实现的,也有人使用row_number函数通过sql语句直接实现分页的,这些方法开发起来简单又高效。可是随着数据量一天天增大,慢慢就可以感觉到加载越来越缓慢。罪魁祸首是由于所有分页原理都使用了select count(*)聚合函数获取总记录数,用来计算总页数。对于需要显示页码的分页形式,目前没有想到很好的实现思路。不过对于只有上一页和下一页这种的分页形式,目前发现了一个更好的实现思路,具体实现如下:

创建测试用的表,并填充数据:


CREATE TABLE sysUser
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   name NVARCHAR(50),
   age INT
)

DECLARE @i INT
SET @i=0
WHILE @i<1000
BEGIN
    SET @i=@i+1
    INSERT INTO dbo.sysUser
            ( name, age )
    VALUES  ( N'User'+LTRIM(STR(@i)), -- name - nvarchar(50)
              32  -- age - int
              )
END

创建一个实体类型User:


public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

/// <summary>
        /// 为了演示写的一个简单的调取函数
        /// </summary>
        /// <param name="sql">();
         public List<User> Query(string sql)
          {
            List<User> _list=List<User>();
            using (SqlConnection conn = new SqlConnection(数据库连接字符串))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (dr.Read())
                    {
                        _list.Add(new User {  Id=dr.GetInt32(0), Name=dr.GetString(1),Age=dr.GetInt32(2)});
                    }
                    dr.Close();
                    dr.Dispose();
                }
            }
            return _list;
        }

/// <summary>
        /// 返回要显示的数据
        /// </summary>
        /// <param name="strwhere">查询条件,可为空</param>
        /// <param name="orderby">排序条件,可为空</param>
        /// <param name="page">当前页数</param>
        /// <param name="pagesize">每天显示多少条</param>
        /// <param name="hasNextPage">有些站前台最多显示多少页的数据,
///例如有些站前台最多显示100页的数据,可以传递()=>{return page<100;},要想显示所有页的数据,可以传递()=>{return true;}</param>
        /// <param name="nextpage">返回参数,大于零,列表页会显示下一页,值为零,列表页不显示下一页</param>
        /// <returns></returns>
        public List<User> GetUserList(
string strwhere, string orderby, int page, int pagesize, Func<bool> hasNextPage, out int nextpage)
        {
            string sqlorder="id desc";
            string sqlwhere=" 1=1";
            if (!string.IsNullOrEmpty(orderby)) sqlorder = orderby;
            if (!string.IsNullOrEmpty(strwhere)) sqlwhere = sqlwhere + strwhere;
            int startnum = (page - 1) * pagesize + 1;    //开始行和之前的计算方式没有变化
             //主要变化是计算结束行,结束行每次要多获取一条,目的只是为了判断下一页是否有数据,多的这一条数据并不显示在页面上
            int endnum = page * pagesize + 1;
            string sql = string.Format(
"with temp as (select id,name,age,ROW_NUMBER() over(order by {0}) as pn from sysUser where {1})
select * from temp where pn between {2} and {3}"
, sqlorder, sqlwhere, startnum, endnum);

            List<User> _list = Query(sql);
            nextpage = 0;
            if (_list.Count == pagesize + 1)
            {
                if (hasNextPage())
                {
                    nextpage = page + 1;
                }
                else
                {
                    nextpage = 0;
                }
                _list.RemoveAt(_list.Count - 1);
            }
            return _list;
        }

使用的时候只需通过如下方式进行调取:


int nextpage = 0;      //页面可以该变量来判断是否显示下一页,大于零显示下一页。
List<User> list = GetUserList("", "", page, 2, () => {return page < 48; },out nextpage);  //page为传递过来的当前第几页.

以上仅为上一页,下一页分页的一个具体实现,在数据量大的情况下,业务有没有特殊需求,只需要提供上一页下一页的情况下可以参考此类实现,减轻数据库压力。

发表在 Net | 评论关闭

大数据下的分页count引起的性能瓶颈及解决思路

在大部分系统里,数据呈现的时候都或多或少需要对数据进行分页处理之后再呈现,在数据量没有达到一定级别之前。无论如何实现分页都不会出现问题,而且分页实现的思路多种多样【sqlserver系统里使用ROW_NUMBER可以很方便的实现分页】,一旦数据量达到一定的级别之后,就会明显感觉到分页在呈现数据的时候有明显的延迟,产生性能瓶颈的根本原因是获取总记录数使用了聚合函数count,而又没有其它可以替代的方式。只能根据业务的进行简单的调整,使其能满足业务的需要。并没有打算实现一个完整的分页例子,也并不打算实现一个分页存储过程,只是将如何调整一个分页缓慢系统的思路整理了一下,大部分思路都是来自网路。

第一种:
这种方法最简单,也是最不灵活的一种,如果只是想要得到一张大表的总记录数,该方法是比较快速的。假如要获取某些条件的总记录数,该方法就不可行了。例子如下:

SELECT rows FROM sysindexes WHERE id=OBJECT_ID('要查询的表名') AND indid=1

第二种:
这种方法比较简单,并且也比较灵活。就是对获得的总记录数进行缓存。既然要缓存总记录数,一般都是key-value,value就是总记录数,以什么作为key,可以是传递过来的url,根据参数的不同进行缓存,使用该方法要将page参数过滤掉,也可以是根据获取总记录数的sql语句,对sqlcount字符串进行md5加密作为key【这种方法有个弊端,就是假如sql语句写的不够规范,随意的大小写或者相同几个where条件任意变换位置,获得的md5字符串将不一样】,可以将数据保存到应用程序内存中,也可以保存到文件中,还可以保存到数据库中,设置一个简单的过期策略。使用md5作为key的话,还可以将sql语句一并保存,这样还可以定时或者服务器空闲时更新相应语句的总记录数。

第三种:
对于只有上一页和下一页的分页思路,这种分页其实是不需要获得总记录数的。例如每页要显示20条记录,每次获取的时候只需获取21条,显示还是显示20条,判断一下获取的记录数只要大于20,就显示下一页即可。这种分页方式是最高效的。

第四种:
利用Set Rowcount函数快速滚动到我们要行数,并将id记下,进行分页。


DECLARE @page INT
DECLARE @pagesize INT
DECLARE @startindex INT
DECLARE @id int
SET @page=1
SET @pagesize=48
SET @startindex=(@page-1)*@pagesize+1
SET ROWCOUNT @startindex
SELECT @id=id FROM tablename ORDER BY id asc

SET ROWCOUNT @pagesize
--生产环境最好不要使用*
SELECT * FROM tablename WHERE id>=@id ORDER BY id asc
SET ROWCOUNT 0

可以将上面的例子修改成存储过程方便使用,将需要的page,pagesize传递进来,要是为了灵活通用,可以将tablename,条件,排序字段,排序方式也传递过来。需要注意一点,如果以asc排序,id>=@id,如果以desc排序,id<=@id

第五种:
通过select … into 将需要的数据导入到一张临时表里,然后对临时表进行分页操作。这种方法效率有多高,没有对其进行测试。不过使用select … into将数据导入到一张临时表之后,可以使用@@RowCount系统变量方便的获取总记录数,SET @SumCount=@@RowCount,这比count获取总记录数高多了。

发表在 Net, 杂文 | 评论关闭

微信js-sdk开发中遇到的问题和解决办法

微信越来越火,提供给开发人员的接口也越来越多,最近研究了一下微信JS-SDK,官方说明文档已经很全了。由于自己擅长Net平台下开发,将自己在测试过程中碰到的问题列出来,并提供解决方法。在Net下调取使用的是RestSharp工具,极大地提高了开发效率。
获取jsapi_ticket的方法就不列出来了,官网文档已经很详细了。
1,由于接口返回的都是json格式,发现将返回的json串反序列化为Dictionary,就不需要为每个不同的返回类型创建类了。可以直接使用dict["access_token"]获取access_token的值。很方便。

2,生成sha1签名,由于php下有相应的函数可以直接调取。Net需要使用System.Security.Cryptography命名空间下的类来生成。


private string SHA1(string str)
        {
            SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider();
            byte[] str1 = Encoding.UTF8.GetBytes(str);
            byte[] str2 = sha1.ComputeHash(str1);
            sha1.Clear();
            (sha1 as IDisposable).Dispose();
            //微信使用,不能用这个
            //return Convert.ToBase64String(str2);

            return BitConverter.ToString(str2).Replace("-","").ToLower();
        }

3,生成时间戳,php下可以直接使用time函数获得,Net使用下面的方法获取时间戳。


string timestamp = ((DateTime.UtcNow.Ticks - new DateTime(1970, 1, 1).Ticks) / 10000000).ToString();

5,当按照官网的文件在页面里设置完js之后,总是出现{errmsg:config:invalid signat}这个问题基本上就是签名生成的有问题了,可以将自己生成的签名工具和微信js接口签名校验工具生成的签名对比一下(http://mp.weixin.qq.com/debug/cgi-bin/sandbox?t=jsapisign)。要是一直提示{errmsg:config:ok},基本上是由于开启了调试模式,在wx.config里debug:true开发的时候设置为true,方便调试bug,一旦提示{errmsg:config:ok},基本上配置没问题了,需要将debug修改为false,才能开到分享的时候的效果。

发表在 Net | 评论关闭

sqlserver巧用row_number和partition by分组取top数据

SQL Server 2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单
分组取TOP数据是T-SQL中的常用查询, 如学生信息管理系统中取出每个学科前3名的学生。这种查询在SQL Server 2005之前,写起来很繁琐,需要用到临时表关联查询才能取到。SQL Server 2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单。下面是一个简单示例:
代码如下:
–1.创建测试表


create table #score
(
name varchar(20),
subject varchar(20),
score int
)

–2.插入测试数据


insert into #score(name,subject,score) values('张三','语文',98)
insert into #score(name,subject,score) values('张三','数学',80)
insert into #score(name,subject,score) values('张三','英语',90)
insert into #score(name,subject,score) values('李四','语文',88)
insert into #score(name,subject,score) values('李四','数学',86)
insert into #score(name,subject,score) values('李四','英语',88)
insert into #score(name,subject,score) values('李明','语文',60)
insert into #score(name,subject,score) values('李明','数学',86)
insert into #score(name,subject,score) values('李明','英语',88)
insert into #score(name,subject,score) values('林风','语文',74)
insert into #score(name,subject,score) values('林风','数学',99)
insert into #score(name,subject,score) values('林风','英语',59)
insert into #score(name,subject,score) values('严明','英语',96)
--3.取每个学科的前3名数据
select * from
(
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score
) T where T.num <= 3 order by subject
--4.删除临时表
truncate table #score
drop table #score

语法形式:ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
解释:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

http://www.68idc.cn/help/mysqldata/mssql2005/201208241713.html

发表在 Net | 评论关闭

巧用FileShare解决C#读写文件时文件正由另一进程使用的bug

在使用C#进行文件读写的时候,一旦对文件操作频繁,总会碰到一些令人措手不及的意外。例如经常会碰到的一个问题:

System.IO.IOException: 文件“XXX”正由另一进程使用,因此该进程无法访问此文件。这个问题是碰到最频繁的一个。其实可

以通过FileShare来完美解决这个问题,下面提供一个例子:


/*filePath为传过来的文件路径,endcode为设置的文件编码方式*/
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, 

FileShare.ReadWrite))
{
    fs.SetLength(0);
    using (StreamWriter writer = new StreamWriter(fs, endcode))
    {
          writer.Write(content);
          writer.Flush();
          writer.Dispose();
    }
    fs.Dispose();
}
发表在 Net | 评论关闭