Android SQLite Performance Analysis

<span id="Label3"></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">As a database module for Android presets, an in-depth understanding of SQLite is necessary to find some direction of Optimization.</span></span></p></p><p><p>The performance and memory of SQLite are tested in this paper. Compared to the operating performance and memory consumption of different operations, a rough list here is a summary.</p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">1. Basic Architecture</span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">First look at the main architecture of SQLite (see "the Definitive Guide to SQLite"), you need to focus on the compiler and backend two Modules. is due to the existence of a virtual machine. So there is the value of compiled statement, because it can reduce the pre-compile time, directly to Vdbe Run. The pager of the backend end is the key data manager. The performance of real-decision data operations, as well as memory Consumption.</span></span></p></p><p><p></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Not to repeat here, the specific content or read about the introduction of SQLite Information.</span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">2. Performance</span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">This test was based on Sumsung i9103 and Google Nexus S1.</span></span></p></p><p><p>Use the Python script and the ADB instructions to operate the Android app via Intent.</p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">I. Select operation</span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">first, the peak distribution of select under different record numbers is observed, and there is an upward trend in general.</span></span></p></p><p><p>As SQLite officials say it is not suitable for storing large amounts of data, be sure to control the number of records in it.</p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">In addition, Select operation Performance also depends on the number of fields queried, while the <strong><em>@ row Cloud</em></strong> further confirms and returns the content size of the FIELD. That is, the contents of the field and how much also need to weigh.</span></span></p></p><p><p></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">ii. on Business</span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Transaction is an important technique to improve the performance of SQLite Operation. In particular, SQLite realizes the Wal. Makes transactions and select not mutually exclusive, greatly improving the performance of the Application.</span></span></p></p><p><p>See attached 2.</p></p><p class="p2"><p class="p2"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">The following is the average of three types of operations when transactions are not used:</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">After the transaction is used, the performance of each operation is greatly reduced.</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">The problem is that the time to commit the transaction is longer, and this time it is necessary to form a new peak, and also to divide it into various operations:</span></span></p></p><p class="p1" style="text-align: center"><p class="p1" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"></span></p></p><p class="p1"><p class="p1"><span style="font-size: 14px"><span style="font-family: "Microsoft YaHei"">therefore cannot too much, the transaction still must submit in Time. </span>also <span style="font-family: "Microsoft YaHei"">note that although compiled statement is good for performance, it is not as straightforward as the effect of using transactions. </span></span></p></p><p class="p1"><p class="p1"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">Iii. operating performance of different models</span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Look at the average of the select operation on the two models, you can see the i9103 on the higher Volatility. And the Nexsus S1 has remained stable:</span></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei"></span></span><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Scatter plots that allow you to observe the time distribution:</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Look at the performance of the other three operations (both false and true for both the upper and lower parts are not used and transactions are used):</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Using Wal can significantly improve performance, but it also has a side effect.</span></span></p></p><p><p>It causes data queries to be made two times and can be understood once in a DB file. Once in the Wal File. For specific reasons see << the definitive guide to<em><strong><span style="font-family: "Microsoft YaHei"; font-size: 14px">SQLite</span></strong></em>>> last Chapter. The Wal is only merged into the main database when it is full 1000Pages. This time is called checking point and can be configured. Computed by default page size:1024, which is checked when the Wal file is close to 1M.</p></p><p><p>Assuming that there are no active transactions that use these pages, they are committed.</p></p><p><p>And the size of the Wal. The effects on select are Different.</p></p><p class="p2"><p class="p2"><span style="font-size: 14px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">The following is the test data for Wal's impact on query performance spikes (i9103 test). The True,false in the figure indicates whether a Wal file Exists.</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: Microsoft YaHei; font-size: 12px"> </span> <span style="font-family: "Microsoft YaHei""> <span style="font-size: 14px">* The overall average difference is 1ms, but the peak is really worth noting. </span></span></p></p><p><p></p></p><p class="p2"><p class="p2"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">In order to avoid the Wal too Big. The ability to choose to invoke Sqlitedatabase::d isablewriteaheadlogging () is forced to merge into the main database File. This call will run asynchronously inside Sqlite. Does not significantly clog the User's Thread.</span></span></p></p><p class="p2"><p class="p2"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">* In addition, sqlitedatabase::query () is only a sqlitedatabase::rawquery () package, a string assembly PROCESS. instead, use Sqlitedatabase::rawquery directly ().</span></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">3. Memory</span></span><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">SQLite stores data in page, default one page has 1024 bytes, and then organizes it through B-tree (table uses B + Tree organization):</span></span></p></p><p class="p2" style="text-align: center"><p class="p2" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Lookaside is the memory management technology of SQLite application, which optimizes the efficiency of memory usage. The main idea is to allocate a whole block of memory first, divided into several slots. Then SQLite is then used as Needed.</span></span></p></p><p><p>This is the same idea as many small memory allocators. See attached 1.</p></p><p class="p2"><p class="p2"><span style="font-size: 14px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">To explain page Cache Overflow, the data recorded in a page cannot be placed exactly within a page, and there is an additional page space. This is overflow Page.</span></span></p></p><p class="p1" style="text-align: center"><p class="p1" style="text-align: center"><span style="font-family: Microsoft YaHei; font-size: 12px"></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei; font-size: 12px"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Android also has a Magnum dumpsys, which uses Dumpsys meminfo to see the memory information used by SQLite in a process. Such as:</span></span></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">Sql</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">heap:265 memory_used:265</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">pagecache_overflow:73 malloc_size:46</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px"></span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">DATABASES</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">Pgsz Dbsz lookaside (b) Cache Dbname</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">4 199/114/1 webviewcache.db</span></span></strong></p></p><p class="p3"><p class="p3"><strong><span style="font-family: Courier New"><span style="font-size: 12px">1/541/1 (pooled # 1) Webviewcache.db</span></span></strong></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">. The three values of the cache are Each:</span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">Page Cache hit count, number of misses, and page cache Count. The ability to find details in <strong>Sqlitedebug.java</strong> and <strong>Activitythread.java</strong> in the Android source Code.</span></span></p></p><p><p></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">. Page size, the unit of DB size is kbytes, lookaside (b) refers to how many lookaside slots are Used.</span></span></p></p><p><p></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">. Memory reclamation for heap and overflow pages,sqlitedatabase may not be as timely as the ability to invoke Sqlitedatabase::releasememory () for active release.</span></span></p></p><p class="p2"><p class="p2"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px"><br></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: "Microsoft YaHei""><span style="font-size: 14px">* Use Sqlite's pragma to get some information directly from Sqlitedatabase. of course, SQLite is not supported. will also be thrown out, see attached 4.</span></span></p></p><p><p></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei">Reprint please specify the source: http://blog.csdn.net/horkychen SQLite is a very delicate system, it is worth studying.</span></p></p><p><p></p></p><p class="p2"><p class="p2"><span style="font-family: Microsoft YaHei"><br></span></p></p><span style="font-family: Microsoft YaHei"><span style="font-family: Microsoft YaHei">References</span></span><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei"><span style="font-size: 14px">1. <span class="s2">SQLite Dynamic Memory Allocation</span></span></span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei"><span style="font-size: 14px">2. <span class="s2">write-ahead Logging</span> or <span class="s2">sqlite's wal mechanism</span></span></span></span></p></p><p class="p1"><p class="p1"><span style="font-family: Microsoft YaHei"><span style="font-size: 14px">3. The definitive guides to SQLite (the authoritative guide for sqlite, two on the Architecture and overflow page for this book.)</span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei"><span style="font-size: 14px">4. <span class="s2">PRAGMA Statement</span></span></span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei; font-size: 12px">5. Official Documentation</span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei; font-size: 12px">6. Nec's Research Report on memory operation performance on Android (there are performance differences on different file systems that describe Sqlite's mechanism for random access)</span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei; font-size: 12px">7. SQLite Optimization in Android development</span></span></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei"><span style="font-size: 12px"><br></span></span></span></p></p><p class="p4"><p class="p4"><br></p></p><p class="p4"><p class="p4"><span class="s1"><span style="font-family: Microsoft YaHei"><br></span></span></p></p><p class="p4"><p class="p4"><br></p></p> <p><p>Android SQLite Performance Analysis</p></p></span>