同步项目中需要用到百万条数据分页，测试完毕

测试存储过程如下：

 

 
/*建测试用的库和表*/
create database data_Test  --创建数据库data_Test
 GO
 use data_Test
 GO
 create table tb_TestTable   --创建表
 (
     id int identity(1,1) primary key,
     userName nvarchar(20) not null,
     userPWD nvarchar(20) not null,
     userEmail nvarchar(40) null
 )
 go

 --插入数据
 set identity_insert tb_TestTable on
 declare @count int
 set @count=1
 while @count<=2000000
 begin 
     insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn')
     set @count=@count+1
 end
 set identity_insert tb_TestTable off

 

--创建存储过程
 create procedure proc_paged_with_notin  --利用select top and select not in
 (
     @pageIndex int,  --页索引
     @pageSize int    --每页记录数
 )
 as
 begin
    set nocount on;
    declare @timediff datetime --耗时
    declare @sql nvarchar(500)
    select @timediff=Getdate()
    set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
    execute(@sql)  --因select top后不支技直接接参数，所以写成了字符串@sql
    select datediff(ms,@timediff,GetDate()) as 耗时
    set nocount off;
end

 

下面是测试在asp.net中调用：

 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class testDatagrid : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.DataGrid1.VirtualItemCount = 200000;
        if (!Page.IsPostBack)
        {
            this.DataGrid1.DataSource = ListProduct(0, 100).Tables[0];
            this.DataGrid1.DataBind();
        }

    }

 

//调用存储过程返回记录集

    public   DataSet   ListProduct(int   pageIndex,   int   pageSize)  
    {
        SqlConnection conn = new SqlConnection("Server=mfmdl;database=data_Test;uid=sa;pwd=sa");
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandType = CommandType.StoredProcedure;
        comm.CommandText = "proc_paged_with_notin";

        SqlParameter Del_Fold_Para1 = new SqlParameter("@pageIndex", SqlDbType.Int);
        Del_Fold_Para1.Value = pageIndex;
        SqlParameter Del_Fold_Para2 = new SqlParameter("@pageSize", SqlDbType.Int);
        Del_Fold_Para2.Value = pageSize;

        comm.Parameters.Add(Del_Fold_Para1);
        comm.Parameters.Add(Del_Fold_Para2);

        SqlDataAdapter adapter = new SqlDataAdapter(comm);

        DataSet ds = new DataSet();

        adapter.Fill(ds);

        return ds;
   
    }
    protected void DataGrid1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
    {
        //   设置当前的Page序号值,   如果不设置的话它是不会变得,   这样会给用户造成误解,以为所有页的数据相同。  

        DataGrid1.CurrentPageIndex = e.NewPageIndex;

        this.DataGrid1.DataSource = ListProduct(e.NewPageIndex + 1, DataGrid1.PageSize);  

        DataGrid1.DataBind();

    }
}