SQLite Operation Problems in Android 4.2 (II): explicitly enabling transactions can improve the operation efficiency in some cases

When data is inserted in SQLite, a default statement is a transaction, and disk operations are performed on every piece of data.

If you need to execute SQL statements frequently, you can enable transactions explicitly to improve efficiency.

Database. begintransaction (); // manually set the start transaction

// Data insertion operation cycle

Database. settransactionsuccessful (); // sets whether the transaction is successfully processed. If this parameter is not set, the system automatically rolls back and does not submit the transaction.

Database. endtransaction (); // processing completed

 

 

This article is thoroughly analyzed. We recommend that you:

In Android, the method and Efficiency Comparison of using insert to insert multiple records at the same time in SQLite Database

In Android development, we often encounter the following situation: when creating an SQLite database, we want to insert a large amount of data at the same time. So what should we do?

The following describes how to insert 20 records:

1. Insert a large amount of data into an SQL statement.
2. The most stupid way to use insert statements to input one by one
3. Use transactions

The Code is as follows:

Public class datebaseopenhelper extends sqliteopenhelper {

Public static final string dbname = "radiomap ";
Public static final int VERSION = 1;
Public DateBaseOpenHelper (Context context ){
Super (context, DBNAME, null, VERSION );
}
@ Override
Public void onCreate (SQLiteDatabase db ){
// TODO Auto-generated method stub
Db.exe cSQL ("create table radiomap (location varchar (20), ap1 int, ap2 int )");
Long a = System. currentTimeMillis ();
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) select 'X = 1, y = 1',-80,-73" +
"Union all select 'X = 2, y = 3', 80, 40 union all select 'X = 3, y = 5', 30, 20" +
"Union all select 'X = 4, y = 5', 3, 2 union all select 'X = 30, y = 50', 30, 20 union all select 'X = 3, y = 5', 40, 20"
+ "Union all select 'X = 3, y = 5', 6, 20 union all select 'X = 3, y = 5', 6, 7 union all select 'X = 3, y = 5', union all select 'X = 3, y = 5', union all select 'X = 3, y = 5', 9, 9 "+
"Union all select 'X = 3, y = 5', 3,5 union all select 'X = 3, y = 5', 7,20 union all select 'X = 3, y = 5', union all select 'X = 3, y = 5', union all select 'X = 3, y = 5', "+
"Union all select 'X = 3, y = 5', 3,6 union all select 'X = 3, y = 5', 7,union all select 'X = 3, y = 5', union all select 'X = 3, y = 5 ");
Long B = System. currentTimeMillis ();
Long c = B-;
Log. I ("LocationActivity", String. valueOf (c ));

A = System. currentTimeMillis ();
Db. beginTransaction ();
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 2,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 4, 5 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 6, 7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 2,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 4, 5 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 6, y = 7', 6, 7 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 4, y = 5', 1, 3 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 5, y = 6', 1, 5 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 6, y = 7', 1, 7 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 4, y = 5', 2,3 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 5, y = 6', 2, 5 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 6, y = 7', 2, 7 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 4, y = 5', 3,3 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 5, y = 6', 3,5 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 6, y = 7', 3,7 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 4, y = 5', 4,3 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 5, y = 7', 5, 5 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 6, y = 7', 4, 7 )");
Db.exe csql ("insert into radiomap (location, AP1, AP2) values ('X = 4, y = 5', 5, 3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 5, 5 )");
Db. setTransactionSuccessful ();
Db. endTransaction ();
B = System. currentTimeMillis ();
Log. I ("LocationActivity", String. valueOf (B-));

A = System. currentTimeMillis ();
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 7, y = 8', 7,8 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 8, y = 9', 8, 9 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 9, y = 10', 9, 10 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 2,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 4, 5 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 6, 7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 2,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 2, 5 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 2, 7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 3,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 3,5 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 3,7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 4,3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 4, 4 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 4, 7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 5, 3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 7', 6, 5 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 6, y = 7', 5, 7 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 4, y = 5', 6, 3 )");
Db.exe cSQL ("insert into radiomap (location, ap1, ap2) values ('X = 5, y = 6', 6, 5 )");
B = System. currentTimeMillis ();
Log. I ("LocationActivity", String. valueOf (B-));
}

 

The first method and:

Insert into Table Name (column name 1, column name 2)
Select value 1, value 2
Union all
Select value 1, value 2 
Union all
Select value 1, value 2

Test results and Running Efficiency of the above three methods:

The first method is 9 ms.

Transaction: 86 Ms

The third type of insert is 29 Ms.

This is because 20 pieces of data are used in this test, so the use of transactions is longer than the overhead. If there are more than 1000 rows, insert is faster than insert.

 

 

