On 2015-08-24 06:43 PM, Mike McWhinney wrote: > Hello, > I am trying to troubleshoot a problem that I'm having with a program > usingSQLite over a wireless network. I know that wireless networks can > havetheir own sets of issues that cause database access problems. > What is happening is that something that takes 1 or 2 seconds on a > wirednetwork will take 15-20 seconds over wireless. > > I am using System.Data.SQLite, which is based on version SQLite 3. 8.8.3 > (version 1.0.96.0)with C# Visual Studio. > The program is installed and runs off a network. The database is also on a > network. > The reads are somewhat slower running on a wired network than when run on a > local machine.However, when run over wireless it is significantly slower. > These are mostly reads onfairly simple queries. > > Are there any pragmas that may be used to increase the latency or to allow > thereads to process faster across a network? Or are there any other tips or > tricksthat may be used to speed up access? This is a multi-user database with > about 2-10 usersreading/writing data. Most of the time, the client > application sits idle. The SQLite connectionsare opened only on demand, then > closed after I/O operations. However this opening and closingis kept to a > minimum.
This is a hard one. To mention the obvious: 2-10 users needing data over a network...? Why on Earth would you use SQLite for this? MySQL/MariaDB and Postgres (to name just 2) will do that job significantly better (with respect to handling the networking anyway) and they cost exactly the same amount. http://www.sqlite.org/whentouse.html Secondly, the simplicity of the query does not enter into it. What costs time is acquiring locks, reading bytes, etc. A lot of that goes on in the background while you do what feels like "simple" queries. I would suggest using larger cache amounts and the like, but then you close and open DB connections (if I understand correct), so will hardly notice a benefit for that. (This is where server based DB's as mentioned above really shines). Lastly, the really bad news: Wireless networks are slow. That's how they roll - especially when the airspace is congested (as is commonly the case). Slower by a factor of 10 (as in your case) is not only possible, but rather likely. Improved bandwidth routers or better (read: more expensive) routers will be the go-to solution for that. Sorry for the lack of good news... Ryan
