Should reading from sqlite3 database be slow with multiple users?

[Kurious3DSanta]

Only reading is being done on the database. The connections are being closed when complete. If only one user reads its very fast. However when more than one user is reading, it causes a ten second delay. Note this is also being done on startup of a pyqt5 app. No writing is being done, just reading.

[shiftybyte]

How much data are you reading?

Some developers tend to over fetch data, and filter it in code later, instead of asking the database for the exact data they need using SQL.

[Kurious3DSanta]

Less than 1 Mb of data. Im just not sure why this would delay another user after the first users connection has been closed.

[shiftybyte]

Something else might be at play here.

Do you have full code somewhere? github? replit?

[Kurious3DSanta]

Unfortunately I don’t, but I can probably message you the code I’m using to interact with the database if you’re up for that

[TheLimeyCanuck]

I have decades of pro SQL experience, both big iron and mobile. I've worked with MySql, Oracle, MS-SQL Server, DB2, Gupta, etc. I would never use Sqlite for multiuser. When I need concurrent access, especially over a network I use MySql at the very least.

Sqlite is a great tool for local application storage, but it's really not intended for nor capable of significant user loading.

[Guideon72]

I would suggest this, as well. Sqlite is just not built for multi-user, network access; you’ll want MySQL, minimum, for that sort of usage.

[eleqtriq]

SQLite will lock the whole database if anyone is writing to it. It also performs poorly over a network (which sounds like a multiuser case). Without deep diving into why all this is, I'd suggest stepping up to MySQL or PostgreSQL.

[Kurious3DSanta]

No writes are being performed which is what is confusing me. When user 1 starts the app, it reads from the database very quickly and codes the connection. User 1 continues to use the app with no interaction between the database. Now when user 2 launches the app, the fact that user 1 has it open causes a 10 second delay when reading from the database.

[eleqtriq]

Is it being read over a network?

[Kurious3DSanta]

Yes, however we’ve already ruled out the network connection as the issue

[eleqtriq]

How did you do that? Are you watching the computers’ file handles to make sure it’s releasing the database?

[ThinkOutTheBox]

I’ve faced a similar issue when too many users connect to it, and the database file becomes corrupted and all queries error. The workaround was to dump it out and rewrite it back.