SqlDataAdapter.Fill()比高效的存储过程效率还高,郁闷中??(请高手指点)
方法1: SqlDataAdapter的分页
string temp_sqlstr = "select * from testDataServer";
SqlDataAdapter adapter = new SqlDataAdapter(temp_sqlstr, pubFunction.P_strConn);
DataSet ds1 = new DataSet();
long datestart = DateTime.Now.Ticks;
Response.Write("datestart " + datestart.ToString() + "<br>");
adapter.Fill(ds1, 9990, 10, "0");
long dateEnd = DateTime.Now.Ticks;
long dIn = dateEnd - datestart;
Response.Write("dateEnd " + dateEnd.ToString());
Response.Write("<br>" + dIn.ToString() + "<br>");
Response.Write("打印数据:");
for (int j = 0; j < ds1.Tables[0].Rows.Count; j++)
{
Response.Write("<br>" + ds1.Tables[0].Rows[j][0].ToString());
}
方法2: 网上比较号称比较高效的分页过程
存储过程:
CREATE PROCEDURE litao_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键/排序 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(6100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
--print @strWhere
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
print @strWhere
if @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
print @strSQL
exec (@strSQL)
程序:
string sql = "exec litao_GetRecordByPage @tblName='testDataServer' , @fldName='testid' , @PageSize=10,@PageIndex=999, @IsReCount=0,@OrderType=0,@strWhere='1=1'";
long datestart = DateTime.Now.Ticks;
Response.Write("datestart " + datestart.ToString() + "<br>");
DataSet ds1 = SuTao.CommonTools.DbHelperSQL.Query(sql);
long dateEnd = DateTime.Now.Ticks;
long dIn = dateEnd - datestart;
Response.Write("dateEnd " + dateEnd.ToString());
Response.Write("<br>" + dIn.ToString() + "<br>");
Response.Write("打印数据:");
for (int j = 0; j < ds1.Tables[0].Rows.Count; j++)
{
Response.Write("<br>" + ds1.Tables[0].Rows[j][0].ToString());
}
以我的100万条数据为例。
SqlDataAdapter.Fill()为1秒,而后者'号称高效的过程'用了7秒之多阿?
不知道是为什么??