System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 104723107d2720e2dd77f824ca0ee5ed66cc4a19
Title: Very slow running all operation if the database located on a shared folder
Status: Deferred Type: Performance
Severity: Important Priority: Medium
Subsystem: LINQ Resolution: Open
Last Modified: 2017-04-03 23:25:24
Version Found In: 1.0.103
User Comments:
anonymous added on 2016-11-10 14:16:43:
I try use SQLite with EF6 (DB first).
If database file located on local machine all fine, but if I move my database to shared folder (for example \\TABLETBOOK\d\base.sqlite3), the performance drop (about 5-10 times).
My connection string: 
$@"metadata = res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl; provider=System.Data.SQLite.EF6;provider connection string='data source=""{databasePath}""'";
(if database on a shared folder, of course I add "\\" before path)

I tried set AutoDetectChangesEnabled and ValidateOnSaveEnabled to false, but performance stayed very bad.

Remark: If I use to access data simple SQLiteConnection without EF6, performance is ok (connection = new SQLiteConnection("Data Source=" + basepath + ";") { ParseViaFramework = true };).

mistachkin added on 2016-11-10 19:47:21:
It may be quite difficult to reproduce this issue since it probably depends on
the network configuration.

Are both machines on the same local network?  What type of network is it?  Are
the machines communicating via IPv4 or IPv6 (or something else)?

anonymous added on 2016-11-10 23:08:32:
I tested on 4 different machines. Two in one local network (ethernet, ipv4), and two in another (ethernet, ipv4). Problem repeater in all 4 times. 
If shared folder are located on the same PC as the application, that bug disappears.
On 3 machines (win 7 and win 10) I used x64 version of SQLite.Interop.dll, and on 1 - x86 (win 7).

mistachkin added on 2016-11-11 02:50:03:
Interesting.

Do you know if there is any packet loss on the network, maybe due to a bad
network card?

Do other applications using the network behave slowly?

It might be useful to capture a log file using the following tool:

[https://technet.microsoft.com/en-us/sysinternals/processmonitor]

anonymous added on 2016-11-11 07:59:39:
Network works fine.
No, other applications run with normal speed. As I wrote earlier, if I make queries to database in shared folder with SQLiteConnection, performance in normal.

I attach log file what you asked (same operation (4 query) with local file and with file on shared folder). In case shared folder, application makes much more read operations.

If that important, I use pattern unit of work, and recreate DBContext for each query.

mistachkin added on 2016-11-11 23:59:09:
Perhaps the Entity Framework is opening and closing connections too much?

Do you have some example code that demonstrates the issue?

If so, I can try to reproduce the issue locally.

anonymous added on 2016-11-14 21:45:27:

[another anonymous user]

Isn't putting an SQLite database on a NFS unsupported at all (at least when used by multiple clients)? From the SQLite FAQ at https://sqlite.org/faq.html#q5:

"You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. […] Sharing an SQLite database between two or more Windows machines might cause unexpected problems."


anonymous added on 2016-11-15 14:31:25:
I wrote a test application, and to my surprise, did not see the problem.
What confuses me most is the fact that when debugging step by step, the problem of slipping away (you can see screenshots, which I attached).
I tried to use the same context for all queries, but the problem remained.

I also attached a test application.

I'm at a loss. I need a few more days for analysis.

mistachkin added on 2016-12-06 22:59:47:
This issue will not block the 1.0.104.0 release.

mistachkin added on 2016-12-06 23:05:51:
Can you try updating your EntityFramework package to 6.1.3?

mistachkin added on 2017-04-03 23:25:24:
This issue will not block the 1.0.105.0 release.