SQLite introduction, study notes, Performance Testing

Who and what companies or software are using SQLite:

Nokia's Symbian, Mozilla, abbench, Google, aliwangwang, fetion, chrome, Firefox
It can be seen that the stability and performance of SQLite are not affected. For a detailed list, see http://www.sqlite.org/famous.html.

Summary:

Advantages and adaptability of SQLite:

Lightweight
Green Components
Single file
Cross-platform
High query efficiency
Extremely fast transaction insertion speed
Supports limit Paging
Suitable for scenarios with high query speed requirements and low memory usage, especially embedded operating systems, such as mobile phone operating systems and low-concurrency Web applications (99.9% of websites are low-concurrency ), native support for SQLite in the PHP environment, and system can be easily used in asp.net/.net winform. data. SQLite

Disadvantages and unsuitable occasions:

Not suitable for cases with high concurrency, such as a large number of insert and update accesses, and incomplete SQL standard support

SQLite vs access

The official website of SQLite does not show a comparison with access. I think it should be: SQLite is open-source. A single file can run not only on windows, but also on various Linux systems, in many cases, he is different from access. His advantage is sufficient to stand in a higher position than access, so he is not comparable. However, we generally compare access with SQLite, it is because the intersection of them is related to our choice.

Intersection: Web/winform in windows,

In my tests
Insert 5 or more rows at a time, each line has 20 or more characters, and SQLite uses transactions much faster than access
Insert multiple rows at a time, each line has more than 8000 characters, SQLite uses transactions faster than twice the access speed
SQLite queries are extremely fast, or even faster than SQL Server 2008 R2 10 times (because MSSQL index Columns cannot exceed 900 characters, varchar (max) cannot be indexed)
The insert speed of a single piece of data is slightly slower than that of access. When a transaction inserts a large amount of data, the insert speed is much faster than access
Access often causes database corruption. SQLite has heard that this problem is not found.
 

SQLite quick select test

For the same data, the same SQL statement:
Select * From DBO. Articles where txtcontent like '% Liuyong %'
The execution efficiency on SQLite and MSSQL is amazing. SQLite is 10 times faster than MSSQL,
And SQLite does not have a process.ProgramThe process memory is not increased, while the MSSQL process is upgraded from 1 GB to 3 GB.

Database	Number of entries	Query time
SQLite	118848	60 s
MSSQL	118848	540 s
SQLite	7428	6 s
MSSQL	7428	60 s

 

About SQLite multithreading and Asp.net concurrency test view plaincopy to clipboardprint?

2. // 1000 winform threads operate at the same time, only CPU usage is high, data is inserted normally, no lock is used
4. Threadpool. setminthreads (1000,100 0 );
6. Threadpool. setmaxthreads (1000,100 0 );
8. For(IntI = 0; I <1000; I ++)
10. {
12. Threadpool. queueuserworkitem (OBJ) =>
14. {
16. Sqliteparameter [] parms = {
18. NewSqliteparameter ("@ Txttitle","Title"+ OBJ ),
19. New Sqliteparameter ( "@ Txtcontent" , "The content can be greater than 8000" + OBJ + New String ( '=' , 8000 + 1000 )),
21. New sqliteparameter ( " @ adder " , " add by " + OBJ ),
23. New sqliteparameter ( " @ addtime " , datetime. now),
25. NewSqliteparameter ("@ Deptid", 1 ),
27. };
31. Sqlitehelper. executenonquery (sqliteconnectionstring, commandtype. Text ,@"
33. Insert into articles (txttitle, txtcontent, adder, addtime, deptid) values (@ txttitle, @ txtcontent, @ adder, @ addtime, @ deptid)
35. ", Parms );
39. }, I );
41. }

Asp.net uses Microsoft Web application stress tool to perform a stress test on 1000 threads for one minute. elmah. dll is used to record errors. No program error is reported after the test, which only occupies a CPU.

Through my own testing, SQLite should also be suitable for high concurrency and multithreading, but the official saying is not suitable. I don't know if my testing method is incorrect.

SQLite resource address:

Official SQLite homepage:
Http://www.sqlite.org/

SQLite Chinese site:
Http://www.sqlite.com.cn/

System. Data. SQLite:
Http://sqlite.phxsoftware.com/

Embedded Database for SQL Study Notes (SQLite, Firebird)
Http://www.cnblogs.com/ljzforever/archive/2010/03/09/1681453.html

SQLite GUI graphical management tool:

SQLite expert (optional database encoding, supporting native configuration of various parameters, fast software updates, one or more versions a day, after trial, found there are many imperfections ):
Http://www.sqliteexpert.com/download.html

Navicat for SQLite (powerful import and export functions, practical functions, intuitive operations, some minor defects, and update speed ):
Http://www.navicat.com/en/download/download.html

SQLite Administrator (old, but many people think it is good, the encoding support is not strong, may be garbled ):
Http://sqliteadmin.orbmu2k.de/

SQLite manager, a plug-in for managing SQLite in Firefox, is not widely used. Looks good ):
Https://addons.mozilla.org/en-US/firefox/addon/5817/

 

SQLite references:

Open-source databases: [add new content]:
Http://www.cnblogs.com/unruledboy/archive/2005/02/04/98604.html

How strong is SQLite? Stress Testing on my 2 machines:
Http://www.cnblogs.com/unruledboy/archive/2005/03/26/sqliteperformance.html

