Sql Server 中有一个上50万条记录的表,有Asp.net做一个页面对其进行相关字段的查询,界面上显示相关字段,要分页,怎么解决这个DataSet过大的问题,用ASP + ADO 自定交分页 是否会更好.
解决方案 »
- Asp.net 上传大文件时,会出错,怎么捕捉这个错误啊
- asp.net中如何获得客户端的ip,和相应信息
- 怎么用 asp .net 代码 判断 本机是否有摄像设备
- .net 怎么返回上一页.
- SQL与asp.net连接, 运行后出现"创建锁错误",WEB什么也不显示!
- 简单的e.Item.FindControl问题,请路过的人帮忙!
- 都来看看这个DataGrid的问题
- 求教,请指点!我若不装vs.net直接在dreamweaver中编写asp.net文件有什么工具可以让我象在vs.net中一样直接调用控件!感谢!!!!!
- AJAX AutoCompleteExtender 问题,急!!!
- 请问如何实现TextBox的单击事件
- 送50分->向各位高手求教:不思不得其解,为啥左连接会失效呢?先谢谢各位啦!!!
- 急问:水晶报表导出问题
asp.net +ado.net 要比 用ASP + ADO 自定交分页 更好
@a_TableList Varchar(200),--字段
@a_TableName Varchar(30), --表名
@a_SelectWhere Varchar(500),--where条件
@a_SelectOrderId Varchar(20),--主健
@a_SelectOrder Varchar(50),--排序
@a_intPageNo int,--页号
@a_intPageSize int,--每页的记录数
@RecordCount bigint OUTPUT,--总记录数
@var_pages bigint output
as
/*定义局部变量*/
declare @var_pages1 bigint
declare @intBeginID bigint
declare @intEndID bigint
declare @intRootRecordCount bigint
declare @intRowCount bigint
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on
/*求总共根贴数*/ select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount bigint OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUTselect @RecordCount = @intRootRecordCount --总记录数 if (@intRootRecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize >= @intRootRecordCount
-- print '页数不正确'
return (-1)
set @var_pages1=@RecordCount%@a_intPageSize
if @var_pages1>0
begin
set @var_pages=@RecordCount/@a_intPageSize+1
end
else
begin
set @var_pages=@RecordCount/@a_intPageSize
end
/*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID bigint OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount bigint,@SPintEndID bigint OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT
if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between ' if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder --execute sp_executesql
-- @TmpSelect,
--N'@SPintEndID bigint,@SPintBeginID bigint',
-- @SPintEndID=@intEndID,@SPintBeginID=@intBeginID
select @intEndID,@intBeginID,@TmpSelect
return(@@rowcount)
GO
根据查询条件只取当前需要的数据。
建议select语句加top(N),如果没页10条记录
每+ 或-1页就top值就加或减1页row的量,倒序排列,
然后再选出top10发回客户端。
自己添加“上下页”按钮控制页,这样每次显示的就是所要的页,传输量超小~
CREATE PROCEDURE M_Eshop_Page @SQL nVARCHAR(4000),--查询的sql语句
@Page int,--页码
@RecsPerPage int,--页的大小
@ID VARCHAR(255),--主键
@Sort VARCHAR(255)--排序字段ASDECLARE @Str nVARCHAR(4000)SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@SortEXEC sp_ExecuteSql @StrGO
设计中不可能把50记录放在一起,并读到DATASET中。
同样也不可能做五十万条记录的查询。我给大家说个例子,大家会明白一切:分檄一下全国居民知份证系统。十多亿的数据不可能放在一起或某一服务器上。
那怎么办呢?
数据分离: 分离成若干个地域服务器。根据区哉代码进行分配检索
比如 4127代表河南 412701代表郑州 这样对一个郑州居民身份证库的检索只有五百万条了,再根据生生日期细分一下库。我想很快就能检索出现。同样的方法你把50万条记录分成 苦干个大类 每个大类再分若干个小类 比如 10大类10个小类 你算算 500000/10/10 对于五千条记录的检索速度不在话下了吧!!!
(select top 500-450 id from T001 where id in
(select top 3000-450 from T001 order by id desc)
order by id asc
)
order by id desc把3000,450,500作为参数传递进去就行了
select * from #temp where ID_Num between 10 and 20或:declare @SQLStr varchar(8000)
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)或:CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
存储过程:
CREATE PROCEDURE [dbo].[]
@PageSize int,
@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
PID int,
Ptime datetime
)INSERT INTO #PageIndex
SELECT
PID=P.ID,
Ptime=P.addtime
FROM
{要查询的表名}P
WHERE
{查询子句}
ORDER BY
Ptime desc{要排序的话} Select * FROM
{要查询的表名} P (nolock),
#PageIndex PageIndex
WHERE
P.ID= PageIndex.PID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
GO
//前台采用SqlDataReader 生成一个ArrayList包含实体类,比如:
public class Users
{
string username="";
public string UserName
{
set{username=value;}
get{return username;}
}
}
//自定义服务器控件绑定数据,通过继承DataList .比如:
public class MySampleList:DataList
{
public MySampleList()
{
this.EnableViewState = false;
ApplyTemplates();
}
//这里返回一定要是Table
private Control RenderItemTemplate()
{
Table table=new Table ();
TableRow tr;
TableCell td;
tr=new TableRow();
td=new TableCell();
td.Width=Unit.Percentage(100);
Literal allcontent=new Literal();
allcontent.DataBinding+=new System.EventHandler(HandleDataBindingForAllcontent);
td.Controls.Add(allcontent);
tr.Controls .Add(td);
table.Controls.Add(tr);
return table;
}
private void HandleDataBindingForAllcontent(Object sender, EventArgs e)
{
Literal content=(Literal)sender;
DataListItem container = (DataListItem) content.NamingContainer;
//获得数据,强行转换为实体类
Users users=(Users)container.DataItem;
//绑定并显示数据
content.Text=users.UserName;
}
private void BuildItemTemplate(Control _ctrl)
{
System.Web.UI.IParserAccessor __parser = ((System.Web.UI.IParserAccessor)(_ctrl));
__parser.AddParsedSubObject(RenderItemTemplate());
}
private void ApplyTemplates()
{
if (ItemTemplate == null)
{
ExtractTemplateRows = true;
ItemTemplate = new CompiledTemplateBuilder(new BuildTemplateMethod(BuildItemTemplate));
}
}
}
//分页控制控件(略)