Introduction to SQLite, learning notes, performance testing

SQLite Introduction, Learning notes, performance testing who, which companies or software are used in SQLite:

Nokia ' s Symbian,mozilla,abobe,google, Alibaba, Fetion, Chrome,firefox
It can be seen that the stability and performance of SQLite is not a problem, detailed list see: http://www.sqlite.org/famous.html.

Online about the introduction of SQLite a Catch a lot, summed up, he has the following characteristics:

SQLite Advantages and adaptation occasions:

Lightweight
Green components
Single File
Cross-platform
Highly efficient queries
Extremely fast with transaction insertion
Support for limit paging
Suitable for high query speed, less memory, especially embedded operating system, such as various mobile phone operating systems, low concurrent web (99.9% site is low concurrency), PHP environment natively support sqlite,asp.net/.net The WinForm can be easily used System.Data.SQLite

Disadvantages and non-adaptation occasions:

Discomfort combined with high-quality occasions such as large insert,update access, SQL standard support is not fully

SQLite vs Access

SQLite official website does not compare with the description of access, I think it should be: SQLite is open source, single file, not only can run on Windows, can also run on a variety of Linux systems, and his many occasions and access is different, His advantage is enough to stand in a higher position than access, so there is no comparability, but we ordinary people take access with SQLite, because they intersect the place, related to our choice.

At the intersection are: Web/winform in the Windows system,

In my Tests,
Insert 5 rows at a time and more than 20 characters per line, SQLite uses transactions much faster than access
Insert multiple lines at once, with more than 8000 characters per line, SQLite uses transactions faster than access
SQLite queries are extremely fast, even faster than SQL Server R2 10 times (because MSSQL index columns cannot be more than 900 characters, so varchar (max) cannot be indexed)
Single data insertions are slower than access, and transactions insert large amounts of data, much faster than access when the amount of data per row is small
Access often has a bad database condition, and SQLite has heard that this is not the problem.

SQLite Speed Select test

The same data, the same SQL statement:
SELECT * FROM dbo. Articles WHERE txtcontent like '% Liu Yong fa% '
In SQLite and MSSQL execution efficiency is shocking, SQLite is 10 times times faster MSSQL,
And SQLite no process only see the program process memory is not raised, and the MSSQL process from 1G to more than 3G

Database	Article number	Query time
Sqlite	118848	60s
MSSQL	118848	540s
Sqlite	7428	6s
MSSQL	7428	60s

About SQLite multi-threaded and ASP. NET concurrency Test View plaincopy to Clipboardprint?

