Posted on 2005-10-14 18:03
随心所欲 阅读(6938)
评论(12) 编辑 收藏 网摘 所属分类:
通讯/WebServerORM/DB
靠UI分页只是一部分
如果一个查询的结果数据量巨大,就需要数据查询的时候就分页,每次只返回其中一页
第一种方法:
思路是根据页号取出两部分数据,比如取出前90条,然后取出前100条,然后比较取出两次结果的差集。
在30万条记录的情况下,如果只分100页(结果有10000条记录),大约需要1分半钟。索引建得好的话,1分钟左右。
//select * from //这一句是不能修改的了,因为它是从结果中读取,所以必须要用*
//(select top @h_count (@filedlist) from @tableName .....) as big //取出符合条件的上限的记录
//where
//big.guid //这里是关键,根据主键从下限结果中过滤掉重复的记录(只留下不同的数据,也就是求交集)
//not in
//(select top @l_count guid from @table .....)//下限
//order @orderby //原来的格式,这里只保留了orderby之后的,应该保留条件之后所有的,包括gruopby什么的
函数类似如此:
public string MakeSqlPager(string sourceSql,int pageIndex)
{
//使用默认页面大小
string orderbyStr=sourceSql.Substring(sourceSql.ToLower().IndexOf("order by"));
int index=sourceSql.ToLower().IndexOf("select");
string bigRes="("+ sourceSql.Insert(index+6," top "+((pageIndex+1)*_pageSize).ToString()+" ")+") as big";
string smallRes="("+ sourceSql.Insert(index+6," top "+(pageIndex*_pageSize).ToString()+" ")+")";
return "select * from "+bigRes+" where big.guid not in "+smallRes+" "+orderbyStr;
}
这种方法还可以改进,就是第二次取过滤时从第一个的结果里面过滤。
第二种方法:
掐头去尾,程序还没写
SELECT * FROM
(
SELECT TOP 100 * FROM
(
SELECT TOP 100000 * FROM pagetest ORDER BY regt ASC
) as a
ORDER BY regt desc
) as b
ORDER BY regt ASC
测试了一下,大约用时间29秒。
比较:
第一种方法的效率很低,猜测是因为多次需要循环比较,时间复杂度要高一个等级。比如,这种方法的响应时间和所取得的页号有很大关系。
第二种方法还是可以接受的,和页号无关,但是也需要两次比较
网上还有使用比较ID的方法的,但是不是所有的表都有ID,即使有,也不一定是int类型的。
还有用存储过程创建临时表的,我还没有测试效率如何
sqlserver里面没有rownum 这个功能(最新的2005beta2版本据说有了,Oracle里面有),所以一次比较就能分页的算法还真不好写
我以前也是这样做的,不过后来仔细想了想,心理还不是很塌实。
比如,如果排序字段有几个,Student(Id,ClassId,Name,Sex)
按 ClassId asc, Name desc排序
如果学生的ClassId,Name相同的话,是否能真的,排得正确的结果。。
一直想证明一下,不过不知道如何做,哪位告诉一下!
Id, ClassId, Name,Sex
0 ,1,Tom,F
1 ,1,Tom,M
2 ,1,Tom,M
3 ,1,Tom,F
4 ,2,Mike,F
不知道各为考虑够过没?
to 风一样的狂徒11
利用id的那种方法太有局限性,不是所有的表都有数字的id的
使用rownum的方法也是限于Oracle,sqlserver里面就没有。
最理想的当然是通用性比较好,使用比较常用的sql语句,多用些技巧。
不知道有没有人可以想出这样的技巧了
我早用第二种了,不过效率没有楼主说的那麽低吧。29秒,是不是看错了。
#region 分页
/// <summary>
/// 转换参数
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
private SqlParameter[] CollectionToArray(SqlParameter[] param)
{
if(param == null) return null;
SqlParameter[] ps = new SqlParameter[param.Length];
param.CopyTo(ps,0);
return ps;
}
/// <summary>
/// 分页
/// </summary>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">条件,必填</param>
/// <returns>结果集</returns>
public DataSet PageResult(string fields,string table,string filter,string group, string sort )
{
return PageResult(1,0,"",fields,table,filter,group,sort,"");
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="sort">排序</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string sort,ArrayList param)
{
SqlParameter[] sqlparams = new SqlParameter[param.Count];
param.CopyTo(sqlparams,0);
return PageResult( pageno , pagesize , pk, fields, table
, filter,"", sort,"", sqlparams);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="sort">排序</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string sort,SqlParameter[] param)
{
return PageResult( pageno , pagesize , pk, fields, table
, filter,"", sort,"", param);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少行</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">排序 必填</param>
/// <param name="sum">统计</param>
/// <param name="param">参数</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk,string fields,string table
,string filter,string group,string sort,string sum,SqlParameter[] param)
{
//定义
string sortdesc = "";
string orderby = "";
string where ="";
string groupby = "";
string realfields = "";
DataSet data = new DataSet();
DataTable result;
int count = 0;
StringBuilder sql = new StringBuilder();
//组织语句
if(filter != "")
{
where = "where " + filter.ToString();
}
if(group != "")
{
groupby = "group by " + group;
//group = ClearPrefix(group);
}
if(sort == "")
{
if(pk !="" ||pk!="*")
{
sort = pk;
}
}
if(sort != "")
{
orderby = "order by " + sort;
sortdesc = "order by " + ReplaceSortDesc(ClearPrefix(sort));
sort = "order by " + ClearPrefix(sort);
}
realfields = ClearPrefix(fields);
//构建SQL
if(pagesize == 0)
{
sql.AppendFormat("select {0} from {1} {2} {3} {4} ",
fields,table,where,groupby,orderby);
#if DEBUG
ApplicationLog.WriteLog(sql.ToString());
#endif
result = ExcuteQuery( sql.ToString(),CollectionToArray(param),"pageresult");
count = result.Rows.Count;
}
else
{
StringBuilder sqlcount = new StringBuilder();
if(groupby != "")
{
//聚合
sqlcount.AppendFormat("select count(1) from (select {0} from {1} {2} {3} ) as t1_counttable",
group,table,where,groupby);
}
else
{
sqlcount.AppendFormat("select count(1) from {0} {1} {2} ",
table,where,groupby);
//非聚合
}
//求总记录数
object obj = ExecuteScalar(sqlcount.ToString(),CollectionToArray(param));
if(Convert.IsDBNull( obj))
{
count = 0;
}
else
{
count = Convert.ToInt32( obj);
}
if(count == 0)
{
sql.AppendFormat("select {0} from {1} {2} {3} {4} ",
fields,table,where,groupby,orderby);
}
else
{
//是否超出最后一页
int lastno = (count+pagesize-1)/pagesize;
//当前页有多少记录
int lastsize = pagesize;
if(pageno >=lastno)
{
pageno = lastno;
lastsize = count - pagesize * pageno+pagesize;
}
if(pageno <= 1)
{
sql.AppendFormat("select top {0} {1} from {2} {3} {4} {5}",
pagesize,fields,table,where,groupby,orderby);
}
else
{
sql.AppendFormat("select {0} from (",realfields);
sql.AppendFormat("select top {0} {1} from ",lastsize,realfields);
sql.AppendFormat("(select top {0} {1} from {2} {3} {4} {5}) as t1 ",
pageno*pagesize,fields,table,where,groupby,orderby);
sql.AppendFormat(" {0} ) as t2", sortdesc);
sql.AppendFormat(" {0} ", sort);
}
}
#if DEBUG
ApplicationLog.WriteLog(sql.ToString());
#endif
result = ExcuteQuery( sql.ToString(),CollectionToArray(param),"pageresult");
}
data.Tables.Add(result);
DataTable counttable = new DataTable("count");
counttable.Columns.Add(new DataColumn("countdata",typeof(int)));
DataRow row = counttable.NewRow();
///总记录数
row[0] = count;
counttable.Rows.Add(row);
data.Tables.Add(counttable);
//统计求和
if(sum != "")
{
StringBuilder sqlsum = new StringBuilder();
sqlsum.AppendFormat("select {0} from {1} {2}",sum,table,where);
#if DEBUG
ApplicationLog.WriteLog(sqlsum.ToString());
#endif
DataTable tablesum =
ExcuteQuery( sqlsum.ToString(),CollectionToArray(param),"sumresult");
data.Tables.Add(tablesum);
}
Close();
return data;
}
/// <summary>
/// 分页
/// </summary>
/// <param name="pageno">页码</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="pk">主键</param>
/// <param name="fields">字段</param>
/// <param name="table">表</param>
/// <param name="filter">条件</param>
/// <param name="group">group by</param>
/// <param name="sort">排序</param>
/// <param name="sum">统计</param>
/// <returns>结果集</returns>
public DataSet PageResult(int pageno , int pagesize ,string pk ,string fields ,string table ,
string filter,string group, string sort , string sum)
{
return PageResult(pageno,pagesize,pk,fields,table,filter,group,sort,sum,null);
}
#endregion