SQLite terrible slow on same terminal server network but not on local network with physical computers

Question

I have two applications (server and client version). Server version put 500.000 records with filenames in a sqlite database file. This DB file is reachable through the network. The client version has a textfield where a filename will be put down by a barcode-scanner. After putting in the filename, the application will connect to the database file on a network mapped drive and ask one query:

select * from files where filename='testfile.pdf'

The database file is around 73MB. When I play this query out on a local machine where both server and client runs, it runs direct, no delay.

When I run this in a local network (not with a terminal server) also no delay or half a second.

When I run this on a terminal server (both server and client version on same machine) no delay.

Yet when I run the query on two different terminal server instances (server version on one and client version on another) in the same datacenter with 1 gigabit connection, it takes up to 10 seconds before the query has been executed.

I have the idea that the client loads the whole SQLite Database file before it can play the query on it. Is there a way to keep using SQLite while making this setup quicker?

My ideas are loading the whole DB in an array when booting up the client version... Although I hope there is a different way for accesing the SQLite database.

Answer 1

As pointed out by Michael Randall, SQLite is just a file and has its own limitations. It's just a tool one can use to test stuff, this is the "Lite" referring to in its name. You can migrate it into other RDBMS. You can use DBSofts for this purpose, by:

• choosing SQLite as the data source
• choosing the RDBMS you favor as the destination
• selecting tables
• clicking on Submit

PostgreSQL

https://www.dbsofts.com/articles/sqlite_to_postgresql/

SQL Server

https://www.dbsofts.com/articles/sqlite_to_sql_server/

Oracle

https://www.dbsofts.com/articles/sqlite_to_oracle/

MySQL

https://www.dbsofts.com/articles/sqlite_to_mysql/

Scripting

You can do it on your own as well. In order to do so, first you dump SQLite (https://www.sqlitetutorial.net/sqlite-dump/) and then convert the script into a script which works for the chosen RDBMS.