在以下的文章中，我将以“办公自动化”系统为例，探讨如何在有着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<=250000
Tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1
@i int
@i=1
@i<=250000
',',,,,,admin,,,,,,','25')
GO
@h int
@h=1
@h<=100
@i int
@i=2002
@i<=2003
@j int
@j=0
@j<50
@k int
@k=0
@k<50
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<=9000000
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<=250000
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<'2004-6-6'
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<'2004-1-1' order by 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()<5000
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