Selection of SQLite. net providers

We used SQLite today and found that the database was greatly upgraded from 3.4.x to 3.5.x. The version number has changed, and the OS layer of the API interface has changed significantly.

SQLite version 3.5.0 introduces a new OS interface layer that is incompatible with all prior versions of SQLite. in addition, a few existing interfaces have been generalized to work every SS all database connections within a process rather than just all connections within a thread. the purpose of this article is to describe the changes to 3.5.0 in detail so that users of prior versions of SQLite can judge what, if any, effort will be required to upgrade to newer versions.

1.0 overview of changes

A quick enumeration of the changes in SQLite version 3.5.0 is provide here. Subsequent sections will describe these changes in more detail.

1. The OS interface layer has been completely reworked:
   1. The uninitialized entedSqlite3_ OS _switch ()Interface has been removed.
   2. TheSqlite_enable_redef_ioCompile-time flag no longer functions. I/O procedures are now always redefinable.
   3. Three new objects are defined for specifying I/O procedures: sqlite3_vfs, sqlite3_file, and sqlite3_io_methods.
   4. Three new interfaces are used to create alternative OS interfaces: sqlite3_vfs_register (), sqlite3_vfs_unregister (), and sqlite3_vfs_find ().
   5. A new interface has been added to provided additional control over the creation of new database connections: requests (). The legacy interfaces of sqlite3_open () and sqlite3_open16 () continue to be fully supported.
2. The optional shared cache and memory management features that were introduced in version 3.3.0 can now be used into SS multiple threads within the same process. formerly, these extensions only applied to database connections operating within a single thread.
   1. The sqlite3_enable_shared_cache () interface now applies to all threads within a process, not to just the one thread in which it was run.
   2. The sqlite3_soft_heap_limit () interface now applies to all threads within a process, not to just the one thread in which it was run.
   3. The sqlite3_release_memory () interface will now attempt to reduce the memory usages within SS all database connections in all threads, not just connections in the thread where the interface is called.
   4. The sqlite3_thread_cleanup () interface has become a no-op.
3. Restrictions on the use of the same database connection by multiple threads have been dropped. It is now safe for multiple threads to use the same database connection at the same time.
4. There is now a compile-time option that allows an application to define alternative malloc ()/Free () implementations without having to modify any core SQLite code.
5. There is now a compile-time option that allows an application to define alternative mutex implementations without having to modify any core SQLite code.

Of these changes, only 1A and 2a through 2C are incompatibilities in any formal sense. but users who have previusly made custom modifications to the SQLite source (for example to add a custom OS layer for embedded hardware) might find that these changes have a larger impact. on the other hand, an important goal of these changes is to make it much easier to customize SQLite for use on different operating systems.

 

Due to changes in the storage layer, various versions of SQLite. Net have also changed:

SQLite. Net 1.0.46 is the last 3.4.x-based provider;
SQLite. Net 1.0.48 is the latest 3.5.4 code-based provider;

The new version of SQLite. Net was modified with sqlite3.4-> 3.5. The performance has greatly changed. The comparison is as follows:

1.0.46 Test Beginning test on system. Data. SQLite. sqliteconnection Success-createtable Success-full text search Success-datatype Test Success-dispose pattern test Success-keyinfo fetch Success-transaction enlistment Success-guid Test Success-inserttable Success-verifyinsert Success-coersiontest Success-parameterizedinsert Success-binaryinsert (using named parameter) Success-verifybinarydata Success-locktest Success-parameterizedinsertmissingparams Inserting using commandbuilder and dataadapter -> (10,000 rows )... -> Insert ends in 210 MS... commits in 481 MS Inserting using commandbuilder and dataadapter -> (With identity fetch) (10,000 rows )... -> Insert ends in 300 MS... commits in 201 MS Fast insert using parameters and prepared statement -> (100,000 rows) begins... -> Ends in 801 MS... commits in 441 MS User Function Iteration of 120003 records in 240 MS Raw iteration of 120003 records in 100 MS Intrinsic Function Iteration of 120003 records in 70 MS User (text) Command executed 397517 times in 1 second. Userfunction Command executed 570342 times in 1 second. Intrinsic Command executed 932032 times in 1 second. Intrin (txt) Command executed 747247 times in 1 second. Raw value Command executed 1013199 times in 1 second. Useraggregate executed 17 times in 1 second. Success-usercollation Success-droptable Tests finished.

1.0.48 Test Beginning test on system. Data. SQLite. sqliteconnection Success-createtable Success-full text search Success-datatype Test Success-dispose pattern test Success-keyinfo fetch Success-transaction enlistment Success-guid Test Success-inserttable Success-verifyinsert Success-coersiontest Success-parameterizedinsert Success-binaryinsert (using named parameter) Success-verifybinarydata Success-locktest Success-parameterizedinsertmissingparams Inserting using commandbuilder and dataadapter -> (10,000 rows )... -> Insert ends in 411 MS... commits in 100 MS Inserting using commandbuilder and dataadapter -> (With identity fetch) (10,000 rows )... -> Insert ends in 440 MS... commits in 131 MS Fast insert using parameters and prepared statement -> (100,000 rows) begins... -> Ends in 1312 MS... commits in 340 MS User Function Iteration of 120003 records in 191 MS Raw iteration of 120003 records in 130 MS Intrinsic Function Iteration of 120003 records in 140 MS User (text) Command executed 298951 times in 1 second. Userfunction Command executed 418648 times in 1 second. Intrinsic Command executed 599105 times in 1 second. Intrin (txt) Command executed 458549 times in 1 second. Raw value Command executed 655652 times in 1 second. Useraggregate executed 15 times in 1 second. Success-usercollation Success-droptable Tests finished.

According to the test, the performance of SQLite version 3.4 is much better than that of SQLite version 3.5 in many aspects. However, the improvement of the OS adaptation layer (introducing VFS objects) of SQLite 3.5 improves the Data Writing efficiency. Obviously, its commits are always less time-consuming.

Conclusion:
SQLite of version 3.4 is still more efficient when data is inserted into many applications;
SQLite of version 3.5 has made great improvements in writing data to the disk, but it obviously still needs to be improved in terms of memory data organization, and the insertion Operation takes much longer than that of version 3.4;

I feel that the SQLite version 3.4 is a very stable version. Version 3.5, it seems that it will be used again later.