We do our website development, and ASPs are written in language, and ASP is more loved because it is easy to develop and flexible. While writing ASP, there are also many friends for patch efficiency headaches. If it is a small application, such as records in the data table are expected to be within thousands, we can use themselves, without considering too much efficiency. However, if the application is tens of thousands or even hundreds of thousands, tens of thousands of data volume, the paging comes from the paging, because it reads all the records, the more the data is slower. So I use this opportunity to discuss this problem.
Debug environment: Microsoft Windows 2000 Advanced ServerMicrosoft SQL Server 2000 Enterpriseiis 5.0ie 6.0
Good paging, you should read the records you need to page, not all data so that the system can respond quickly. The method I used to use is: Endid = (CurrentPage - 1) * PagesizerstartId = endid - Pagesizersql = "Select * from [Table] WHERE ID in (SELECT TOP" & EndID & "ID from [Table]) And id NOT IN ( SELECT TOP "& StartID &" ID from [Table] "SET RS = Conn.execute (SQL) ........
The principle is very simple, just returns the data before endid, then uses Not in, eliminates the data above STARTID, and the rest is the data of the current page. Although this paging method saves resources more than the Asp's paging. However, we know that IN is not supported, so it has also been limited by IN; in addition, if descending order is required (our default id is the primary key, ascending), then the two subquersides need to descend the statement, the outside statement is also We all know that the ordering needs to spend a lot of time, and it is often the key to the efficiency of the entire program!
So when I design a forum, in order to adapt to the massive data, I will re-adjust the paging scheme, you can also refer to this way, design a paging suitable for you. I will briefly introduce the design of the database. There are two post data tables. A Table is used to store the topic information, that is, "landlord" post, mainly used in the discussion forum to display the list of posts, not used when browsing the post The B meter is used to store all posts, including the "landlord" post, used to browse the post. I will explain the page design of the topic list here. First we have to deal with the most influential efficiency, it is sorted. The default sorting method used by the today's forum is to arrange according to time descending order, should we write sort in the statement when writing SQL statements, this is the key to the maximum efficiency! Today's the default sorting method is arranged in descending order according to the last reply time, we can reply to the topic sequence update of the post is MAX (ID) 1, we can set this field to the primary key, descending, then someone replied, The theme is in the database 'floating', so it is also convenient to work later. And the positioning ID of the post is self-growth ID! EndID = (CURRENTPAGE - 1) * Pagesizersql Statement section is: SQL = "SELECT TOP" & PAGESIZER & "* from [Topic] Where [id] <(SELECT MIN) AS [MAXID] from (SELECT TOP" & EndID & "[ID] from [Topic])" Statement Explanation: The most inside of the subquery: select top "&" [id] from [Topic] lists the current collection statement 2: SELECT MIN (ID) AS [MAXID] from (SELECT TOP "& Endid &" [Id] from [Topic]), draw the minimum ID of the above collections, is less than the last ID of the maximum ID of the current page, then we can use ID For the forum, in fact, more is to read the first page, so we can directly select TOP Pagesizer * from .... After using this paging, it is much more than the past, solves the problem of a headache! Welcome friends with me to discuss, and explore better algorithms. May 31, 2004 9:02
