SQL optimization
Third, universal paging display stored procedures for realizing small data volume and massive data
Transfer Csdn Author: ....
Establish a web application, paging browsing features is essential. This problem is a very common problem in database processing. The classic data paging method is: ADO record set paging method, that is, using ADO's own paging function (using a cursor) to implement paging. However, this paging method is only suitable for smaller data, because the cursor itself has a disadvantage: the cursor is stored in memory, which is very consumed. The cursor is built, and the relevant record is locked until the cursor is canceled. The cursor provides a means of scanning a row of row by line, generally uses a cursor to cross data, and perform different operations depending on the different data conditions. The cycle of the cursor (large data set) defined in multi-table and big tables is easy to enter a long wait or even crash.
More importantly, for a very large data model, the page retrieves, if the method of loading the entire data source is very wasteful in accordance with the traditional method of loading each time, it is very wasteful. Nowadays, the popular paging method is typically the data of the block area of the page size, rather than retrieving all the data, and then performing the current row.
The first way to extract data based on page size and page number is probably "Russian Storage Process". This stored procedure uses a cursor, because this method does not have a universal recognition of everyone because of the limitations of the cursor.
Later, some online stored procedures, the following stored procedures are the paging stored procedures written in conjunction with our office automation:
Create Procedure Paging1
(@PageSize Int, - Page size, such as 20 records per page
@PageIndex Int - Current page number
)
AS
Set nocount on
Begin
Declare @indextable table (id int identity (1, 1), NID INT) - Define the table variable
Declare @pagelowerbound Int - Define the base code on this page
Declare @PageUpperBound Int - Define the top code of this page
Set @pagelowerbound = (@ pageindex-1) * @ PageSize
Set @ PageUpperBound = @ PagelowerBound @ PageSize
Set rowcount @PageUpperBound
INSERT INTO @indextable (NID) Select Gid from Tgongwen Where Fariqi> Datead (day, -365, getdate ()) Order by Fariqi DESC
Select O.GID, O.MID, O. Title, O.FadanWei, O.Fariqi from Tgongwen O, @ indextable t where o.gid = T.NID
And T.ID> @PagelowerBound and T.ID <= @PageUpperBound ORDER by T.ID
end
Set nocount off
The above stored procedure uses the latest technology of SQL Server-Table variables. It should be said that this stored procedure is also a very good paging stored procedure. Of course, in this process, you can write the table variables into temporary tables: CREATE TABLE #Temp. But it is obvious that in SQL Server, the temporary table is not used in the table variable. Therefore, the author just started using this stored procedure, it feels very good, the speed is better than the original ADO. But later, I found a better way than this method.
The author saw a small short message "Removes the method of recording the records from the Nth to Mth" from the data sheet. The full text: Remove the Nth to RMB from the Publish Table: SELECT TOP M -n 1 * from Publish WHERE (Id Not in (SELECT TOP N-1 ID from Publish)
ID is keyword for the Publish table
When I saw this article, I really had a spirit of the spirit, I feel very good. Wait until later, I suddenly remembered this article when I was working on an office automation system (ASP.NET C # SQL Server). I would like to transform this statement, which may be a very good paging store. So I was looking for this article online. I didn't expect that the article has not found it yet, but I found a paging store process according to this statement. This stored procedure is also a popular paging store process. I regret it. Did not change this paragraph into stored procedures:
Create Procedure Paging2 (@sql nvarchar (4000), - SQL statement @Page Int, - Page @RecsperPage Int, - Requirements of each page @ ip varchar (255) Non-repetitive ID @Sort varchar (255) - Sort field and rules) AS
Declare @str nvarchar (4000)
Set @ Str = 'SELECT TOP' CAST (@Recsperpage As Varchar (20)) '* from (' @ SQL ') T where t.' @id 'Not in (SELECT TOP ' CAST ((@ Recsperpage * (@ Page-1)) AS VARCHAR (20)) '' @ ID 'from (' @ SQL ') T9 Order By' @ Sort ') Order by' @ Sort
Print @str
EXEC SP_EXECUTESQL @STRGO
In fact, the above statement can be simplified:
SELECT TOP page size *
From table1
WHERE (ID NOT IN
(SELECT TOP page size * page number ID
FROM table
Order by id))
ORDER BY ID
But this stored procedure has a fatal disadvantage that it contains NOT IN words. Although I can transform it as:
SELECT TOP page size *
From table1
WHERE NOT EXISTS
(Select * from (SELECT TOP) * from table1 order by id) b where b.id = a.id)
ORDER BY ID
That is, use not exists instead of Not in, but we have already talked in front, and the effectiveness of the two is actually no difference.
It's so, this method of combining with Not in in TOP is still more fast than using a cursor.
Although NOT EXISTS cannot save the efficiency of the stored procedure, the TOP keyword in SQL Server is a very wise choice. Because the ultimate goal of paging optimization is to avoid excessive record sets, and we have also mentioned the advantage of TOP in front, and the amount of data on the data can be implemented by top. In paging algorithms, there are two provets that affect our query speed: Top and Not in. TOP can improve our query speed, while NOT I will slow down our query speed, so we must improve our entire pagination algorithm, so you have to completely transform NOT IN, replace it with other methods.
We know, almost any field, we can all extract the maximum or minimum in a field via the max (field) or min, so if this field does not repeat, you can take advantage of these non-duplicated fields of MAX Or MIN is used as a watershed, which makes it a reference substance for the paging algorithm. Here, we can use the operator ">" or "<" to complete this mission, so that the query statement is in line with the SARG form. Such as:
SELECT TOP 10 * from table1 where id> 200
So the following paging scheme:
SELECT TOP page size *
From table1
WHERE ID>
(Select Max (ID) from from
(SELECT TOP ((Page-1) * Page size) ID from table1 order by id) AS T
)
ORDER BY ID
When the selection is not repeated, we often select the primary key when the size of the size is easily resolved. The following table lists the tables in an office automation system with 10 million data, in GID (GID is the primary key, but not aggregation index.) Is ranked sequence, extract GID, Fariqi, Title field, respectively 10, 100, 500, 100 million, 100,000, 250,000, 500,000 pages as an example, the speed of execution of above three paging schemes: (unit: milliseconds)
Page Code 1 Program 2 Scenario 3 1 60 30 76 10 46 16 63 100 1076 720 130 17110 470 250 10,000 24796 4500 140 100,000 38326 42283 1553 250,000 28140 128720 2330 50021686 127846 7168
From the above table, we can see that when the three stored procedures are executing the paging commands below 100 pages, they can trust, and the speed is very good. But the first solution is dropped by the speed of 1000 pages or above. The second solution is approximately the speed of the page 10,000 page begins to fall. The third program has never had a big destination, and the post-strength is still very good.
After determining the third paging scheme, we can write a stored procedure accordingly. Everyone knows the SQL Server stored procedure is compiled in advance, and its execution efficiency is higher than the execution efficiency of SQL statements from the web page. The following stored procedures not only contain paging schemes, but also determine if the data is performed according to the parameters of the page.
- Get the data of the specified page
Create Procedure Pagination3
@tblname varchar (255), - Table name
@StrGetfields varchar (1000) = '*', - the column that needs to be returned
@fldname varchar (255) = '', - Sort field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Total number of records in - Total number of records, non-0 values returned
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = "Select count (*) as total from [" @TBLNAME "] where" @ strwhere
Else
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
end
- The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0
Begin
Set @strtmp = "<(SELECT MIN"
Set @strorder = "Order by [" @fldname "] desc"
- If @ORDERTYPE is not 0, it will be designed, this sentence is very important!
end
Else
Begin
Set @strtmp = "> (SELECT MAX"
Set @strorder = "Order by [" @fldname "] ASC"
end
IF @PageIndex = 1
Begin
IF @strwhere! = ''
Set @strsql = "SELECT TOP" STR (@PageSize) "" @ strGetfields "from [" @TBLNAME "] where" @strwhere "" @strorder
Else
Set @STRSQL = "SELECT TOP" STR (@Pagesize) " @ strGetfields " from [" @tblname "] " @strorder
- If you are the first page, you will implement the above code, which will speed up the execution speed.
end
Else
Begin
- The following code gives @strsql to real execute SQL code
Set @STRSQL = "SELECT TOP" STR (@Pagesize) "" @ strGetfields "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) " @ strGetfields " from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "["
@fldname "] from [" @tblname "] where" @strwhere ""
@strorder ") as tbltmp) and" @strwhere "" @strorder
end
end
EXEC (@strsql)
Go
The above stored procedure is a universal stored procedure whose comment is written there.
In the case of big data, especially when querying the last few pages, the query time generally does not exceed 9 seconds; use other stored procedures, in practice, this stored process is very suitable for large capacity The database is queried.
The author hopes to bring a certain revelation by parsing the above stored procedures, and bring a certain efficiency to the work, and hopes to make a better real-time data paging algorithm.
Transfer Csdn Author: ....
Establish a web application, paging browsing features is essential. This problem is a very common problem in database processing. The classic data paging method is: ADO record set paging method, that is, using ADO's own paging function (using a cursor) to implement paging. However, this paging method is only suitable for smaller data, because the cursor itself has a disadvantage: the cursor is stored in memory, which is very consumed. The cursor is built, and the relevant record is locked until the cursor is canceled. The cursor provides a means of scanning a row of row by line, generally uses a cursor to cross data, and perform different operations depending on the different data conditions. The cycle of the cursor (large data set) defined in multi-table and big tables is easy to enter a long wait or even crash. More importantly, for a very large data model, the page retrieves, if the method of loading the entire data source is very wasteful in accordance with the traditional method of loading each time, it is very wasteful. Nowadays, the popular paging method is typically the data of the block area of the page size, rather than retrieving all the data, and then performing the current row.
The first way to extract data based on page size and page number is probably "Russian Storage Process". This stored procedure uses a cursor, because this method does not have a universal recognition of everyone because of the limitations of the cursor.
Later, some online stored procedures, the following stored procedures are the paging stored procedures written in conjunction with our office automation:
Create Procedure Paging1
(@PageSize Int, - Page size, such as 20 records per page
@PageIndex Int - Current page number
)
AS
Set nocount on
Begin
Declare @indextable table (id int identity (1, 1), NID INT) - Define the table variable
Declare @pagelowerbound Int - Define the base code on this page
Declare @PageUpperBound Int - Define the top code of this page
Set @pagelowerbound = (@ pageindex-1) * @ PageSize
Set @ PageUpperBound = @ PagelowerBound @ PageSize
Set rowcount @PageUpperBound
INSERT INTO @indextable (NID) Select Gid from Tgongwen Where Fariqi> Datead (day, -365, getdate ()) Order by Fariqi DESC
Select O.GID, O.MID, O. Title, O.FadanWei, O.Fariqi from Tgongwen O, @ indextable t where o.gid = T.NID
And T.ID> @PagelowerBound and T.ID <= @PageUpperBound ORDER by T.ID
end
Set nocount off
The above stored procedure uses the latest technology of SQL Server-Table variables. It should be said that this stored procedure is also a very good paging stored procedure. Of course, in this process, you can write the table variables into temporary tables: CREATE TABLE #Temp. But it is obvious that in SQL Server, the temporary table is not used in the table variable. Therefore, the author just started using this stored procedure, it feels very good, the speed is better than the original ADO. But later, I found a better way than this method.
The author saw a small short message "Remove the method of the record of the Nth to Mth in the data sheet", the whole text is as follows:
Remove the Nth to RMB from the Publish Table: SELECT TOP M-N 1 * from Publish Where (ID Not in (SELECT TOP N-1 ID from Publish) ID The keyword for the Publish table
When I saw this article, I really had a spirit of the spirit, I feel very good. Wait until later, I suddenly remembered this article when I was working on an office automation system (ASP.NET C # SQL Server). I would like to transform this statement, which may be a very good paging store. So I was looking for this article online. I didn't expect that the article has not found it yet, but I found a paging store process according to this statement. This stored procedure is also a popular paging store process. I regret it. Did not change this paragraph into stored procedures:
Create Procedure Paging2 (@sql nvarchar (4000), - SQL statement @Page Int, - Page @RecsperPage Int, - Requirements of each page @ ip varchar (255) Non-repetitive ID @Sort varchar (255) - Sort field and rules) AS
Declare @str nvarchar (4000)
Set @ Str = 'SELECT TOP' CAST (@Recsperpage As Varchar (20)) '* from (' @ SQL ') T where t.' @id 'Not in (SELECT TOP ' CAST ((@ Recsperpage * (@ Page-1)) AS VARCHAR (20)) '' @ ID 'from (' @ SQL ') T9 Order By' @ Sort ') Order by' @ Sort
Print @str
EXEC SP_EXECUTESQL @STRGO
In fact, the above statement can be simplified:
SELECT TOP page size *
From table1
WHERE (ID NOT IN
(SELECT TOP page size * page number ID
FROM table
Order by id))
ORDER BY ID
But this stored procedure has a fatal disadvantage that it contains NOT IN words. Although I can transform it as:
SELECT TOP page size *
From table1
WHERE NOT EXISTS
(Select * from (SELECT TOP) * from table1 order by id) b where b.id = a.id)
ORDER BY ID
That is, use not exists instead of Not in, but we have already talked in front, and the effectiveness of the two is actually no difference.
It's so, this method of combining with Not in in TOP is still more fast than using a cursor.
Although NOT EXISTS cannot save the efficiency of the stored procedure, the TOP keyword in SQL Server is a very wise choice. Because the ultimate goal of paging optimization is to avoid excessive record sets, and we have also mentioned the advantage of TOP in front, and the amount of data on the data can be implemented by top. In paging algorithms, there are two provets that affect our query speed: Top and Not in. TOP can improve our query speed, while NOT I will slow down our query speed, so we must improve our entire pagination algorithm, so you have to completely transform NOT IN, replace it with other methods.
We know, almost any field, we can all extract the maximum or minimum in a field via the max (field) or min, so if this field does not repeat, you can take advantage of these non-duplicated fields of MAX Or MIN is used as a watershed, which makes it a reference substance for the paging algorithm. Here, we can use the operator ">" or "<" to complete this mission, so that the query statement is in line with the SARG form. Such as:
SELECT TOP 10 * from table1 where id> 200
So the following paging scheme:
SELECT TOP page size *
From table1
WHERE ID>
(Select Max (ID) from from
(SELECT TOP ((Page-1) * Page size) ID from table1 order by id) AS T
)
ORDER BY ID
When the selection is not repeated, we often select the primary key when the size of the size is easily resolved. The following table lists the tables in an office automation system with 10 million data, in GID (GID is the primary key, but not aggregation index.) Is ranked sequence, extract GID, Fariqi, Title field, respectively 10, 100, 500, 100 million, 100,000, 250,000, 500,000 pages as an example, the speed of execution of above three paging schemes: (unit: milliseconds)
Page Code 1 Program 2 Scenario 3 1 60 30 76 10 46 16 63 100 1076 720 130 17110 470 250 10,000 24796 4500 140 100,000 38326 42283 1553 250,000 28140 128720 2330 50021686 127846 7168
From the above table, we can see that when the three stored procedures are executing the paging commands below 100 pages, they can trust, and the speed is very good. But the first solution is dropped by the speed of 1000 pages or above. The second solution is approximately the speed of the page 10,000 page begins to fall. The third program has never had a big destination, and the post-strength is still very good.
After determining the third paging scheme, we can write a stored procedure accordingly. Everyone knows the SQL Server stored procedure is compiled in advance, and its execution efficiency is higher than the execution efficiency of SQL statements from the web page. The following stored procedures not only contain paging schemes, but also determine if the data is performed according to the parameters of the page.
- Get the data of the specified page
Create Procedure Pagination3
@tblname varchar (255), - Table name
@StrGetfields varchar (1000) = '*', - the column that needs to be returned
@fldname varchar (255) = '', - Sort field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Total number of records in - Total number of records, non-0 values returned
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = "Select count (*) as total from [" @TBLNAME "] where" @ strwhere
Else
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
end
- The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0
Begin
Set @strtmp = "<(SELECT MIN"
Set @strorder = "Order by [" @fldname "] desc"
- If @ORDERTYPE is not 0, it will be designed, this sentence is very important!
end
Else
Begin
Set @strtmp = "> (SELECT MAX"
Set @strorder = "Order by [" @fldname "] ASC"
end
IF @PageIndex = 1
Begin
IF @strwhere! = ''
Set @strsql = "SELECT TOP" STR (@PageSize) "" @ strGetfields "from [" @TBLNAME "] where" @strwhere "" @strorder
Else
Set @STRSQL = "SELECT TOP" STR (@Pagesize) " @ strGetfields " from [" @tblname "] " @strorder
- If you are the first page, you will implement the above code, which will speed up the execution speed.
end
Else
Begin
- The following code gives @strsql to real execute SQL code
Set @STRSQL = "SELECT TOP" STR (@Pagesize) "" @ strGetfields "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) " @ strGetfields " from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "["
@fldname "] from [" @tblname "] where" @strwhere ""
@strorder ") as tbltmp) and" @strwhere "" @strorder
end
end
EXEC (@strsql)
Go
The above stored procedure is a universal stored procedure whose comment is written there.
In the case of big data, especially when querying the last few pages, the query time generally does not exceed 9 seconds; use other stored procedures, in practice, this stored process is very suitable for large capacity The database is queried.
The author hopes to bring a certain revelation by parsing the above stored procedures, and bring a certain efficiency to the work, and hopes to make a better real-time data paging algorithm.
