测试存储过程如下:
/*建测试用的库和表*/
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();
}
}