Very slow bulk insert

[dilbert]

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:

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();
}

This code executes almost 1 minute for about 3000 records. I've got a little better performance with the following setting:

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;

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.

[Pinturiccio]

Please tell us which product you use: dotConnect for SQLite or LinqConnect for Metro. Please also specify the product version.

If possible, please create and send us a small test project with the corresponding SQLite database that reproduces the issue.

[dilbert]

I use the latest version of LinqConnect for Metro (4.4.621).
I have sent you the sample project as you requested.

[MariiaI]

Thank you for the sample project. The question of performance in LinqConnect for Metro with the SQLite database is related to the C#-SQLite engine being used.
We will investigate the performance issue and the possibility to support original SQLite engine adapted for WinRT, and inform you about the results as soon as possible.

[dilbert]

Do you have any progress with using official SQLite engine?

[MariiaI]

Unfortunately, we can't tell any timeframe regarding this at the moment.
We will definitely inform you about the results as soon as any are available.