英文:
Insert in SQLite faster using single Android Room transaction rather than multi transactions
问题
默认情况下，DAO类中使用**@Insert、@Update或@Delete注释的每个方法将在其自己的事务中执行，而其他事务将按照先到先服务的顺序排队并执行。如果希望使用单个事务运行多个方法，可以使用runInTransaction()**方法。如果我对此有误，请纠正我。
我预期在向数据库插入数据时，使用单个事务而不是多个事务的性能会更好。但即使在单个事务中调用两个不同的DAO方法后，性能方面没有任何改变。
我使用了两种不同的方法来测量性能。其中一种方法是在不使用runInTransaction()的情况下插入数据。
以下是在调用此方法后的日志记录：
14:49:55.860 D Is it running in transaction?: false
14:50:00.480 D insertLanguageResourceList completed. Duration is 4619 milliseconds!
14:50:05.299 D insertProducts completed. Duration is 4820 milliseconds!
另一种尝试是在runInTransaction()内运行此方法。
以下是在调用insertWithSingleTransaction()后的日志记录：
14:54:07.495 D Is it running in transaction?: true
14:54:12.483 D insertProducts completed. Duration is 4988 milliseconds!
14:54:16.573 D insertLanguageResourceList completed. Duration is 4090 milliseconds!
我的DAO类
@Dao
public abstract class LanguageResourceDao {
@Insert
public abstract Completable insertLanguageResourceList(List<LanguageResource> languageResourceList);
@Insert
public abstract Completable insertProducts(List<Product> productList);
}
总之，如果不使用runInTransaction()，插入数据需要9349毫秒，而使用runInTransaction()则需要9078毫秒。如果这两种不同的方法在性能上表现相同，那么在同一事务中执行两个不同的方法有什么意义呢？期望单个事务的性能优于默认行为是错误的吗？如何提高向数据库插入数据的速度？
英文:
By default, each method annotated with @Insert, @Update, or @Delete in DAO class will be executed within its own transaction and additional transactions are queued and executed on a first come, first serve order. If it is desired to run multi methods using a single transaction runInTransaction() method can be used. Correct me if I'm wrong about this.
I expected using single transaction rather than multi transactions performs better while inserting data in DB. But nothing has changed in terms of performance even after I call 2 different DAO methods in the single transaction.
I used two different methods to measure performance. First of them insert the data without using runInTransaction()
private void insert() {
Log.d(DEBUG_TAG, "Is it running in transaction?: " + resourceRoomDatabase.inTransaction());
startTime = Instant.now();
disposable.add(dao.insertLanguageResourceList(languageResourceList)
.subscribeOn(Schedulers.io())
.subscribe(() -> {
endTime = Instant.now();
Log.d(DEBUG_TAG, "insertLanguageResourceList completed. Duration is " + (Duration.between(startTime, endTime).toMillis()) + " milliseconds!");
startTime = endTime;
},
(error) -> Log.d(DEBUG_TAG, "insertLanguageResourceList error: " + error)));
disposable.add(dao.insertProducts(productList)
.subscribeOn(Schedulers.io())
.subscribe(() -> {
endTime = Instant.now();
Log.d(DEBUG_TAG, "insertProducts completed. Duration is " + (Duration.between(startTime, endTime).toMillis()) + " milliseconds!");
startTime = endTime;
},
(error) -> Log.d(DEBUG_TAG, "insertProducts error: " + error)));
}
Here is the logcat after I call this method
14:49:55.860 D Is it running in transaction?: false
14:50:00.480 D insertLanguageResourceList completed. Duration is 4619 milliseconds!
14:50:05.299 D insertProducts completed. Duration is 4820 milliseconds!
And other way I tried is running this method inside runInTransaction()
private void insertWithSingleTransaction() {
new Thread(() -> resourceRoomDatabase.runInTransaction(this::insert)).start();
}
Here is the logcat after I call insertWithSingleTransaction()
14:54:07.495 D Is it running in transaction?: true
14:54:12.483 D insertProducts completed. Duration is 4988 milliseconds!
14:54:16.573 D insertLanguageResourceList completed. Duration is 4090 milliseconds!
My DAO class
@Dao
public abstract class LanguageResourceDao {
@Insert
public abstract Completable insertLanguageResourceList(List<LanguageResource> languageResourceList);
@Insert
public abstract Completable insertProducts(List<Product> productList);
}
In conclusion without using runInTransaction()
it takes 9349 milliseconds and with runInTransaction() it takes 9078 milliseconds to insert data in DB.
If these two different ways acts the same in performance what's the point of executing two different methods in the same transaction? Is it wrong to be expecting single transaction performs better than the default behavior? How can I increase the speed of inserting data in DB?
答案1
得分: 1
事务仅涉及数据库方面。在计时中，还有其他方面，你没有考虑到，即包装数据库操作的部分。
你基本上可以将事务等同于磁盘活动，这包括将结果数据写入磁盘（-WAL文件），可能还包括检查点（将-WAL文件的某些数据写入实际数据库文件）。
- 如果不使用默认的WAL模式，即日志模式，那么磁盘活动将包括写入实际数据库文件和-journal文件。
数据也存储在页面中，如果一个测试请求比另一个测试请求更多或更少的新页面，但活动量相同，这也可能是不公平的测试。
- 例如，假设一个测试需要1.5页，而另一个测试有一个空页面，那么它只需要创建一个新页面。而一个从3/4满的页面开始的测试将需要创建2个页面。
另一个因素可能是IO缓存，这可能对两个测试中的一个有利。
你确实需要运行更全面的测试，以提供更公平/一致的结果。
然而，正如预期的那样，runInTransaction 显示了约3%的改进。
演示
以下是一个演示，显示了使用单个事务时速度快两倍的显著差异。
- D/DBTIMING_OVERALL: 在1个事务中的总时间 = 13376。在多个事务中的总时间 = 27681。
与其在线程中运行，不如纯粹在主线程上运行（.allowMainThreadQueries）。
它基于一个非常简单的单表：
@Entity
class Basic {
@PrimaryKey
Long id=null;
String name;
}
而不是使用 @Dao 注释的方法，而是在Database类的一个方法中使用了 SupportSQLiteDatabase，如下所示：
long insertMany(int count, boolean inTransaction, String tagSuffix) {
SupportSQLiteDatabase db = this.getOpenHelper().getWritableDatabase();
if (inTransaction) db.beginTransaction();
long start = System.currentTimeMillis();
for (int i=0; i < count; i++) {
db.execSQL("INSERT INTO basic (name) VALUES('xxxx:" + start + "');");
}
if (inTransaction()) {
db.setTransactionSuccessful();
db.endTransaction();
}
Log.d("DBTIMING_" + tagSuffix ,"To insert " + count + " rows, took " + (System.currentTimeMillis() - start) + " milliseconds. Done in 1 transaction " + inTransaction);
return System.currentTimeMillis() - start;
}
然后是一些活动代码：
public class MainActivity extends AppCompatActivity {
TheDatabase db;
AllDAOs dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
long in1=0;
long inmany=0;
db = TheDatabase.getInstance(this);
dao = db.geAllDAOs();
in1 = in1 + db.insertMany(20000,true,"R1");
inmany = inmany + db.insertMany(20000,false,"R2");
inmany = inmany + db.insertMany(20000,false,"R3");
in1 = in1 + db.insertMany(20000,true,"R4");
inmany = inmany + db.insertMany(20000,false,"R5");
in1 = in1 +db.insertMany(20000,true,"R6");
in1 = in1 +db.insertMany(20000,true,"R7");
inmany = inmany + db.insertMany(20000,false ,"R8");
in1 = in1 + db.insertMany(20000,true,"R9");
inmany = inmany + db.insertMany(20000,false,"R10");
inmany = inmany + db.insertMany(20000,false,"R11");
in1 = in1 + db.insertMany(20000,true,"R12");
inmany = inmany + db.insertMany(20000,false,"R13");
in1 = in1 +db.insertMany(20000,true,"R14");
in1 = in1 +db.insertMany(20000,true,"R15");
inmany = inmany + db.insertMany(20000,false ,"R16");
Log.d("DBTIMING_OVERALL","Total for in 1 transaction = " + in1 + ". Total for in many transactions = " + inmany + ".");
}
}
- 注意使用了2对运行，第一对颠倒了在单个事务中完成的顺序。还有相当多的插入操作。
完整的结果写入日志如下：
2023-06-02 11:39:18.445 D/DBTIMING_R1: To insert 20000 rows, took 1897 milliseconds. Done in 1 transaction true
2023-06-02 11:39:21.934 D/DBTIMING_R2: To insert 20000 rows, took 3489 milliseconds. Done in 1 transaction false
2023-06-02 11:39:25.385 D/DBTIMING_R3: To insert 20000 rows, took 3451 milliseconds. Done in 1 transaction false
2023-06-02 11:39:27.018 D/DBTIMING_R4: To insert 20000 rows, took 1632 milliseconds. Done in 1 transaction true
2023-06-02 11:39:30.452 D/DBTIMING_R5: To insert 20000 rows, took 3434 milliseconds. Done in 1 transaction false
2023-06-02 11:39:32.108 D/DBTIMING_R6: To insert 20000 rows, took 1656 milliseconds. Done in 1 transaction true
2023-06-02 11:39:33.744 D/DBTIMING_R7: To insert 20000 rows, took 1636 milliseconds. Done in 1 transaction true
2023-06-02 11:39:37.167 D/DBTIMING_R8: To insert 20000 rows, took 3423 milliseconds. Done in 1 transaction false
2023-06-02 11:39:38.814 D/DBTIMING_R9: To insert 20000 rows, took 1646 milliseconds. Done in 1 transaction true
2023-06-02 11:39:42.246 D/DBTIMING_R10: To insert 20000 rows, took 3432
<details>
<summary>英文:</summary>
The **transaction** is purely the Database aspect. There are other aspects that you are not considering in the timings; the wrapper around the database actions.
You can basically ascribe a **transaction** to equate to disk activity which would be writing the resultant data to the disk (the -WAL file), perhaps including checkpointing (writing some of the -WAL file data to the actual database file).
- if not using the default WAL mode, i.e. Journal Mode, then the disk activity is to write to both the actual database file and the -journal file.
Data is also stored in pages, if one test requests more or fewer new pages than the other for the same amount of activity, perhaps due to previous test then this may also be an unfair test.
- e.g. say a test requires 1.5 pages and one test has an empty page then it will require the creation of just one new page. Whilst a test that starts with a page that is 3/4 full will require the creation of 2 pages.
Another factor could be IO caching which may be advantageous to one of the two tests.
You really need to run more comprehensive tests that provide a fairer/more consistent result.
However as expected, the `runInTransaction` does show a circa **3%** improvement.
**Demo**
Here's a demo that shows a marked distinction (twice as fast when using a single transaction).
- ***D/DBTIMING_OVERALL: Total for in 1 transaction = 13376. Total for in many transactions = 27681.***
Rather than running in threads it is run purely on the main thread (`.allowMainThreadQueries`).
It is based on a very simple single table:-
@Entity
class Basic {
@PrimaryKey
Long id=null;
String name;
}
No use of `@Dao` annotated methods rather a SupportSQLiteDatabase is used in a method of the Database class as per:-
long insertMany(int count, boolean inTransaction, String tagSuffix) {
SupportSQLiteDatabase db = this.getOpenHelper().getWritableDatabase();
if (inTransaction) db.beginTransaction();
long start = System.currentTimeMillis();
for (int i=0; i < count; i++) {
db.execSQL("INSERT INTO basic (name) VALUES('xxxx:" + start + "');");
}
if (inTransaction()) {
db.setTransactionSuccessful();
db.endTransaction();
}
Log.d("DBTIMING_" + tagSuffix ,"To insert " + count + " rows, took " + (System.currentTimeMillis() - start) + " milliseconds. Done in 1 transaction " + inTransaction);
return System.currentTimeMillis() - start;
}
Then some activity code:-
public class MainActivity extends AppCompatActivity {
TheDatabase db;
AllDAOs dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
long in1=0;
long inmany=0;
db = TheDatabase.getInstance(this);
dao = db.geAllDAOs();
in1 = in1 + db.insertMany(20000,true,"R1");
inmany = inmany + db.insertMany(20000,false,"R2");
inmany = inmany + db.insertMany(20000,false,"R3");
in1 = in1 + db.insertMany(20000,true,"R4");
inmany = inmany + db.insertMany(20000,false,"R5");
in1 = in1 +db.insertMany(20000,true,"R6");
in1 = in1 +db.insertMany(20000,true,"R7");
inmany = inmany + db.insertMany(20000,false ,"R8");
in1 = in1 + db.insertMany(20000,true,"R9");
inmany = inmany + db.insertMany(20000,false,"R10");
inmany = inmany + db.insertMany(20000,false,"R11");
in1 = in1 + db.insertMany(20000,true,"R12");
inmany = inmany + db.insertMany(20000,false,"R13");
in1 = in1 +db.insertMany(20000,true,"R14");
in1 = in1 +db.insertMany(20000,true,"R15");
inmany = inmany + db.insertMany(20000,false ,"R16");
Log.d("DBTIMING_OVERALL","Total for in 1 transaction = " + in1 + ". Total for in many transactions = " + inmany + ".");
}
}
- Noting that 2 pairs of runs are used, the first pair reversing the sequence of if done in a single transaction. Also a fair number of inserts.
The full results written to the log being:-
2023-06-02 11:39:18.445 D/DBTIMING_R1: To insert 20000 rows, took 1897 milliseconds. Done in 1 transaction true
2023-06-02 11:39:21.934 D/DBTIMING_R2: To insert 20000 rows, took 3489 milliseconds. Done in 1 transaction false
2023-06-02 11:39:25.385 D/DBTIMING_R3: To insert 20000 rows, took 3451 milliseconds. Done in 1 transaction false
2023-06-02 11:39:27.018 D/DBTIMING_R4: To insert 20000 rows, took 1632 milliseconds. Done in 1 transaction true
2023-06-02 11:39:30.452 D/DBTIMING_R5: To insert 20000 rows, took 3434 milliseconds. Done in 1 transaction false
2023-06-02 11:39:32.108 D/DBTIMING_R6: To insert 20000 rows, took 1656 milliseconds. Done in 1 transaction true
2023-06-02 11:39:33.744 D/DBTIMING_R7: To insert 20000 rows, took 1636 milliseconds. Done in 1 transaction true
2023-06-02 11:39:37.167 D/DBTIMING_R8: To insert 20000 rows, took 3423 milliseconds. Done in 1 transaction false
2023-06-02 11:39:38.814 D/DBTIMING_R9: To insert 20000 rows, took 1646 milliseconds. Done in 1 transaction true
2023-06-02 11:39:42.246 D/DBTIMING_R10: To insert 20000 rows, took 3432 milliseconds. Done in 1 transaction false
2023-06-02 11:39:45.737 D/DBTIMING_R11: To insert 20000 rows, took 3491 milliseconds. Done in 1 transaction false
2023-06-02 11:39:47.358 D/DBTIMING_R12: To insert 20000 rows, took 1621 milliseconds. Done in 1 transaction true
2023-06-02 11:39:50.844 D/DBTIMING_R13: To insert 20000 rows, took 3486 milliseconds. Done in 1 transaction false
2023-06-02 11:39:52.490 D/DBTIMING_R14: To insert 20000 rows, took 1645 milliseconds. Done in 1 transaction true
2023-06-02 11:39:54.132 D/DBTIMING_R15: To insert 20000 rows, took 1642 milliseconds. Done in 1 transaction true
2023-06-02 11:39:57.606 D/DBTIMING_R16: To insert 20000 rows, took 3474 milliseconds. Done in 1 transaction false
2023-06-02 11:39:57.606 D/DBTIMING_OVERALL: Total for in 1 transaction = 13376. Total for in many transactions = 27681.
- As can be seen throughout, the results consistently show a benefit of doing the work in a single transaction.
- There is also a marked reduction in **wrapping** code.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论