Test and analysis of large data volume in Access database

"E-Mentor Network" in the use of Access databases do not need to open a dedicated database space, call, migration is also convenient, save money. In addition to the website builders of the professional ability requirements are relatively low. But with the website running, the database volume is getting bigger, the data volume also from the initial hundreds of to the present tens of thousands of, on 100,000 or even more. So a variety of application problems arise due to changes in data application levels. And the large data volume of the list paging efficiency problem is a lot of people headache. I casually through the "Big Data volume paging efficiency", "Access paging" and other keywords Baidu a bit, found that there are many of these questions. Many Web pages also offer different solutions. So, can these methods achieve the goal of optimizing efficiency and increasing speed?
The following several access paging optimization scenarios. Today we'll talk about the access Big Data volume optimization paging method and how much data access can tolerate.

Programme one:

Paging using the properties of the pagesize,absolutepage of the result set of ADO itself

Example of the program: (only for schematic, perfect various conditions to determine the self-added)

Disadvantage: Read all records that match the criteria each time, and then set the record on the corresponding page. When the amount of data is large, the efficiency is very low.

scenario two:
1, set a self-growing field. And this is index.
2, because it is ACCESS, it can only be the foreground page. The purpose of the self-growth field is to achieve paging functionality.
1> records the last self-increment of the user's previous page, such as M.
2> on the next page, remove the start value of a page. M+1, End value: m+1+1.5*pagesize (Note: Because the database will have additions and deletions operations, it should be paged size should have a factor, you can customize a 1-large coefficient according to the situation.
3> front desk loop take Rs PAGESIZE, write to a new RS and return.

Programme III:

Not in method. This program is reproduced on many websites. It is said that the more forward the paging efficiency increases more obvious. I have always been skeptical, because "not in" is itself a resource-intensive algorithm. It is hard to believe that an inefficient approach can improve the efficiency of large data paging. Examples are as follows:

Sql= "SELECT top" from table where Id not in (select top page*pagesize ID from table order BY id DESC) ORDER BY id DESC "
If this is page 9th, 20 articles per page
Select top * from table where Id not in (select top 9*20 ID from table order BY id DESC) ORDER BY id DESC
The principle is: Select the top 20 record, the condition is that the ID is not in the previous paging record ID. In this way, the records of the previous paging are filtered out, and then the records of the page are obtained by top efficient way.
"Top" is really efficient, but "not in"?
so I tested this method directly, test conditions: 100,000 data. Click to inquire ..... MY GOD, long time unresponsive, finally ctrl+alt+delete end task. Try again, the same goes for the result. So change the test conditions, into 1000 data, OK, the results show very smooth.
Conclusion: If you are a big data paging, do not use this method, will be dead.

 

Programme IV:

"SELECT * FROM (select top" &pagesize& "* FROM (select top" &page*pagesize& "* from table" ORDER by id DESC) o Rder by ID) Order BY id DESC "

This method simply means selecting the current page and any records smaller than the current paging, and then selecting the current page's record by "Top".
This method does not appear to be inefficient, although at least select two times (the example selects three times for sorting). But the efficiency should be good. And the more forward the paging should be more obvious.
If you want to save efficiency, you can select only two times.
If the record ID is 1-100, 5 articles per page. The 4th page is now displayed, sorted in reverse order.
Execution order:
1) Select the first 4 pages of data, that is, 100-81 total 20 data
2) Select the smallest of 5, or 81-85, from the 20 data lines.
3) The selected 5 is ranked in reverse order, which becomes 85-81.
If you save the third step, you can only show that it becomes
First page: 96,97,98,99,100
Second page: 91,92,93,94,95
.
.
.
Actually, it's good.
Light says no use, finally see test result. In the same data conditions, the server configuration, respectively, the two methods in scenario one and scenario four in the access paging efficiency test, the test data is as follows
Test conditions: >10; pagesize=20; page Totals >5000 page; In the same way, a comparison test of "Select section field" and "Select all Fields" is performed.
Judging from the above test results, the advantages of scenario three are still quite obvious. The efficiency to 5000 pages is basically the same as the first two methods, and there are still some advantages.
In addition, many people are accustomed to the select * from table when writing a SELECT statement, which is not a good habit. The above access page test results show that, on demand, on-demand supply of good, what fields are required, select what field. Can greatly save the server resources.
Many netizens refer to access with contempt, "Are you still using Access?" "," not yet swapped for SQL Server? "," How fast do you want to use Access? ”。 In fact, in my experience, even at the 100,000 level of application. Access is often faster than SQL Server. Because SQL Server requires additional connectivity, and the impact of a single bandwidth connection factor (of course, both the Web server and the database server run fast and bandwidth are OK, that's fine).
The speed advantage of SQL Server at the higher data level is still obvious, after all, with access is not a level of product.
to explore the limits of an Access database. In the case of 400,000 data, the above page test is carried out. The speed is really a big discount. But the third option is good in 10,000 pages. At this point the database has reached more than 400 megabytes. Then combine several 4,500 trillion access databases that were previously processed. Think that 400,000 data is an Access database in general application of a boundary, but not the limit.
Hope this information for you to solve the problem and doubts, or there is not clear, please leave a message to tell us! Welcome everyone to advise! If you want to learn more about database Tutorials , You can visit e-mentor Web.

Test and analysis of large data volume in Access database