I need to copy data from one database to another using the following code:
Code: Select all
using (SQLiteConnection conn = new SQLiteConnection("Data Source=ms-appdata:///local/main.db;Read Uncommitted=true;Busy Timeout=15000;Connection Timeout=60;Foreign Key Constraints=Off"))
{
using (SQLiteCommand cmd = new SQLiteCommand("ATTACH [second_db] AS db2", conn))
cmd.ExecuteNonQuery();
sbQuery.Append("BEGIN TRANSACTION;");
sbQuery.Append("INSERT INTO t1 (...) SELECT ... FROM db2.t1;");
sbQuery.Append("END TRANSACTION;");
using (SQLiteCommand cmd = new SQLiteCommand(sbQuery.ToString(), conn))
cmd.ExecuteNonQuery();
}
Code: Select all
PRAGMA foreign_keys = FALSE;PRAGMA synchronous=OFF;PRAGMA count_changes=OFF;PRAGMA journal_mode=OFF;PRAGMA temp_store=MEMORY;PRAGMA cache_size = 100000;PRAGMA default_cache_size = 100000;
However, if I try the same query with different Sqlite driver (SQLite for Windows Runtime) it executes less than 1 second.
Can you advice me where is a problem with this query using Devart dotConnect? Is there any other workaround how to achieve better performance?
Thank you in advance.