2. Winform 1000 threads at the same time, only high CPU consumption, data normal insertion, no lock
4. Threadpool.setminthreads (1000, 1000);
6. Threadpool.setmaxthreads (1000, 1000);
8. for (int i = 0; i <; i++)
10. {
12. ThreadPool.QueueUserWorkItem ((obj) =
14. {
16. Sqliteparameter[] Parms ={
18. New Sqliteparameter ("@txtTitle", "title" +obj),
20. New Sqliteparameter ("@txtContent", "content can be greater than 8000" +obj+new string (' = ', 8000+1000)),
22. New Sqliteparameter ("@Adder", "Add people" +obj),
24. New Sqliteparameter ("@AddTime", DateTime.Now),
26. New Sqliteparameter ("@DeptId", 1),
28. };
32. Sqlitehelper.executenonquery (sqliteconnectionstring, CommandType.Text, @ "
34. Insert into articles (Txttitle,txtcontent,adder,addtime,deptid) values (@txtTitle, @txtContent, @Adder, @AddTime, @ DeptID)
36. ", parms);
40. }, I);
42. }

ASP. NET uses Microsoft Web Application Stress tool to carry out 1000-thread stress test for 1 minutes, using Elmah.dll to log errors, the test did not find the program error, but also accounted for the CPU only

Through my test, sqlite should be considered more suitable for high concurrency, and multi-threaded, but the official said not suitable, do not know is not my test method is wrong

SQLite Resource Address:

The official homepage of SQLite:
http://www.sqlite.org/

SQLite Chinese Station:
http://www.sqlite.com.cn/

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

SQL Learning Notes Embedded database (Sqlite,firebird)
Http://www.cnblogs.com/ljzforever/archive/2010/03/09/1681453.html

SQLite GUI graphical management tool:

SQLite Expert (Optional database encoding, support native configuration of various parameters, software update speed extremely fast, one or more versions a day, after trial, found there are many imperfect places):
Http://www.sqliteexpert.com/download.html

Navicat for SQLite (import, export powerful, functional, intuitive operation, some minor defects, update speed is OK):
Http://www.navicat.com/en/download/download.html

SQLite Administrator (old, but still have a lot of people feel good, coding support is not strong, may garbled):
http://sqliteadmin.orbmu2k.de/

Firefox manages SQLite's plugin for SQLite manager (not much used.) Looks good):
https://addons.mozilla.org/en-US/firefox/addon/5817/

SQLite reference:

Open source of open source database of inductive series [new content added version]:
Http://www.cnblogs.com/unruledboy/archive/2005/02/04/98604.html

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

Performance comparisons for access and 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

Brief introduction of sqlite--realization and application:
Http://www.cnblogs.com/hustcat/archive/2010/01/27/1657821.html

SQLite database is the best choice for small and medium site CMS:
Http://www.dbanotes.net/database/sqlite_cms.html

The limitations of SQLite:
Http://dev.firnow.com/course/7_databases/sql/sqlServer/200838/103309.html

SQLite FAQs:
Http://dev.firnow.com/course/7_databases/sql/sqlServer/200838/103310.html

MySQL vs. SQLite (PostgreSQL strong scrambling):
http://obmem.com/?p=493

★sqlite Technical Advantages and characteristics

SQLite is a lightweight, cross-platform relational database. Since the so-called relational database, support SQL92 standard commonly used gadgets (such as views, transactions, triggers, etc.) is taken for granted, we do not elaborate today. Today, I would like to talk about some special things.

　　◇ Lightweight
First of all, it's a feature: lightweight. It must be the author of SQLite value this feature, even its logo is the use of "feathers" to show off its lightness.
Unlike the database software for SQLite and C/s mode, it is a database engine within the process, so there is no client and server for the database. With SQLite, you can use only one of its dynamic libraries to enjoy its full functionality. and the dynamic library size is also very small, 3.6.27 version is hundreds of K

　　◇ Green Software
Another feature of SQLite is green: Its core engine itself does not rely on third-party software, and it does not require "installation environment" (such as OLE DB, etc.). So you can save a lot of trouble when you deploy.

　　◇ Single File
The so-called "single file" is that all the information in the database (such as tables, views, triggers, etc.) is contained within a single file. This file can be copied to other directories or other machines, but also replicable.

　　◇ Cross-platform/portability
If the light supports the mainstream operating system (Windows,linux), then there is nothing to brag about. In addition to the main operating system, SQLite also supports a lot of small embedded systems, such as Android, Windows Mobile, Symbin, Palm, VxWorks, etc., that is, iphone,android and other mobile phones can be used.

　　◇ in-memory databases (In-memory database)
These days, memory is getting cheaper, and many ordinary PCs are starting to measure memory in gigabytes (not to mention the server). At this point, SQLite's memory database features are becoming more usable.
The SQLite API does not differentiate whether the current operating database is in memory or in a file (for storage media is transparent). So if you think that disk I/O can be a bottleneck, consider switching to memory mode. When switching, the operation of the code of SQLite basically do not change, as long as the beginning of the file load to memory, the end of the memory of the database dump back to the file is OK. In this case, the "online backup API" mentioned earlier comes in handy, and smart students should understand why I'm looking forward to the backup feature?

★ Technical shortcomings and deficiencies

In front of the light chat features and advantages, in order to avoid the gunman writing soft article suspicion, and then say some of the shortcomings of SQLite. Yours faithfully crossing in the future if you want to use it, these shortcomings should be weighed.

　　◇ lock mechanism for concurrent access
SQLite's performance in concurrent (including multi-process and multi-threaded) reading and writing has been suboptimal. The database may be exclusive to write operations, causing other read and write operations to be blocked or faulted.

　　◇sql Standard Support Not complete
On its official website, there are specific examples of what SQL92 standards are not supported. I personally feel rather uncomfortable is not support foreign key constraints.

　　◇ Network File System (hereinafter referred to as NFS)
Sometimes it is necessary to access the SQLite database files on other machines, and the database files will be placed on the network shared directory. You must be careful at this time. When SQLite files are placed on NFS, there may be problems (such as data corruption) in the case of concurrent read and write. The cause is said to be due to a bug in some NFS file lock implementations.

★ Programming Language Interface

SQLite supports programming interfaces in many languages. This is great for people like me who are mixed with a variety of programming languages. I'm about to introduce you.

　　◇c/c++
Since SQLite itself is written in C, its own API is also the C interface. So C + + is most straightforward to use. If you don't like the process-oriented C API style, you can also find a C + + packaging library. Students who want to reinvent the wheel can also pack their own.
◇java
If you want to access SQLite in Java, you can use SQLite's JDBC driver, or through a dedicated SQLite packaging library. I personally recommend the JDBC approach, in case you want to change the database in the future, the code will not change greatly.
◇python
Pysqlite is the first choice for Python to manipulate SQLite. Starting with Python 2.5, it has been integrated into the Python standard library. It seems that the Python community still likes SQLite.
◇.net
For students who like. NET, they can access it through System.Data.SQLite.
◇ruby
Ruby can manipulate the SQLite database through Sqlite-ruby, but I didn't use it.
◇perl
There are dbd::sqlite on the CPAN, but I didn't use them.

★ Some non-technical reference factors

It's all about technical issues, if you're thinking about using SQLite in your company's business software projects. You also need to evaluate it according to several reference factors mentioned in "How to choose Open Source Project".
◇ License Agreement (License)
SQLite uses the public domain protocol, which is the most cool one, can be assured to use the bold.
◇ User's popularity degree
In recent years, there have been more and more people using SQLite (from Google Trends). Some big companies are also starting to integrate it into their products (such as Google's gears, Apple Safari, Adobe's Air). This shows that its robustness, stability, and so on are not too much of a problem.
◇ the active degree of development
If you get a rough look at the change log on SQLite, you can see that the last 5 years have basically been updated every 1-2 months. Indicates that the development activity is still very high.
From the above-mentioned non-technical factors, sqlite for commercial companies, software projects are very reliable.

Reference:
Introduction to SQLite, learning notes, performance testing
Http://www.yongfa365.com/item/SQLite.html

Introduction to SQLite, learning notes, performance testing