Massive database query statements

In the following article, I will take the "office automation" system as an example to explore how to implement rapid data extraction and data paging IN THE MSSQLSERVER database with 10 million pieces of data.

In the following article, I will take the "office automation" system as an example to explore how to implement rapid data extraction and data paging in the ms SQL SERVER database with 10 million pieces of data.

The following code illustrates part of the data structure of the database "Red header file" table in our instance: [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

With the preceding statement, we have created2520042525200496200220031002500502004559001000

Index, SELECT

SQL SERVERclustered indexnonclustered index

Anazazhang

67263390

2004112004101

1

SQL SERVER

IDID1GidSQL SERVERID

Idididid

Where1328

ID1000325

1

Select gid

128470128

2 fariq

Gid, fariqi, neibuyonghu, title Tgongwen
Fariqi> dateadd (day,-90, getdate ())

Time:5376354

3 fariqi

Gid, fariqi, neibuyonghu, title Tgongwen
Fariqi> dateadd (day,-90, getdate ())

Time:24232

251000ID12ID

Select @ d datetime

@ D = getdate ()

Select

Select [(datediff (MS, @ d, getdate ())

2

23 fariqi

1000fariqi5003

3

Compound index

25 fariqineibuyonghu

1 select gid, fariqi, neibuyonghu, title from Tgongwen where fariqi> '2017-5-5'

2513

2 select gid, fariqi, neibuyonghu, title from Tgongwen where fariqi> '2017-5-5' and neibuyonghu =''

2516

3 select gid, fariqi, neibuyonghu, title from Tgongwen where neibuyonghu =''

60280

12

1

25

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-9-16'

3326

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where gid <= 250000

4470

1/4

2 order

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> '2017-1-1'

6343100

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi> '2017-6-6'

317050

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-9-16'

3326

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi> '2017-1-1 'and fariqi <'2017-6-6'

3280

4

10020041150505000

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi> '2017-1-1 'order by fariqi

6390

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi <'2017-1-1 'order by fariqi

6453

SQL

SQLSQL SERVERSQLSQL SERVER

* From table1 where name = 'hangsan' and tID> 10000

:

* From table1 where tID> 10000 and name = 'hangsan'

Tid1_name = 'hangsan' tID> 10000

SQL SERVERwhere

Where

SARG

SARGAND

<>

<>

Name =

> 5000

5000 <

Name = and> 5000

SARGSQL SERVERWHERESARG

SARGSARG

1 LikeSARG

Name like % SARG

Name like %, SARG

%

2or

Name = and> 5000 SARGName = or> 5000 SARGor

3 SARG

SARGNOT! = <>! NOT EXISTSNOT INNOT LIKESARG

ABS () & lt; 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

6 exists in

Existsinnot existsnot innotSQL SERVERpubsSQL SERVERstatistics I/O

1 select 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

2 select 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

7 charindex () % LIKE

LIKE % charindex () LIKE

Select gid, title, fariqi, reader from tgongwen where charindex ('', reader)> 0 and fariqi> '2017-5-5'

7 4 7155 0 0

Select gid, title, fariqi, reader from tgongwen where reader like '%' + '%' and fariqi> '2017-5-5'

7 4 7155 0 0

8 unionor

Whereorunionor

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-9-16 'or gid> 2004

68 1 404008 283 392163

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-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 = '2017-9-16 'or fariqi = '2017-2-5'

6423 2 14726 1 7176

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-9-16'

Gid, fariqi, neibuyonghu, reader, title from Tgongwen where fariqi = '2017-2-5'

11640 8 14806 108 1144

9 select *

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

10 count (*) count ()

*

Count (*) from Tgongwen

1500

Count (gid) from Tgongwen

1483

Count (fariqi) from Tgongwen

3140

Count (title) from Tgongwen

52050

Count (*) SQL SERVERcount ()

11 order

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

12TOP

I/0

Top 10 * from (

Top 10000 gid, fariqi, title from tgongwen

Where neibuyonghu =''

By gid desc) as

By gid asc

201710i/OI/OTOPTOPSQL servertoporacle?lerownumbertop

Web: 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 <= @ PageUpperBound t. id

Nocount off

The above stored procedure is usedSQL SERVERCREATE TABLE # TempSQL SERVERADO

Nm

Publish n m

TOP m-n + 1 *
Publish
(Id
(TOP n-1 id
Publish ))

IdPublish

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

In fact, the preceding statement can be simplified:

TOP page size *
Table1
(ID
(TOP page size * Page id
Table
Id ))
ID

However, this stored procedure has a fatal drawback: it containsNOT IN

TOP page size *
Table1

(* (Top (page size * page size) * table1 id) B. id = a. id)
Id

That is, useNot existsnot in

TOP NOT IN

Not existsSQL servertoptop

TOPNOT INTOPNOT INNOT IN

Max () min () maxmin>

Select top 10 * from table1 where id> 200

Top page size *
Table1
Id>
(Id)
(Top (page number-1) * page size) id table1 id) T
)
Id

When selecting a column with no repeated values and easy to tell the size, we usually select a primary key. The following table lists1000 GIDGIDgid, 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

@ StrWhere! =
@ StrSQL = + str (@ PageSize) ++ @ strGetFields +
+ @ TblName ++ @ fldName ++ @ strTmp +
+ @ FldName ++ str (@ PageIndex-1) * @ PageSize) +
+ @ FldName ++ @ tblName ++ @ strWhere +
+ @ StrOrder ++ @ strWhere ++ @ strOrder


(@ StrSQL)

The above stored procedure is a general stored procedure, and its annotations have been written in it.

9

113

258330

13 ACCESS

1

2

12

FariqiID

MaxminID

Getdate () UNIQUE

Maxmin

0

1

2