Access optimization has become a worry. One year later, I left the autumn garden.

 

Since last month, the QBlog database of the autumn garden has changed from access + sqlite to sql2000 + sqlite. Since then, access has left the autumn garden.

 

I still have to leave. After using Access for more than a year, I have been able to be superior to others, and I have finally left.

 

Let's briefly review the grievances between the autumn garden and Access (I have not written an article for too long and I am not used to writing long texts ):

 

Well:I still remember that Access was first used in the autumn garden because the autumn garden was sent to a virtual subdirectory of a friend's godaddy. At that time, I didn't know sqlite, so access was the best choice, and access was still good, at first, I felt that the speed was quite fast.

Advantage: it is simple and practical, and you don't have to think about it. It's okay to upload it.

 

Resentment:With the increase in the number of articles in the autumn garden, access speed, especially the paging speed, is obviously insufficient. The larger the number of articles, the more obvious the speed is. Multiple paging methods are optimized, finally, the speed went up a little bit, but this little bit won't solve the problem, and then changed to the M memory vps.

Disadvantage: it cannot handle the amount of suffering (tens of thousands or more.

 

Situation:Although the autumn garden has tried to change other databases many times, including running the autumn garden on oracle, Mysql, mssql and other databases, but because the memory is too small, it eventually returns to access, although it was once running on sqlite, but there was no improvement in the speed of sqlite, so everything went back to access and worked hard for the optimization.

Advantages: There are many optimizations in the past.

 

After each access optimization, you will always feel a sense of superiority. After a long time, you may feel deeply touched. The following will recall what is related to Access optimization:

1: optimize paging statements: when combining SQL statements, you can optimize SQL statements. This seems to be common everywhere and does not need to be divided into access statements.
 
2: Database sharding: Actually, it is a chain table. Using a chain table, the solution can solve some problems to some extent.

This database shard involves splitting a large number of segments or tables to keep a database smaller.

3: Create an index: Access also has an index, but I have set up and not set up, and I don't feel the difference (unlike other databases, the effect after setting is too obvious ).

4: It is helpful to compress the database. It takes a long time to compress the database.

 

Dizzy, summed up, only to find that access is not much optimization points, the previous optimization is to avoid contact with access, basically program optimization.

 

Qiu:Because Access itself does not have many optimization points, the program cannot block access writing or reading at all. Therefore, the database is deadlocked at a certain time, in the end, aspnet_isapi often detects deadlocks and restarts the application pool. This is the most fatal blow to servers with small memory. From then on, I hate access.

 

Disadvantage: This is terrible when access is deadlocked, because you have almost no other way to restore the normal operation of the website except to restart IIS.

 

Add another trick: Call GC. Collect (), which can release the temporary lock caused by the loss of the link reference when Access is not closed.

 

Finally, I must summarize:

 

1: access is a desktop database, or do not force it to eat multi-threaded applications.

 

2: The site has a bit of traffic, and the memory must be large enough. When buying a vsp, the memory should be at least 1 GB to open a sql2000, and more money will be spent, saving N more time.

 

3: currently, the autumn GardenQBlogRunning in sql2000 + sqlite, everything is normal.

 

4: I was writing a Text Database (CYQ. Data Data framework operation text) related articles, did not expect to write and write this article, it is awkward...