描述信息 | 内容结果 | |||
count(*)比count(字段) | 如果在开始时没有执行过count(最小字符的字段)来统计个数,count(*)不比count(字段)慢 Ge: declare @d datetime set @d=getdate() SELECT count(*) FROM [TestPaging02].[dbo].[TGongwen] select [用时(毫秒)]=datediff(ms,@d,getdate()) -----用时:21193--- | 次数 | Count(*) | Count(gid) |
1 | 743 | 720 | ||
2 | 686 | 743 | ||
3 | 753 | 686 | ||
字段提取要按照“需多少、提多少”的原则[H1] | declare @d datetime set @d=getdate() select top 10000 gid from tgongwen order by gid desc select [用时(毫秒)]=datediff(ms,@d,getdate()) | 3000 | ||
select top 10000 gid,fariqi,titlefrom tgongwen order by gid desc | 7436 | |||
select top 10000 gid,fariqi,title,readerfrom tgongwen order by gid desc | 17180 | |||
order by按聚集索引列排序效率最高 | select top 10000 gid,fariqi,reader,title from tgongwen(没有order by操作) | 533 | ||
select top 10000 gid,fariqi,reader,title from tgongwen order by gidasc | 363 | |||
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc 不是索引fariqi列 | 60483 | |||
符合扫描参数(SARG)的优化格式:列名 操作符 <常数 或 变量> 或<常数 或 变量> 操作符列名eg: 5000<价格 | select * from TGongWen where neibuyonghu='办公室' and gid > 10000 | 8473 | ||
select * from TGongWen where gid > 10000 and neibuyonghu='办公室' | 8496 | |||
select * from TGongWen where gid > 10000 and neibuyonghu like '%公室' (不符合的形式) | 14043 | |||
select * from TGongWen where gid > 10000 and neibuyonghu like '办公%' | 9336 | |||
表变量分页:
USE [TestPaging02]
GO
/****** Object: StoredProcedure [dbo].[pagination1] Script Date: 02/05/2012 15:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger:SQLQuery12.sql|15|0|C:\Users\HP\AppData\Local\Temp\~vs44BA.sql
--=============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER procedure [dbo].[pagination1]
(@pagesize int, --页面大小,如每页存储20条记录
@pageindex int --当前页码
)
as
set nocount on
begin
declare @indextable [H2] table(id int identity(1,1),nid int) --定义表变量
declare @PageLowerBound int --定义此页的底码
declare @PageUpperBound int --定义此页的顶码
set@PageLowerBound=(@pageindex-1)*@pagesize
set@PageUpperBound=@PageLowerBound+@pagesize
set rowcount[H3] @PageUpperBound
insert into @indextable(nid) select gidfrom TGongwenorder by gid[H4]
select O.gid from TGongwen O,@indextable twhere O.gid=t.nid
and t.id>@PageLowerBound andt.id<=@PageUpperBound order by t.id
end
set nocount off
-------------------------------------------------------计算时间
declare @d datetime
set @d=getdate()
exec pagination1 60,1/10/100/1000/10000/100000,
select [用时(毫秒)]=datediff(ms,@d,getdate())
Pagesize页面大小 | 当前页码 | 用时(毫秒) |
60 | 100 | 63 |
1000 | 170 | |
10000 | 2503 | |
100000 | 15666 | |
10 | 100000 | 2446 |
15 | 100000 | 3176 |
20 | 100000 | 3446 |
25 | 100000 | 7913/4740 |
MAX的方法
USE[TestPaging02]
GO
/******Object: StoredProcedure[dbo].[pagination2] Script Date:02/06/2012 08:24:18 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE [dbo].[pagination2]
@pagesizeint,@pageindex int
AS
declare@strSQL varchar(5000)
declare@pSize int
declare@pIndex int
BEGIN
SETNOCOUNT ON
set@pSize=@pagesize
set@pIndex=@pageindex
set@strSQL='select top '+convert(varchar,@pSize)+' gid from TGongwen '+'where gid>(select max (gid) from (select top (('+convert(varchar,@pIndex)+'-1)*'+convert(varchar,@pSize)+') gid from TGongwen order by gid) as T) order by gid'
--selecttop @pSize gid from TGongwen
--set@strSQL="select top"+str(@pagesize)+" gid from TGongwen
--whereid>(select max (id) from (select top(("+str(@pageindex)+"-1)*"+str(@pagesize)+") id fromTGongwen order by id) as T)
--order by id"
--selecttop @pagesize gid from TGongwen
--whereid>(select max (id) from (select top ((@pageindex-1)*@pagesize) id fromtable1 order by id) as T)
--orderby id
END
exec(@strSQL)
-------------------------------------------------------计算时间
declare@d datetime
set@d=getdate()
execpagination2 25,1000000
select [用时(毫秒)]=datediff(ms,@d,getdate())
Pagesize页面大小 | 当前页码 | 用时(毫秒) |
60 | 100 | |
1000 | ||
10000 | 276 | |
100000 | 2196/2133 | |
10 | 100000 | 410 |
15 | 100000 | 566 |
20 | 100000 | 840 |
25 | 100000 | 883 |
相关备注: