tari-project/tari

Console wallet with many outputs sql queries optimize

Closed this issue · 1 comments

hansieodendaal commented

Using a console wallet with a large amount of outputs (84,000+), it takes uo to 6s to construct a one-sided transaction spending one output to one receiver with change to self.

Some of the profiled db queries (there are other time-hungry operations apart form the db access as well):

2024-03-04 14:01:07.955113400 [wallet::output_manager_service::database::wallet] TRACE sqlite profile - fetch_unspent_outputs_for_spending: lock 0 + db_op 1047 = 1047 ms
2024-03-04 14:01:10.982744500 [wallet::output_manager_service::database::wallet] TRACE sqlite profile - short_term_encumber_outputs (TxId: 4048846998157462111): lock 0 + db_op 1 = 1 ms
2024-03-04 14:01:11.148263700 [wallet::output_manager_service::database::wallet] TRACE sqlite profile - confirm_encumbered_outputs (TxId: 4048846998157462111): lock 0 + db_op 163 = 163 ms
2024-03-04 14:01:11.156091400 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 5 + db_op 0 = 6 ms
2024-03-04 14:01:11.163138600 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 6 + db_op 0 = 6 ms
2024-03-04 14:01:11.170076200 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 6 = 6 ms
2024-03-04 14:01:11.174403000 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.178777200 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.183380800 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.187884400 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.192301600 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.205458800 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 6 = 6 ms
2024-03-04 14:01:11.210843200 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 5 = 5 ms
2024-03-04 14:01:11.215761200 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.221657200 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 5 = 5 ms
2024-03-04 14:01:11.226626800 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 4 = 4 ms
2024-03-04 14:01:11.231706900 [wallet::key_manager_service::database::wallet] TRACE sqlite profile - insert_imported_key: lock 0 + db_op 5 = 5 ms
2024-03-04 14:01:11.246844900 [wallet::transaction_service::database::wallet] TRACE sqlite profile - fetch 'Completed Transaction': lock 0 + db_op 2 = 2 ms
2024-03-04 14:01:11.250367600 [wallet::transaction_service::database::wallet] TRACE sqlite profile - fetch 'Completed Transaction': lock 0 + db_op 3 = 3 ms
hansieodendaal commented

See #6196

Note: One or two more PRs to come.