在以下的文章中，我将以“办公自动化”系统为例，探讨如何在有着1000万条数据的ms sql server数据库中实现快速的数据提取和数据分页。
以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构： [dbo].[tgongwen] (
[gid] [int] identity (1, 1) ,
[title] [varchar] (80) collate chinese_prc_ci_as ,
[fariqi] [datetime] ,
[neibuyonghu] [varchar] (70) collate chinese_prc_ci_as ,
[reader] [varchar] (900) collate chinese_prc_ci_as ,
) on [primary] textimage_on [primary]
1000
@i int
@i=1
@i
tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1
@i int @i=1
@i
',',,,,,admin,,,,,,','25')
go
@h int
@h=1
@h
@i int
@i=2002
@i
@j int
@j=0
@j
@k int
@k=0
@k
tgongwen(fariqi,neibuyonghu,reader,title) (cast(@i varchar(4))++cast(@j varchar(2))++cast(@j varchar(2)),,,)
@k=@k+1
@j=@j+1
@i=@i+1
@h=@h+1
@i int
@i=1
@i
tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1000000
通过以上语句，我们创建了2520042525200496200220031002500502004559001000
index,select
sql serverclustered indexnonclustered index
anazazhang
67263390
2004112004101
1
sql server
idid1gidsql serverid
ididididid
where1328
id1000325
1
select gid
128470128
2fariq
gid,fariqi,neibuyonghu,title tgongwen
fariqi> dateadd(day,-90,getdate())用时：5376354
3fariqi
gid,fariqi,neibuyonghu,title tgongwen
fariqi> dateadd(day,-90,getdate())用时：24232
251000id12id
select @d datetime
@d=getdate()
select
select [(datediff(ms,@d,getdate())
2
23fariqi
1000fariqi5003
3
compound index
25fariqineibuyonghu
1select gid,fariqi,neibuyonghu,title from tgongwen where fariqi>'2004-5-5'
2513
2select gid,fariqi,neibuyonghu,title from tgongwen where fariqi>'2004-5-5' and neibuyonghu=''
2516
3select gid,fariqi,neibuyonghu,title from tgongwen where neibuyonghu=''
60280
12
1
25
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16'
3326
gid,fariqi,neibuyonghu,reader,title from tgongwen where gid
4470
1/4
2order by
gid,fariqi,neibuyonghu,reader,title from tgongwen order by fariqi
12936
gid,fariqi,neibuyonghu,reader,title from tgongwen order by gid
18843
order by3/1010
3
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi>'2004-1-1'
6343100
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi>'2004-6-6'
317050
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16'
3326
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi>'2004-1-1' and fariqi
3280
4
10020041150505000
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi>'2004-1-1' order by fariqi
6390
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi
6453
sql
sqlsql serversqlsql server
* from table1 where name='zhangsan' and tid > 10000
:
* from table1 where tid > 10000 and name='zhangsan'
tid10000name='zhangsan'tid>10000
sql serverwhere
where
sarg
sargand
name=
>5000
5000
name= and >5000
sargsql serverwheresarg
sargsarg
1likesarg
name like % sarg
name like % ,sarg
%
2or
name= and >5000 sargname= or >5000 sargor
3sarg
sargnot!=!not existsnot innot likesarg
abs()
name like %
where *2>5000
sql serversargsql server
where >2500/2
sql server
4in or
select * from table1 where tid in (2
select * from table1 where tid=2 or tid=3
tid
5not
6exists in
existsinnot existsnot innotsql serverpubssql serverstatistics i/o
1select title,price from titles where title_id in (select title_id from sales where qty>30)
'sales' 18 56 0 0
'titles' 1 2 0 0
2select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
'sales' 18 56 0 0
'titles' 1 2 0 0
existsin
7charindex()%like
like%charindex()like
select gid,title,fariqi,reader from tgongwen where charindex('',reader)>0 and fariqi>'2004-5-5'
7 4 7155 0 0
select gid,title,fariqi,reader from tgongwen where reader like '%' + '' + '%' and fariqi>'2004-5-5'
7 4 7155 0 0
8unionor
whereorunionor
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16' or gid>9990000
68 1 404008 283 392163
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16'
gid,fariqi,neibuyonghu,reader,title from tgongwen where gid>9990000
9 8 67489 216 7499
unionor
orunionorunionor
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'
6423 2 14726 1 7176
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-9-16'
gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi='2004-2-5'
11640 8 14806 108 1144
9select *
top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
4673
top 10000 gid,fariqi,title from tgongwen order by gid desc
1376
top 10000 gid,fariqi from tgongwen order by gid desc
80
10count(*)count()
*
count(*) from tgongwen
1500
count(gid) from tgongwen
1483
count(fariqi) from tgongwen
3140
count(title) from tgongwen
52050
count(*)count()count(*)count(*) sql servercount()
11order by
gidfariqi
top 10000 gid,fariqi,reader,title from tgongwen
196 1 289 1 1527
top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
4720 1 41956 0 1287
top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
4736 1 55350 10 775
top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
173 1 290 0 0
top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
156 1 289 0 0
order by order by
12top
i/0
top 10 * from (
top 10000 gid,fariqi,title from tgongwen
where neibuyonghu=''
by gid desc) as a
by gid asc
1000010i/oi/otoptopsql servertoporacleoraclerownumbertop
web :ado ado
pagination1
(@pagesize int,
@pageindex int
)
nocount on
@indextable (id int identity(1,1),nid int)
@pagelowerbound int
@pageupperbound int
@pagelowerbound=(@pageindex-1)*@pagesize
@pageupperbound=@pagelowerbound+@pagesize
rowcount @pageupperbound
@indextable(nid) gid tgongwen fariqi >dateadd(day,-365,getdate()) fariqi
o.gid,o.mid,o.title,o.fadanwei,o.fariqi tgongwen o,@indextable t o.gid=t.nid
t.id>@pagelowerbound t.id
nocount off以上存储过程运用了sql servercreate table #tempsql serverado
nm
publish n m
top m-n+1 *
publish
(id
( top n-1 id
publish)) id publish
asp.net+ c#sql server
pagination2
(
@sql nvarchar(4000),
@page int,
@recsperpage int,
@id varchar(255),
@sort varchar(255)
)
@str nvarchar(4000)
@str=+cast(@recsperpage varchar(20))++@sql++@id+
( top () t9 )
print @str
sp_executesql @str
其实，以上语句可以简化为：
top 页大小 *
table1
(id
( top 页大小*页数 id
表
id))
id但这个存储过程有一个致命的缺点，就是它含有not in
top 页大小 *
table1
( * ( top (页大小*页数) * table1 id) b b.id=a.id )
id即，用not existsnot in
top not in
not existssql servertoptoptop
topnot intopnot innot in
max()min()maxmin>select top 10 * from table1 where id>200
top 页大小 *
table1
id>
( (id)
( top ((页码-1)*页大小) id table1 id) t
)
id在选择即不重复值，又容易分辨大小的列时，我们通常会选择主键。下表列出了笔者用有着1000gidgidgid,fariqi,title11010050010001102550
123
1
10
100 1076
500 540 12943
1000
1 24796
10
25
50
10010001
sql serversqlwebsql
--
pagination3
@tblname varchar(255),
@strgetfields varchar(1000) = ,
@fldname varchar(255)=,
@pagesize int = 10,
@pageindex int = 1,
@docount bit = 0,
@ordertype bit = 0,
@strwhere varchar(1500) =
@strsql varchar(5000)
@strtmp varchar(110)
@strorder varchar(400)
@docount != 0
@strwhere !=
@strsql = + @tblname + +@strwhere
@strsql = + @tblname +
@ordertype != 0
@strtmp =
@strorder = + @fldname +
@strtmp =
@strorder = + @fldname +
@pageindex = 1
@strwhere !=
@strsql = + str(@pagesize) ++@strgetfields+ + @tblname + + @strwhere + + @strorder
@strsql = + str(@pagesize) ++@strgetfields+ + @tblname + + @strorder
@strsql = + str(@pagesize) ++@strgetfields+
+ @tblname + + @fldname + + @strtmp + + @fldname + + str((@pageindex-1)*@pagesize) + + @fldname + + @tblname + + @strorder + + @strorder
@strwhere !=
@strsql = + str(@pagesize) ++@strgetfields+
+ @tblname + + @fldname + + @strtmp +
+ @fldname + + str((@pageindex-1)*@pagesize) +
+ @fldname + + @tblname + + @strwhere +
+ @strorder + + @strwhere + + @strorder
(@strsql)
上面的这个存储过程是一个通用的存储过程，其注释已写在其中了。
9
113
258330
13access
1
2
12
fariqiid
maxminid
getdate()unique
maxmin
0
1
2