The drawbacks and limitations of SQLite

1 As queries become larger and more complex, query time dwarfs network calls or transaction processing overhead,
2 At this time, some large-scale and complex databases come into play.
3 Although SQLite can also handle complex queries, it does not have a sophisticated optimizer or query planner.
4 SQLite knows how to use indexes, but it does not save detailed table statistics. If you perform a 17-way join, SQLite will also join the tables and give you results. Unlike what you expected in Oracle or PostgreSQL, SQLite does not try to determine the optimization path by calculating various alternative query plans and selecting the fastest candidate plan. .
5 Therefore, if you run complex queries on large data collections, the chances that SQLite will run as fast as databases with query planners with complex designs are very small.

SQLite is an embedded database designed for small and medium-sized applications

Limitation One:

Concurrent. SQLite's lock mechanism is coarse-grained, allowing multiple reads, but only one write at a time. A write lock locks the database exclusively during writing, and the other person cannot access the database during this time. SQLite has taken steps to minimize the time it takes to lock the locks. Typically, the locks in SQLite are kept for only a few milliseconds. But as a general rule of thumb, if your application has high write concurrency (many connections compete to write to the same database) and are time-critical, you may need additional databases. You need to actually test your application to see what performance you can get. In a simple Web application, SQLite processes more than 500 transactions per second with 100 concurrent connections. The number of records modified by the firm and the complexity involved in the query may vary. What concurrency is acceptable depends on the specific application and can only be judged by direct testing. In summary, it is true for any database: until you have actually tested it, you will know what performance the application can achieve.

Limitation II:

Internet. Although SQLite databases can be shared over a network file system, potential delays associated with such file systems can lead to performance damage. To make things worse, some of the flaws in the network file system implementation also make it easy to open and modify remote files--sqlite or other--error-prone. If the file system locks are implemented improperly, it is possible to allow two clients to modify the same database file at the same time, which inevitably results in a database error. In fact, it is not because SQLite is implemented that SQLite cannot work on a network file system. Instead, SQLite works under the underlying file system and wired protocols, but these techniques are sometimes imperfect. For example, many NFS use a defective fcntl (), which means that the lock may not work as it was conceived. Some newer versions of NFS, such as the Solaris NFSV4, work fine, and the lock mechanism that SQLite needs is fairly reliable. However, SQLite developers have neither the time nor the resources to verify that any given network file system can work without defects in all cases.

Limitation Description:

Most of the restrictions are intentionally designed-they are the result of the SQLite design. For example, supporting high write concurrency can be very complex, which makes SQLite's simplicity impossible to maintain. Similarly, as an embedded database, SQLite is intentionally not supporting the network. This is not surprising. In short, SQLite cannot do all the direct results that it can do. It begins with a modular, simple, compact, and easy-to-use embedded relational database whose underlying code is within the grasp of the programmer who uses it. In many ways, it can do things that many other databases cannot do, such as running in an embedded environment where power consumption is actually a constraint.

Features not implemented by SQLite:

1 Full trigger support.
 2 SQLite supports almost all standard trigger functions, including recursive triggers and INSTEAD OF triggers.
 3 But for all trigger types, SQLite requires FOR EACH ROW behavior when evaluating each row affected by a trigger query.
 4 ANSI SQL92 also states that FOR EACH STATEMENT is not currently supported.
 5
 6 Completely modify the table structure support.
 7 Currently only ALTER TABLE commands of RENAME TABLE and ADD COLUMN types are supported.
 8 other types of ALTER TABLE operations,
 9 For example DROP COLUMN, ALTER COLUMN and ADD CONSTRAINT have not been implemented yet.
10
11 Right outer connection and full outer connection.
12 Left outer join (LEFT OUT JOIN) has been supported, but right outer join (RIGHT OUTER JOIN) and full outer join (FULL OUTER JOIN) have not yet been implemented.
13 All right outer joins have the same left outer join semantically, and the opposite is also true.
14 By simply reversing the order of the table and modifying the connection restrictions, the left outer join can be implemented as a right outer join.
15 Full outer joins can be achieved by combining left outer joins with UNION and proper NULL filtering in the WHERE clause.
16
17 Updatable views.
18 SQLite views are made read-only,
19 You cannot execute a DELETE, INSERT, or UPDATE statement on a view.
20 But you can create a trigger that initiates a DELETE, INSERT, or UPDATE on the view,
21 Do what you need to do inside the trigger.
twenty two 
23 Window functions.
24 One of the new features of ANSI SQL99 is the window function.
25 This feature provides post-processing analysis of result sets, such as sorting, moving averages, and lead and lag calculations.
26 SQLite currently supports part of ANSI SQL92, so it does not support things like RANK (), ROW_NUMBER (), etc.
27
28 Authorization and revocation.
29 Because SQLite can read and write ordinary disk files,
30 Therefore, the only access rights that can be applied are the access rights for ordinary files of the operating system in which they are located.
31 GRANT and REVOKE commands are generally used in high-end systems.
32 There are multiple users in these systems, and different users have different levels of access to the data in the database.
In the SQLite model, the application is the primary user and has access to the entire database. Access in this model is clearly defined at the application level--specifically, applications can access database files.
34
35 In addition to those listed here, there is a dedicated page on the SQLite Wiki that lists SQL that SQLite does not support. The URL is www.sqlite.org/cvstrac/wiki?p=UnsupportedSql. 

FOREIGN KEY constraints are not supported

◇ 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.

1:sqlite can not store too many databases, its performance is best only in the case of storing a small amount of data. Don't use it as MySQL or even Oracle. It's just a 200K database.

2:sqlite3 not use fixed log file like MySQL, all use Insert, UPDATE, delete run efficiency is general, sqlite3 a transaction, need to call 4 times fsync () operation, and general large database, such as MySQL only used 2 times. Sqlite3 creates a temporary file for each transaction to record the log, which is created, updated, and deleted using 3 times Fsync ()! Why not use a fixed log file? It's hard to understand the designers ' ideas. Maybe they're focusing on "select."

1 Reference link: http://blog.csdn.net/yuzhouxiang/article/details/7373111

The drawbacks and limitations of SQLite