62,074
社区成员
发帖
与我相关
我的任务
分享求一个效率高一点的分页方法
目前的分页方法。前人留下的。感觉效率偏低。
private void FillData(SqlDataAdapter sda, DataTable dt, string strQueryString, int pageSize, int pageIndex, int rowsCount, string orderField)
{
Connect();
int startRecord = (pageIndex - 1) * pageSize;
int maxRecord = pageIndex * pageSize;
if (startRecord >= rowsCount && startRecord >= pageSize)
{
startRecord = startRecord - pageSize;
maxRecord = maxRecord - pageSize;
}
string strSQL = sda.SelectCommand.CommandText;
try
{
//对传入的strQueryString进行二次包装，利用最新的sqlserver2005的ROW_NUMBER进行分页查询
//注意，如果是其他数据库此处不需要对strQueryString进行包装；
//构造开始
StringBuilder sbStr = new StringBuilder();
sbStr.Append(" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ");
sbStr.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ");
sbStr.Append(orderField);
sbStr.Append(") as rowNum,* from (");
sbStr.Append(strQueryString);
sbStr.Append(") as t ) as tt where ");
sbStr.Append("rowNum > " + startRecord + " and ");
sbStr.Append("rowNum <= " + maxRecord);
//构造结束
if (strQueryString != null)
{
sda.SelectCommand.CommandText = sbStr.ToString();
sda.SelectCommand.CommandType = CommandType.Text;
}
sda.SelectCommand.Connection = conn;
sda.SelectCommand.CommandTimeout = 60;
sda.Fill(dt.DataSet, dt.TableName);
}
catch (Exception ex)
{
error = ex.Message;
throw ex;
}
finally
{
sda.SelectCommand.CommandText = strSQL;
DisConnect();
}
}
private void FillData(SqlDataAdapter sda, DataTable dt, string strQueryString, int pageSize, int pageIndex, int rowsCount, string orderField)
{
Connect();
int startRecord = (pageIndex - 1) * pageSize;
int maxRecord = pageIndex * pageSize;
if (startRecord >= rowsCount && startRecord >= pageSize)
{
startRecord = startRecord - pageSize;
maxRecord = maxRecord - pageSize;
}
string strSQL = sda.SelectCommand.CommandText;
try
{
//对传入的strQueryString进行二次包装，利用最新的sqlserver2005的ROW_NUMBER进行分页查询
//注意，如果是其他数据库此处不需要对strQueryString进行包装；
//构造开始
StringBuilder sbStr = new StringBuilder();
sbStr.Append(" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ");
sbStr.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ");
sbStr.Append(orderField);
sbStr.Append(") as rowNum,* from (");
sbStr.Append(strQueryString);
sbStr.Append(") as t ) as tt where ");
sbStr.Append("rowNum > " + startRecord + " and ");
sbStr.Append("rowNum <= " + maxRecord);
//构造结束
if (strQueryString != null)
{
sda.SelectCommand.CommandText = sbStr.ToString();
sda.SelectCommand.CommandType = CommandType.Text;
}
sda.SelectCommand.Connection = conn;
sda.SelectCommand.CommandTimeout = 60;
sda.Fill(dt.DataSet, dt.TableName);
}
catch (Exception ex)
{
error = ex.Message;
throw ex;
}
finally
{
sda.SelectCommand.CommandText = strSQL;
DisConnect();
}
}
...全文
请发表友善的回复…
发表回复
人傻且呆但不萌 2013-05-24
- 打赏
- 举报
1.加top
2.指定字段，不要用*
md5e 2013-05-24
- 打赏
- 举报
要加top 就效率了
public DataSet GetinfoList(string table, int pageindex, int pagesize, string orderFid, string Conditions, string[] strparam, object[] strval)
{
string query = "Select top {0} * From "
+ "(Select *,ROW_NUMBER() OVER(ORDER BY {2}) as RowNum From {4} {3}) as newTable "
+ "Where (RowNum > {1} )";
query = string.Format(query, pagesize, (pageindex - 1) * pagesize, orderFid, Conditions, table);
//string query = "Select top {0} * From {1} {2} and id not in(Select top {3} id From {1} {2} Order by {4}) Order by {4}";
//query = string.Format(query, rowcount, table, Conditions, (rowpage - 1) * rowcount + 1, orderFid);
return db.GetDataSet(query, strparam, strval);
}
francis998 2013-05-24
- 打赏
- 举报
恩。 这个不错。 bs异步调用也能做到你这个。ios不就是这个模式吗？。
by_封爱 2013-05-24
- 打赏
- 举报
ALTER PROC [dbo].[Pr_PageView]
@tbname varchar(200), --要分页显示的表名
@FieldKey varchar(200), --用于定位记录的主键(惟一键)字段,单个字段快,多个字段慢
@PrValue varchar(100)='', --主键值,用于计算出页码,耗时加倍,如果是复合主键要他们的值之和
@PageCurrent int=1 OUTPUT, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序
@Where nvarchar(4000)='', --查询条件
@PageCount int OUTPUT --总条数
AS
DECLARE @sql nvarchar(max)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--为了方便使用复合主键查询与定位,转换成: upper(col1)+upper(col2)
declare @FieldKeyFormula varchar(200)
set @FieldKeyFormula='upper('+Replace(@FieldKey,',',')+upper(')+')'
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N'order by '+ @FieldKey --设置默认的排序方式
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--PrValue为有效主键值,重新设置页码
IF ISNULL(@PrValue,'')<>''
begin
set @PrValue=upper(@PrValue);
set @sql = N'with rnTable as '+
N'('+
N'select ' + @FieldKeyFormula + ' primaryKeyId,row_number() '+
N' over('+ @FieldOrder +') rn from '+@tbname+ ' '+@Where+
N') '+
N'select @PageCurrent=rn from rnTable where primaryKeyId='''+@PrValue+'''';
exec sp_executesql @sql,N'@PageCurrent int output',@PageCurrent output
SET @PageCurrent=(@PageCurrent+@PageSize-1)/@PageSize
end
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(1)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
--SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
declare @TopN2 int
set @TopN2=0
--单个主键的,判断是否是单个主键
If Charindex(',',@FieldKey,1)=0
begin
SELECT @TopN2=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(max))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @TopN2
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(max) OUTPUT',
@TopN2,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
end
else--复合主键,数量大时慢,数据量小时快
begin
set @sql=N'with rnTable as '+
N'('+
N'select '+@FieldKey+N',row_number() over('+@FieldOrder +
N') rn from '+@tbName+N' '+@Where+N' '+
N') '+
N'select '+@FieldShow+N' from '+@tbName+N' where '+@FieldKeyFormula+N' in '+
N'(select '+@FieldKeyFormula+N' from rnTable where rn>='+cast((@TopN1-@PageSize+1) as varchar(50))+' and rn<='+cast(@TopN1 as varchar(50))+N')'+
N' '+@FieldOrder
exec (@sql)
print @sql
end
END
joyhen 2013-05-24
- 打赏
- 举报
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PageList]
(
@tblName VARCHAR(255), -- 表名
@strGetFields VARCHAR(1000) = '*', -- 需要返回的列
@fldName VARCHAR(255)='', -- 排序的字段名
@PageSize INT = 10, -- 页尺寸
@PageIndex INT = 1, -- 页码
@doCount BIT = 0, -- 返回记录总数, 非 0 值则返回
@OrderType INT = 0, -- 设置排序类型, 非 0 值则降序
@strWhere VARCHAR(1500) = '' -- 查询条件 (注意: 不要加 WHERE)
)
AS
SET NOCOUNT ON
DECLARE @strSQL VARCHAR(5000) -- 主语句
--DECLARE @strTmp VARCHAR(110) -- 临时变量
DECLARE @strOrder VARCHAR(400) -- 排序类型
IF(@doCount != 0)
BEGIN
IF @strWhere !=''
SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @tblName + '] WHERE '+@strWhere
ELSE
SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @tblName + ']'
END
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
ELSE
BEGIN
IF @fldName !=''
BEGIN
IF @OrderType = 0
BEGIN
SET @strOrder = ' ORDER BY [' + @fldName +'] ASC'
--如果@OrderType 是1，就执行降序，这句很重要
END
ELSE If @OrderType = 1
BEGIN
SET @strOrder = ' ORDER BY [' + @fldName +'] DESC'
END
ELSE --如果@OrderType 是其他，就不额外添加后缀，采用默认
BEGIN
SET @strOrder = ' ORDER BY ' + @fldName +' '
END
END
IF @strWhere != ''
SET @strSQL = 'SELECT '+@strGetFields+ ' FROM [' + @tblName + '] WHERE ' + @strWhere + ' ' + @strOrder
ELSE
SET @strSQL = 'SELECT '+@strGetFields+ ' FROM ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
END
declare @pageCount int --总页数
declare @recordCount int --总记录数
declare @SearchTime int --执行时间
EXEC Paging @strSQL,@PageIndex,@PageSize,@pageCount output,@recordCount output,@SearchTime output
select @pageCount as 'pageCount',@recordCount as 'recordCount',@SearchTime as 'SearchTime'
select @strSQL
SET NOCOUNT OFFSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Paging]
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out,--总记录数
@SearchTime int=0 out
as
declare @usetime datetime
set @usetime=getdate()
set nocount on
begin
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
set @SearchTime=datediff(ms,@usetime,getdate())
print @SearchTime
end/// <summary>
/// 通用分页存储过程
/// </summary>
/// <param name="connectionString">连接</param>
/// <param name="tblName">要显示的表或多个表的连接</param>
/// <param name="strGetFields"> 需要返回的列</param>
/// <param name="fldName">排序的字段名</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">返回记录总数, 非 0 值则返回</param>
/// <param name="OrderType">1降序 ,0 升序，其他 多字段order 条件</param>
/// <param name="strWhere">查询条件 (注意: 不要加 WHERE)</param>
/// <returns>查询当前页的数据集</returns>
public static DataSet PageList(string connectionString, string tblName, string strGetFields, string fldName
, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
SqlParameter[] parameters ={ new SqlParameter("@tblName",SqlDbType.NVarChar,255),
new SqlParameter("@strGetFields",SqlDbType.NVarChar,1000),
new SqlParameter("@fldName",SqlDbType.NVarChar,255),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@doCount",SqlDbType.Bit),
new SqlParameter("@OrderType",SqlDbType.Int),
//new SqlParameter("@strWhere",SqlDbType.NVarChar,1500)};
new SqlParameter("@strWhere",SqlDbType.VarChar,8000)};
parameters[0].Value = tblName;
parameters[1].Value = (strGetFields == null) ? "*" : strGetFields;
parameters[2].Value = (fldName == null) ? "" : fldName;
parameters[3].Value = PageSize;
parameters[4].Value = PageIndex;
parameters[5].Value = doCount;
parameters[6].Value = OrderType;
parameters[7].Value = (strWhere == null) ? "" : strWhere;
DataSet ds = RunProcedureDS(connectionString, "PageList", parameters, "PageListTable");
return ds;
}
hu_hujun 2013-05-24
- 打赏
- 举报
1.可以用存储过程啊
2.查询的时候不要用* 用*表示从系统表sysobjcts里面查询到这张表 然后再去找所有的字段 可以用select id ……from table
3.把查询出来的数据放入缓存里面
5.在页面显示的时候尽力用AJAX+JS来实现分页 别拖控件
这样就能有效的保证页面的效率了
IssacChow 2013-05-24
- 打赏
- 举报
是这样的 ...
