Installation, usage, performance optimization, and settings of sqlite in windows

Installation, usage, performance optimization, and settings of sqlite in windows Time:21:25:48 Source:Http://blog.51soump3.com /? Action = showlog & gid = 51 Author:5th Click:3940 times

Sqlite is an extension of the embedded SQL database engine SQLite (SQLite Embeddable SQL DatabaseEngine. SQLite is a small C language library (Clibrary) for implementing the embedded SQL database engine. It implements an independent, embedded, and zero-configuration SQL database engine. Features include: transaction operations are atomic, consistent, isolated, and persistent, even after system crash and power failure. Zero Configuration-no installation or management is required. The vast majority of SQL92 standards are implemented. The entire database is stored in a single file. Database files can be freely shared between machines in different byte sequences. Supports databases up to 2 TB. The size of the string and BLOB types is limited by the available memory. The complete configuration is less than kb, and some optional features are ignored less than kb. Most common operations are faster than popular Client/Server database engines. Simple and Easy-to-use API. Built-in TCL binding. It also provides binding for many other languages. Source code with good comments and code 95% with better comments. Independent: no external dependency. The source code is located in a public domain and can be used for any purpose. Programs connected with SQLite can use SQL databases, but do not need to run a separate relational database management system process (separate RDBMSprocess ). SQLite is not a client library used to connect to a large database server, but a database server suitable for desktop programs and small websites. SQLite directly reads and writes (reads and writesdirectly) database files on the hard disk.

The following is my use process:
SQLite installation Solution
SQLiteYou can download the latest version from here. Next, we will introduce the installation method of windows?sqlite-3_5_1.zip as an example.
(You can download and install the appropriate version)
After the download, decompress sqlite-3_5_1.zip to the C: \ sqlite directory to complete the installation.
The C: \ sqlite directory is constructed as follows:
C: \ sqlite
|
Certificate --sqlite3.exe
Open a CMD command window
C: \> cd sqlite
C: \ sqlite> sqlite3.exe mydb. db
SQLite version 3.5.1
Enter ". help" for instructions
Sqlite> create table user (id integer primary key, name varchar (32 ));
Sqlite>. schema
Create table user (id integer primary key, name varchar (32 ));
Sqlite>. quit
After exiting, check the C: \ sqlite directory and you will find an additional mydb. db file:
C: \ sqlite
|
Certificate --sqlite3.exe
+ -- Mydb. db

SQLite Performance Optimization
Many people use it directly, but do not notice that SQLite also has configuration parameters, so you can adjust the performance. Sometimes, the results will have a great impact.
It is mainly implemented through The pragma command.
For example, space release, Disk Synchronization, and Cache size.
Do not open. As mentioned above, Vacuum is very inefficient!
PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;
Query or set the auto-vacuum tag of the database.
Normally, when a transaction is committed to delete data from the database, the database file size does not change. Unused file pages are marked and used again in subsequent add operations. In this case, use the VACUUM command to release the deleted space.
When auto-vacuum is enabled, when a transaction is committed to delete data from the database, the database file is automatically shrunk (the vacuum command does not work in the database enabled by auto-vacuum ). The database stores some internal information to support this function, which makes the database file slightly larger than when this option is not enabled.
The auto-vacuum tag can be changed only when no table is created in the database. If you try to modify an existing table, no error is returned.
It is recommended to change to 8000
PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;
Query or modify the number of database files stored in memory by SQLite. Each page uses about 2000 KB of memory. The default cache size is. if you need to change a large number of multi-line UPDATE or DELETE commands, and you don't mind using more memory for SQLite, you can increase the cache to improve performance.
When cache_size pragma is used to change the cache size, the change is only valid for the current dialog. When the database is closed and re-opened, the cache size is restored to the default size. To change the cache size permanently, use default_cache_size pragma.
Open. Otherwise, an error occurs when searching for a Chinese string.
PRAGMA case_sensitive_like;
PRAGMA case_sensitive_like = 0 | 1;
The default behavior of the LIKE operator is to ignore latin1 characters in case. Therefore, by default, the value of 'A' LIKE 'A' is true. You can enable case_sensitive_like pragma to change this default behavior. When case_sensitive_like is enabled, 'A' LIKE 'A' is false while 'A' LIKE 'A' is still true.
Open. Easy to debug
PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;
Query or change the count-changes tag. Normally, INSERT, UPDATE, and DELETE statements do not return data. When count-changes is enabled, the preceding statement returns the number of rows inserted, modified, or deleted for a row containing an integer. The number of rows returned does not include the number of rows generated by the trigger, such as insertion, modification, or deletion.
PRAGMA page_size;
PRAGMA page_size = bytes;
Query or set the page-size value. Page-size can be set only when no database is created. The page size must be an integer multiple of 2 and greater than or equal to 512 and less than or equal to 8192. The upper limit can be changed by modifying the SQLITE_MAX_PAGE_SIZE value of the macro definition during compilation. The upper limit is 32768.
If a regular backup mechanism is available and a small amount of data is lost, use OFF.
PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
Query or change the settings of the "synchronous" tag. The first form (query) returns an integer. When synchronous is set to FULL (2), the SQLite database engine will pause in an emergency to confirm that the data has been written to the disk. This ensures that the database will not be damaged when the system crashes or the power supply goes wrong. FULLsynchronous is safe but slow. When synchronous is set to NORMAL, the SQLite database engine is paused in most emergency periods, but not as frequently as in FULL mode. In NORMAL mode, there is a small probability (but not non-existent) that a power failure will cause database damage. But in fact, in this case, it is very likely that your hard disk is no longer available, or there are other unrecoverable hardware errors. When synchronous OFF (0) is set, SQLite continues directly after passing data to the system without pausing. If the application running SQLite crashes, the data will not be damaged, but the database may be damaged if the system crashes or data is written into the database without unexpected power failure. On the other hand, some synchronous OFF operations may be 50 times faster or more.
In SQLite 2, the default value is NORMAL, and the value is changed to FULL in 3.
Use 2, memory mode.
PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)
Query or modify the settings of the "temp_store" parameter. When temp_store is set to DEFAULT (0), use the C pre-processing macro TEMP_STORE during compilation to define the location for storing temporary tables and temporary indexes. When MEMORY (2) is set, temporary tables and indexes are stored in the MEMORY. If it is set to FILE (1), it is stored in the FILE. Temp_store_directorypragma can be used to specify the directory where the file is stored. When the temp_store settings are changed, all existing temporary tables, indexes, triggers, and views will be deleted immediately.