Create
Procedure
GetRecordfromPage @tblname
VARCHAR
(
255
),
-
Table Name
@fldname
VARCHAR
(
255
),
-
Field name
@PageSize
int
=
10
,
-
Page size
@PageIndex
int
=
1
,
-
page number
@Iscount
Bit
=
0
,
-
Return to the total number of records, non-0 values return
@ORDERTYPE
Bit
=
0
,
-
Set the sort type, descending in a non-0 value
@Strwhere
VARCHAR
(
1000
)
=
'' '
-
Query criteria (attention: don't add WHERE)
AS
Declare
@STRSQL
VARCHAR
(
6000
)
-
Primary statement
Declare
@strtmp
VARCHAR
(
100
)
-
Temporary variables
Declare
@strorder
VARCHAR
(
400
)
-
Sort type
IF
@ORDERTYPE
! =
0
Begin
set
@strtmp
=
"
<
(
SELECT
MIN
"
set
@strorder
=
"
ORDER
BY
[
" @Fldname "
]
DESC
"
end
Else
Begin
set
@strtmp
=
"
>
(
SELECT
Max
"
set
@strorder
=
"
ORDER
BY
[
" @Fldname "
]
ASC
"
end
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
"
*
From
[
" @TBLNAME "
]
WHERE
[
" @Fldname "
]
"
@strtmp
"(
[
" @Fldname "
]
)
From
(
SELECT
TOP
"
Str
(@PageIndex
-
1
)
*
@PageSize)
"
[
" @Fldname "
]
From
[
" @TBLNAME "
]
"
@strorder
")
AS
TBLTMP) "
@strorder
IF
@Strwhere
! =
'' '
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
"
*
From
[
" @TBLNAME "
]
WHERE
[
" @Fldname "]
"
@strtmp
"(
[
" @Fldname "
]
)
From
(
SELECT
TOP
"
Str
(@PageIndex
-
1
)
*
@PageSize)
"
[
" @Fldname "
]
From
[
" @TBLNAME "
]
WHERE
"
@Strwhere
""
@strorder
")
AS
TBLTMP)
and
"
@Strwhere
""
@strorder
IF
@PageIndex
=
1
Begin
set
@strtmp
=
""
IF
@Strwhere
! =
'' '
set
@strtmp
=
"
WHERE
"
@Strwhere
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
"
*
From
[
" @TBLNAME "
]
"
@strtmp
""
@strorder
end
IF
@Iscount
! =
0
set
@STRSQL
=
"
SELECT
count
(
*
)
AS
Total
From
[
" @TBLNAME "
]
"
EXEC
(@STRSQL)
Go
After the modification, it is easy to use the code:
Modify a little convenient for others to use
Create
Procedure
GetRecordfromPage @tblname
VARCHAR
(
255
),
-
Table Name
@fldname
VARCHAR
(
255
),
-
Field name
@Orderfldname
VARCHAR
(
255
),
-
Sort word name
@Statfldname
VARCHAR
(
255
),
-
Statistical field name
@PageSize
int
=
10
,
-
Page size
@PageIndex
int
=
1
,
-
page number
@Iscount
Bit
=
0
,
-
Return to the total number of records, non-0 values return
@ORDERTYPE
Bit
=
0
,
-
Set the sort type, descending in a non-0 value
@Strwhere
VARCHAR
(
1000
)
=
'' '
-
Query criteria (attention: don't add WHERE)
AS
Declare
@STRSQL
VARCHAR
(
6000
)
-
Primary statement
Declare
@strtmp
VARCHAR
(
100
)
-
Temporary variables
Declare
@strorder
VARCHAR
(
400
)
-
Sort type
IF
@ORDERTYPE
! =
0
Begin
set
@strtmp
=
"
<
(
SELECT
MIN
"
set
@strorder
=
"
ORDER
BY
[
" @Orderfldname "
]
DESC
"
end
Else
Begin
set
@strtmp
=
"
>
(
SELECT
Max
"
set
@strorder
=
"
ORDER
BY
[
" @Orderfldname "
]
ASC
"
end
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
""
@fldname
"
From
[
" @TBLNAME "
]
WHERE
[
" @Orderfldname "
]
"
@strtmp
"(
[
" @Orderfldname "
]
)
From
(
SELECT
TOP
"
Str
(@PageIndex
-
1
)
*
@PageSize)
"
[
" @Orderfldname "
]
From
[
" @TBLNAME "
]
"
@strorder
")
AS
TBLTMP) "
@strorder
IF
@Strwhere
! =
'' '
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
""
@fldname
"
From
[
" @TBLNAME "
]
WHERE
[
" @Orderfldname "
]
"
@strtmp
"(
[
" @Orderfldname "
]
)
From
(
SELECT
TOP
"
Str
(@PageIndex
-
1
)
*
@PageSize)
"
[
" @Orderfldname "
]
From
[
" @TBLNAME "
]
WHERE
"
@Strwhere
""
@strorder
")
AS
TBLTMP) and
"
@Strwhere
""
@strorder
IF
@PageIndex
=
1
Begin
set
@strtmp
=
""
IF
@Strwhere
! =
'' '
set
@strtmp
=
"
WHERE
"
@Strwhere
set
@STRSQL
=
"
SELECT
TOP
"
Str
(@PageSize)
""
@fldname
"
From
[
" @TBLNAME "
]
"
@strtmp
""
@strorder
end
IF
@Iscount
! =
0
set
@STRSQL
=
"
SELECT
count
("
@Statfldname
")
AS
Total
From
[
" @TBLNAME "
]
"
EXEC
(@STRSQL)
Go
Description:
Increase the following two parts, others copy to set it according to their needs.
@Orderfldname varchar (255), - Sort Field Name
@StatfldName VARCHAR (255), - Statistics Field Name
FLDNAME The role is transformed by sorting to a field that needs to be opened.
@fldname varchar (255), - field name
----------------------------------------------
Personal test results:
Through testing, performance does not have a good post. But this may be a machine reason, know, in SQL Server,
2 million air records about accounting for about 3G, and
Insert these 20 million records, spending on my test platform
Near 10 hours, the memory occupies from 125m to about 350m. I actually tested it. In the case of establishing an index, execute a query according to the primary key, return a record, 512M memory, 2.2G CPU, 20G single partition storage database file, the average of about 15s in the configuration of the 100M LAN Around (this is just my personal test, there is no substantive significance.).
In addition, I found that for the above-described machine, when running SQL Server, the query is executed in the table below million - (index is good, there is no bad point, or the latest index), the speed difference is not large, but reaches 8 digits The above, that is, the SQL Server performance is not very good, of course, this is a big relationship with the machine configuration. However, it is undeniable that SQL Server 2000 is not the best choice for an enterprise-level database, and similar queries are performed in Oracle, performance is better than SQL Server. The time to be executed is 4 hours, and the memory usage has not changed: 400m. Execute the same query, the time required is about 10s. The younger brother has been busy with a provident fund project. For the department database, the central database scheduling has been evaluated. The hardware of Oracle Software Sun / HP still accounts for most of the Chinese government agency server, and it is IBM, accounting for high-end and very high-end. Most business.
Microsoft hits in this regard, there is still a long way to go - individual thinks, and hardware companies cooperate to develop their support hardware is not two ways.
