Access paging Solution

When you open the door, this article mainly answers three questions:

1. Is there a more efficient paging Method for Access?
2. Efficiency Test of existing Access with a large data volume of 0.1 million data records by PAGE
3. What is the data Carrying Capacity of Access?

I believe that many ASP sites are still using Access databases, because Access databases do not need to have special database space, so they can be called and migrated conveniently, saving costs. In addition, the professional competence requirements for website builders are relatively low. However, with the running of the website, the database volume is getting bigger and bigger, and the data volume has also increased from several hundred to tens of thousands, with more than 100,000. As a result, various application problems have emerged due to changes in the data application level. However, the paging efficiency of large data lists is a headache for many people. I casually passed keywords such as "Big Data paging efficiency" and "Access paging", Baidu and Google, respectively, and found that there are many people in this question. Many Web pages also provide different solutions. So can these methods achieve optimization efficiency and speed improvement?

Let's take a look at the following Access paging optimization solutions. Of course, if you upgrade the database directly to SQL Server, there are better methods such as stored procedures. Today, we will discuss how to optimize the paging of Access large data volumes and how much data Access can withstand.

Solution 1: pagesize and AbsolutePage attributes of the result set of ado are used for paging.
Program example: for illustration only, complete judgment of various conditions to add)

 
 

  
  
MaxPerPage = 20
  
  
Page = cint (request ("Page"))
  
  
SQL ="Select * from table where condition order by sorting condition"
  
  
SetRst = server. CreateObject ("Adodb. recordset")
  
  
Rst.OpenSQL, conn, 1, 1
  
  
Rst. pagesize = MaxPerPage
  
  
Rst. AbsolutePage = Page 'locates the record to the first entry of the corresponding Page number.
  
  
ForI = 1ToMaxPerPage loop list
  
  
Rst. movenext
  
  
If rst. eofThenExitFor
  
  
Next
 
 

This method is the most commonly used Access paging method.

Disadvantage: All records that meet the conditions must be read each time, and then the records on the corresponding page must be located. When the data volume is large, the efficiency is very low.
Similar to this, the move method of ado is used to move the cursor of the record set (page-1) * pagesize each time, and the pagination of records is realized. After testing, the efficiency is roughly the same as solution 1.

Solution 2:

1. Set an auto-increment field. The field is INDEX.

2. Because it is Access, it can only be the front page. The purpose of Self-increasing fields is to implement the paging function.

1> record the last auto-increment value on the previous page of the user, for example, M.

2> next page, which is the start value of the next page. M + 1, end value: M + 1 + 1.5 * PAGESIZE (Note: Due to the addition and deletion operations on the database, the page size should have a coefficient, you can customize a coefficient as needed.

3> the front-end cyclically retrieves the front PAGESIZE of RS, writes it to a new RS, and returns the result.

This solution uses auto-increment to split the data list of different pages. Considering the addition and deletion operations on the database, a coefficient is added, which is a last resort. This solution ensures paging efficiency, but can only be used in data tables where values of adjacent records of added and Deleted fields are similar.

Solution 3: not in method.

This solution is reprinted on many websites. It is said that the more forward paging efficiency is improved, the more obvious. I have always been skeptical, because "not in" itself is a resource-consuming algorithm. It is hard to believe that an inefficient method can improve the paging efficiency of large data volumes. Example:

 
 

  
  
SQL ="Select top 12 * from table where Id not in (select top page * pagesize Id from Table order by id desc) order by Id desc" 
 
 

For the first page, 20 entries per page

 
 

  
  
Select Top20 *FromTableWhereIdNot In(Select Top9*20 IdFromTableOrder ByIdDesc)Order ByIdDesc 
 
 

The principle is: select the top 20 records, with the condition that the id is not in the Record ID on the previous page. In this way, you can filter out the records from the previous pages, and then obtain the records on the current page in the top-efficient way.

"Top" is indeed efficient, but what about "not in?

So I used this method to test the condition: 0.1 million pieces of data. Click query... my god. No response is returned for a long time, and Ctrl + Alt + Delete ends the task. Try again. The result is the same. So I changed the test condition to 1000 data records. OK, and the result showed that the test was very smooth.

Conclusion: If you are paging large data volumes, or do not use this method, it will be dead.

Solution 4:

 
 

  
  
"Select * from (select top"& Pagesize &"* From (select top"& Page * pagesize &"* From table" Order ByIdDesc)Order ById)Order ByIdDesc"
 
 

Simply put, this method selects the current page and all records smaller than the current page, and then selects the records on the current page through the "Top" method.

This method does not have a statement with low efficiency, although at least two select examples are selected three times for sorting ). But the efficiency should be good. And the front page should be more obvious.

If you want to save efficiency, you can select only twice.

Assume that the record ID is 1-100, with 5 records per page. The page 4th is displayed, and the sorting is in descending order.

Execution sequence:

1) Select the data in the first four pages, that is, 20 data entries in-81.

2) Select the minimum of five of the 20 data records, that is, 81-85.

3) sort the five selected entries in descending order, that is, 85-81.

If you save step 3, you can only change it

Page 1: 100

Page 2: 91,92, 93,94, 95

In fact, it is also good.

It is useless, and the test result is finally taken into consideration. Under the same data condition and server configuration, I tested Access paging efficiency for the two methods and solution 4 in solution 1. the test data is as follows:

Test conditions:

> 0.1 million; pagesize = 20;

Total number of pages> 5000 pages;

By the way, we also conducted a comparison test between "select fields" and "select all fields.

From the test results, the advantages of solution 3 are obvious. The efficiency of pages 5000 is basically the same as that of the first two methods, and even has some advantages.

In addition, many users are used to the select * from table when writing select statements. This is not a good habit. The above Access paging test results show that it is still on demand, and the supply is good, what fields are needed, and select what fields. It can greatly save server resources.

Many netizens may not be able to despise Access. "Are you still using Access ?", "Do not change SQL Server ?", "How fast do you want to use Access ?". In my experience, even at the application level of 0.1 million articles. Access is usually faster than SQL Server. Because SQL Server requires additional connections, and there is an extra bandwidth connection factor, of course, the website Server and database Server are running at a very high speed and bandwidth, that's nothing to say ).

The speed advantage of SQL Server at a higher data level is obvious. After all, it is not a product with Access level.

To explore the limits of the Access database. The above paging test was conducted in the case of 0.4 million data records. The speed is indeed compromised. However, the third solution still performs well on the 10 thousand page. At this time, the database has reached more than 400 mb. Combined with several 4,500 MB Access databases that have previously been processed. I think 0.4 million pieces of data is a limit of Access databases in general applications, but not the limit. If the number is exceeded, too much work needs to be done in program optimization. It is not worth it.

1. Data communication between Access and other Office software
2. Import SQL Server data to the Access Database
3. Experience in converting Access to SQL Server databases
4. How to crack the SQL Server Error Code imported from Access files
5. The process from Access to SQL