为什么取最后一页速度会快呢?
select 语句
SELECT TOP 6 * FROM (
SELECT TOP 6 * FROM (SELECT TOP
6 * FROM (SELECT TOP 23706 * FROM Sign_StudentInfo ORDER BY ID ) DERIVEDTBL ORDER BY ID DESC ) DERIVEDTBL ORDER BY ID ) DERIVEDTBL
select 语句
SELECT TOP 6 * FROM (
SELECT TOP 6 * FROM (SELECT TOP
6 * FROM (SELECT TOP 23706 * FROM Sign_StudentInfo ORDER BY ID ) DERIVEDTBL ORDER BY ID DESC ) DERIVEDTBL ORDER BY ID ) DERIVEDTBL
解决方案 »
- vs控制台应用程序能不能单独运行?
- 想用c#开发个winform的ping程序,给点资料
- 怎么得到程序进程信息
- 大家帮忙看看应该用什么技术来开发! (对我有帮助有启发的, 我会另外发帖多给分的, 决不食言!人格担保!)
- 哪儿有免费的.net域名空间啊?(提供有意义的一定给分)
- 请问自定义控件中的属性页的Item中的名称可不可以自定?
- 想找C#进行嵌入式开发的资料(WINCE)
- Dotnet 中用Sybase ASA7.03数据库的Null值问题
- 辭職去成都考研﹐散分.
- “System.Net.Mail.SmtpFailedRecipientsException”类型的未经处理的异常在 System.dll 中发生
- 各位大哥。c#里关闭web窗体的语句是什么?紧急!!谢谢!!
- 请教一个正则表达式的写法
CREATE procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=2, --要显示的页码
@pagesize int=10, --每页的大小
@pagecount int=0 out --总页数
as
set nocount on
declare @p1 intexec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
--select @pagecount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@currentpage=(@currentpage-1)*@pagesize+1exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
GO使用:p_splitpage 'SELECT * FROM Sign_StudentInfo ORDER BY ID ',1100,6
我测试过了.取数据一般不会超过2秒
肯定是越往后越慢了要提高检索速度
要么分表
要么先全部提出来1、放到DataSet中,程序控制翻页
2、绑定到DataGrid中,翻页你做的是分页控件,看着权宜吧
CREATE PROCEDURE PO_View_RpoMaster
,@Status varchar(50)
,@PoGroupID varchar(50)
,@RpoComID varchar(50)
,@RpoType varchar(50)
,@PAddr varchar(50)
,@ReqID varchar(50)
,@ReqUserName nvarchar(50)
,@MtlID varchar(50)
,@MtlName nvarchar(200)
,@pagecount int
,@pageindex int
,@orderbystr varchar(50)
AS
set nocount on
declare @Start int ,@End int,@Total int
set @Start=(@pageindex-1)*@pagecount+1
set @End=@Start+@pagecount-1
create table #temp (
Sid int identity,
RpoNo varchar(50),
OpenDate datetime,
ReqUserName nvarchar(50),
DeptName nvarchar(50),
RpoTypeName nvarchar(200),
Status varchar(50),
Enable varchar(50),
ConfirmDate datetime,
ConfirmUser varchar(50)
)
if @orderbystr like 'RpoNo%'
begin
set @orderbystr='A.'+@orderbystr
end
insert #temp exec ('select distinct A.RpoNo,OpenDate,ReqUserName,DeptName,RpoTypeName,Status,Enable,ConfirmDate,ConfirmUser
from RpoMaster A,RpoDetail B where A.RpoNo=B.RpoNo and A.ReqUserName like ''%'+@ReqUserName+'%'' and B.MtlNo like ''%'+@MtlID+'%'' and B.MtlName like ''%' + @MtlName +'%'' and GroupID like ''%'+@PoGroupID+'%'' and A.RpoNo like ''%'+@RpoNo+'%'' and Status like ''%'+@Status+'%'' and PAddr like ''%'+@PAddr+'%'' and DeptID like ''%'+@ReqID+'%'' and ComID='''+@RpoComID+''' order by ' +@orderbystr)
set @Total=@@Rowcount
select total=@Total ,Sid, RpoNo,OpenDate,ReqUserName,DeptName,RpoTypeName,Status,Enable,ConfirmDate,ConfirmUser into #tempT from #temp where Sid between @Start and @End
select * from #tempT
drop table #temp
drop table #tempT
GO
[limit] startRow [offset] rowCount,对分页处理很方便。