Performance Comparison of access, Firebird and SQLite
Http://www.cnblogs.com/kevin-moon/archive/2008/12/01/1344658.html
Http://www.cnblogs.com/Kevin-moon/archive/2008/11/14/1333285.html

Talking about SQLite-implementation and application:
Http://www.cnblogs.com/hustcat/archive/2010/01/27/1657821.html

SQLite database is the best choice for CMS for Small and Medium sites:
Http://www.dbanotes.net/database/sqlite_cms.html

Limitations of SQLite:
Http://dev.firnow.com/course/7_databases/ SQL /sqlServer/200838/103309.html

SQLite FAQ:
Http://dev.firnow.com/course/7_databases/ SQL /sqlServer/200838/103310.html

MySQL vs. SQLite (PostgreSQL ):
Http://obmem.com /? P = 493

★Advantages and features of SQLite Technology

SQLite is a lightweight, cross-platform relational database. Since it is known as a relational database, it is a matter of course to support things commonly used in the sql92 standard (such as views, transactions, triggers, and so on). I will not go into detail today. Today, I will talk about some special things.

◇ Lightweight
First, it has its first feature: lightweight. Presumably, the author of SQLite attaches great importance to this feature, and even its logo uses "feathers" to show its lightness.
SQLite is different from the C/S database software. It is a database engine in the process, so there is no database client or server. To use SQLite, you only need to include a dynamic library to enjoy all its functions. In addition, the size of the dynamic library is quite small, and version 3.6.27 is hundreds of kb.

◇ Green software
Another feature of SQLite is green: its core engine does not rely on third-party software and does not require "installation environment" (such as oledb ). Therefore, it can save a lot of trouble during deployment.

◇ Single file
The so-called "single file" means that all the information in the database (such as tables, views, triggers, and so on) is contained in one file. This file can be copied to other directories or other machines.

◇ Cross-platform/portability
If we only support mainstream operating systems (Windows and Linux), we will not boast much about it. In addition to mainstream operating systems, SQLite also supports many small embedded systems, such as Android, Windows Mobile, symbin, palm, and VxWorks, which can be used on iPhone, Android, and other mobile phones.

◇ Memory Database)
In this year, the memory is getting cheaper and cheaper. Many common PCs start to measure the memory in GB (servers are even more expensive ). At this time, the memory database features of SQLite become more and more useful.
The SQLite API does not distinguish whether the database currently operated is in memory or in files (transparent to the storage media ). Therefore, if you think that disk I/O may become a bottleneck, you can switch to the memory mode. During the switchoverCodeYou don't need to make a big change. You just need to load the file to the memory at the beginning and then dump the memory database back to the file at the end. In this case, the "online backup API" mentioned above comes in handy. Smart students should understand why I expect the backup function so much?

★Technical disadvantages and deficiencies

We talked about the features and advantages above. To avoid the suspicion of hand-writing soft texts with a gun, let's talk about some shortcomings of SQLite. If you want to use it in the future, consider these disadvantages.

◇ Concurrent access Lock Mechanism
SQLite's performance in concurrent (including multi-process and multi-thread) read/write has never been ideal. The database may be exclusive to write operations, leading to blocking or error of other read/write operations.

◇ Incomplete SQL standard support
On its official website, it lists the sql92 standards that are not supported. What I personally feel is that foreign key constraints are not supported.

◇ Network File System (NFS)
Sometimes you need to access the SQLite database files on other machines and place the database files in the shared network directory. At this time, you should be careful. When SQLite files are stored in NFS, concurrent read/write operations may cause problems (such as data corruption ). It is said that some NFS file locks have bugs.

★ Programming Language Interface

SQLite supports programming interfaces in many languages. This is great for people like me who like to mix multiple programming languages. Next I will give a rough introduction.

◇ C/C ++
Since SQLite is written in C, its built-in APIs are also C interfaces. Therefore, C/C ++ is the most straightforward to use. If you do not like the process-oriented c api style, you can find another c ++ packaging library. If you want to re-invent the wheel, you can package one by yourself.
◇ Java
If you want to use Java to access SQLite, you can use the JDBC driver of SQLite or a specialized SQLite packaging library. I personally suggest using the JDBC Method. If you want to change the database in the future, you don't need to change the code.
◇ Python
Pysqlite is the first choice for python to operate SQLite. It has been integrated into the python Standard Library since Python 2.5. PythonCommunityI still like SQLite.
◇. Net
If you like. net, you can access it through system. Data. SQLite.
◇ Ruby
Ruby can operate the SQLite database through SQLite-Ruby, but I have never used it.
◇ Perl
There is DBD: SQLite on CPAN, but I have never used it.

★Some non-technical reference factors

I 've talked about topics at the technical level, if you want to use SQLite in your company's commercial software project. You also need to evaluate it based on several reference factors mentioned in "how to select an open-source project.
◇ license
SQLite uses the public domain protocol.
◇ popularity of users
over the past few years, more and more people have used SQLite (which can be reflected by Google Trends ). Some large companies have begun to integrate it into products (such as Google's gears, Apple's Safari, and Adobe's air ). This shows that its robustness and stability won't be too big a problem.
◇ development activity
if you have a rough idea about the change log of SQLite, it can be seen that there will be updates in the last five years every 1-2 months. It indicates that the development activity is still very high.
from the above non-technical factors, SQLite is very reliable for commercial companies.

reference:
SQLite introduction, study notes, performance testing
http://www.yongfa365.com/item/SQLite.html