ado访问数据库的性能问题。如何提升一百万多万条记录的大数据量访问速度

Question

现在有个项目，其中有几张表单表记录一百多万条，字段一百个左右。单表物理大小400M左右。
我现在采用三层结构。由服务器端进行数据库查询，然后将结果分批（譬如壹千条记录一批）返回给客户端。由于查询请求的条件和请求的字段都在客户端可以定制。所以最差情况下需要把整个表的数据都返回给客户端。在这种情况，目前的速度慢得无法忍受，第一批数据就要等的时间以分钟为单位。（当然如果用select top 1000是很快的。环境VC+ado+sql server2000。
不知道各个大虾有什么好的办法解决这个问题。谢谢

Answer 1

利用存储过程在服务器端实现翻页的功能，类似的存储过程代码可以查看FAQ区。

Answer 2

给你一个用SQL存储过程分页的代码CREATE proc CP_Pages
@pageIndex int,   --页数
@pageSize int,    --页面显示的数据量
@dbFields varchar(1000)    --查询的列名
as 
declare @strSql varchar(5000)   --查询的Sql语句if @pageIndex = '1'  
  set @strSql='select top'+ ' ' + str(@pageSize) + + ' ' + @dbFields + ' from Company_Data  order by CD_ID desc'
else
  begin
   set @strSql = 'select  top'+ ' ' + str(@pageSize) +' '+ @dbFields +' '+'from Company_Data'
   set @strSql = @strSql + ' ' + 'where CD_ID<('  
   set @strSql = @strSql + 'select min(CD_ID) from ('
   set @strSql = @strSql + 'select top '+ str((@pageIndex-1)*@pageSize) + +' CD_ID from Company_Data order by CD_ID desc) as t' +') order by CD_ID desc'
  end exec(@strSql)
GO

Answer 3

客户端有数据库的所有的表以及字段的信息，数据库查询请求客户端可以任意定制，包括请求的字段，多表间关联，各个字段都可能成为查询条件。由就是说sql语句是通过解析客户端传回来的条件动态生成的。因此到目前为止我不知道怎么生成一个可以满足条件的存储过程？

Answer 4

有些思路了，我先试试。还有为什么用recordcount函数查询记录数，经常返回-1。

Answer 5

分页放在后台处理，优化存储过程中的SQL。对于“用recordcount函数查询记录数，经常返回-1。”，可能与你打开记录集的方式有关，用adOpenKeyset方式打开试试。

Answer 6

返回-1, 是游标位置不对.如果你用 adodb.Recordset 对象, 则在打开对象前, 应该设置
.CursorLocation = 3 'adUseClient
.Open "表名", iConc, 1,1

Answer 7

可以考虑使用sql的存储过程实现分页
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PageView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PageView]
GO/*--利用SQL未公开的存储过程实现分页

方法简单且效率高,已知的问题就是要多返回一个空的记录集 解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset
的方法跳过第一个记录集

此方法由J9988提供,我只是将它改成了方便调用的存储过程--邹建 2004.05(引用请保留此信息)--*//*--调用示例

declare @PageCount int
exec sp_PageView 
@sql='select * from sysobjects',
@PageCurrent=2,
@PageCount=@PageCount out
SELECT @PageCount
--*/
CREATE PROC sp_PageView   
@sql         ntext,     --要执行的sql语句
@PageCurrent int=1,     --要显示的页码
@PageSize    int=10,    --每页的大小
@PageCount   int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen 
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1 
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
GO

Answer 8

如果sql的查询条件大于8000个字符，用什么类型的参数传进存储过程比较方便？

Answer 9

汗~~看来得用text类型啊。郁闷。最怕用这种类型了

Answer 10

ntext是为了传递更长的sql, 你完全可以用varchar代替

Answer 11

ntext是为了传递更长的sql, 你完全可以用varchar代替
======================
因为我发现生成的sql语句可能超过10000个字节,varchar应该有8000个字节的限制的吧.