Today, a friend tested SQLite and concluded that the efficiency of SQLite was too low. It took 2 minutes to insert 1000 records in batches!
Below is the test code he sent me. My dizzy ~~~~~~
Usingsystem. Data;
Usingsystem. Data. Common;
Usingsystem. Data. SQLite;
// Create a database file
File. Delete ("test1.db3 ");
Sqliteconnection. createfile ("test1.db3 ");
Dbproviderfactory factory = sqlitefactory. instance;
Using (dbconnection conn = factory. createconnection ())
{
// Connect to the database
Conn. connectionstring = "Data Source = test1.db3 ";
Conn. open ();
// Create a data table
Stringsql = "create table [test1] ([ID] integer primary key, [s] Text collate nocase )";
Dbcommand cmd = conn. createcommand ();
Cmd. Connection = conn;
Cmd. commandtext = SQL;
Cmd. executenonquery ();
// Add Parameters
Cmd. Parameters. Add (CMD. createparameter ());
// Start timing
Stopwatch watch = newstopwatch ();
Watch. Start ();
// Insert 1000 records consecutively
For (INTI = 0; I <1000; I ++)
{
Cmd. commandtext = "insert into [test1] ([s]) values (?) ";
Cmd. Parameters [0]. value = I. tostring ();
Cmd. executenonquery ();
}
// Stop timing
Watch. Stop ();
Console. writeline (WATCH. elapsed );
}
Ah ~~~~ For a common sense error, I add a few lines of code (Add code mark "// <-------------------").
Usingsystem. Data;
Usingsystem. Data. Common;
Usingsystem. Data. SQLite;
// Create a database file
File. Delete ("test1.db3 ");
SQLiteConnection. CreateFile ("test1.db3 ");
DbProviderFactory factory = SQLiteFactory. Instance;
Using (DbConnection conn = factory. CreateConnection ())
{
// Connect to the database
Conn. ConnectionString = "Data Source = test1.db3 ";
Conn. Open ();
// Create a data table
Stringsql = "create table [test1] ([id] integer primary key, [s] text collate nocase )";
DbCommand cmd = conn. CreateCommand ();
Cmd. Connection = conn;
Cmd. CommandText = SQL;
Cmd. ExecuteNonQuery ();
// Add Parameters
Cmd. Parameters. Add (cmd. CreateParameter ());
// Start timing
Stopwatch watch = newStopwatch ();
Watch. Start ();
DbTransaction trans = conn. BeginTransaction (); // <-------------------
Try
{
// Insert 1000 records consecutively
For (inti = 0; I <1000; I ++)
{
Cmd. CommandText = "insert into [test1] ([s]) values (?) ";
Cmd. Parameters [0]. Value = I. ToString ();
Cmd. ExecuteNonQuery ();
}
Trans. Commit (); // <-------------------
}
Catch
{
Trans. Rollback (); // <-------------------
Throw; // <-------------------
}
// Stop timing
Watch. Stop ();
Console. WriteLine (watch. Elapsed );
}
The execution takes 0.2 seconds. Is the gap too big?
Why is there such a big gap between simply enabling a transaction? Simple: SQLite starts a transaction for each operation by default. Therefore, at least 1000 transactions are started for 1000 inserts of the original code, and "transaction enabling + SQL Execution + transaction disabling" naturally takes a lot of time, this is why the transaction is so fast. In fact, this is the basic knowledge of database operations. It is important to note that the poor code efficiency is not half past one.

 

 

Recently, an android project encountered a problem. During app initialization, a large number of data needs to be inserted to SQLite in batches, resulting in slow application startup.
Android uses the SQLite database, while SQLite is a lightweight database. After Google finds that the issue of SQLite transaction processing is a transaction by default when SQLite inserts data, disk operations are performed as many data records as possible. The first 5000 records of my application are 5000 read/write operations on the disk.

Solution:

Add transaction processing and insert 5000 records as a transaction

Database. begintransaction (); // manually set the start transaction

// Data insertion operation cycle

Database. settransactionsuccessful (); // sets whether the transaction is successfully processed. If this parameter is not set, the system automatically rolls back and does not submit the transaction.

Database. endtransaction (); // processing completed

 

SQLite databases are essentially files on a disk. Therefore, all database operations are actually converted to file operations, and frequent file operations will be a very good process, it will greatly affect the speed of database access.
For example, if you insert 1 million data records to a database
Sqlite3_exec (dB, "insert into name values 'lxkxf', '24';", 0, 0, & zerrmsg );
It will repeatedly Open and Close database files for 1 million times, so the speed will of course be slow. Therefore, we should use "Transactions" in this case ".
The specific method is as follows: add
Rc = sqlite3_exec (db, "BEGIN;", 0, & zErrMsg );
// Execute the SQL statement
Rc = sqlite3_exec (db, "COMMIT;", 0, & zErrMsg );
In this way, SQLite will first cache all SQL statements to be executed in the memory, and then write them into the database at one time when the COMMIT is executed. In this way, the database file is opened and closed only once, the efficiency is naturally greatly improved. There is a set of data comparison:
Test 1: 1000 INSERTs
Create table t1 (a INTEGER, B INTEGER, c VARCHAR (100 ));
Insert into t1 VALUES (1,13153, 'Thirteen thousand one hundred INTO Ty three ');
Insert into t1 VALUES (2,75560, 'venty five thousand five hundred sixty ');
... 995 lines omitted
Insert into t1 VALUES (998, 66289, 'sixty six thousand two hundred eighty nine ');
Insert into t1 VALUES (999,24322, 'twenty four thousand three hundred twenty two ');
Insert into t1 VALUES (1000,94142, 'Ninety four thousand one hundred forty two ');
SQLite2.7.6:
13.061
SQLite2.7.6 (nosync ):
0.223

Test 2: Use transaction 25000 INSERTs
BEGIN;
Create table t2 (a INTEGER, B INTEGER, c VARCHAR (100 ));
Insert into t2 VALUES (1,59672, 'entity ty nine thousand six hundred seventy two ');
... 24997 lines omitted
Insert into t2 VALUES (24999,89569, 'hthty nine thousand five hundred sixty nine ');
Insert into t2 VALUES (25000,94666, 'Ninety four thousand six hundred sixty six ');
COMMIT;
SQLite2.7.6:
0.914
SQLite2.7.6 (nosync ):
0.757

It can be seen that the database efficiency is greatly improved after transactions are used. However, we should also note that the use of transactions also has a certain amount of overhead, so you do not need to use operations with a small amount of data to avoid extra consumption.

Http://hi.baidu.com/duxikuan/item/f75583dd61d54a3be3108f8d