Very slow bulk insert
Posted: Wed 29 Oct 2014 19:20
I'm using the latest version of dotConnect for Metro (WinRT 8.1).
I need to copy data from one database to another using the following code:
This code executes almost 1 minute for about 3000 records. I've got a little better performance with the following setting:
But it is still more than 20 seconds. I got another 10 seconds down if I don't use two databases and I copy data from one table to another within one database. So, attaching second database is one cause of the bad performance.
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.
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.