SQLite open database question

[DeanH]

I have a couple of questions about using SQLite. I have been using the embedded version with PureBasic for 12 years. About 2,000 users, mostly schools in Australia. The database can be used by multiple workstations on a LAN - mostly 1 user at a time but at times 2 or 3, rarely more.

Is it better/faster to open the database at the start of the program, or to open it when needed, execute the DB functions, then close the database each time? I have been using the latter. Or is it ok to open at the start and leave it open until the program ends? Does it make a difference?

Second question involves transactions. I have been using Begin Immediate/Commit for writing (Update, Insert) but not reading (Select). Is it necessary to use Begin/Commit when reading as well or is it better to avoid except for large writes? Would WAL mode help?

[skywalk]

Wow, your use case indicates PostgresSQL or MariaDB.
SQLite is not absolutely stable over a network and definitely does not support multiple concurrent writers. You would have to do timeouts and pool user writes. And how do you differentiate reads vs writes? SQL parsing for update,insert is a drag.
Of course you may slip by with small user counts, but why risk it?

The benefit of using a database and standard SQL is you can migrate fairly easily once your app demands more users and concurrency.
You may have to add/expand your user table for the server case.

[DeanH]

I was hoping to avoid this can of worms about concurrency. I have seen malformed database errors on stand-alone, non-networked systems. I am not in a position to force users to change to another database, and am loath to introduce a complex-to-install database system like Postgresql or MySql (MariaDB). The only database system acceptable to education authorities is MS-SQL. I can use SQLite because it is embedded or comes with the system as a dll, and does not require any setup on a server/host.

[PBJim]

Hi Dean,
I think this linked post might help to clarify.

I am trying to develop a Windows based application with multiple users accessing the same database at the same time. Can SQLite support multiple accesses at one time? Is SQLite stable in this regard? https://stackoverflow.com/questions/510 ... iple-users

Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter). But it is very well tested and very stable (and widely used) so you can trust it. You may read this short document for information when to use SQLite and not: http://www.sqlite.org/whentouse.html

In SQLite's page, see 1. Situations Where SQLite Works Well, then Server-side database as it offers a scenario which is relevant in your case — "but instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific".

Under 2. Situations Where A Client/Server RDBMS May Work Better, sub-section Client/Server Applications it elaborates further to explain why this is, including shortcomings associated with locking and network latency.

[infratec]

BAck to your questions:

A SELECT does not need a transaction.
Only with SQL statements which modifies the database it makes sense to use transactions.

In general open the database once is faster than open it every time again.
In your case:

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

it is better to open it every time again and hope that the file locking is working.

Maybe you need to implement something to check if the file is already in use.

[DeanH]

Thank you. That is what I wanted to know. What I am doing is correct. While 75% of my customers use the system on LAN's, 99% only use 1, 2 or 3 workstations. It is unusual for more.

[blueb]

If you want to take it further....

Including the **libmariadb.dll** file is sufficient for connecting your program to MariaDB. The user does not need the full MariaDB package. This DLL file is part of the MariaDB Connector/C library, which allows applications developed in PureBasic to connect to MariaDB and MySQL databases.

e.g.

Code: Select all

UseMySQLDatabase("libmariadb.dll") ; in the same folder as your exe

[spikey]

Including the **libmariadb.dll** file is sufficient for connecting your program to MariaDB.

That's not the issue I think. It sounds like deploying the backend database would be a technological or political problem for the end users.

The only database system acceptable to education authorities is MS-SQL

For the record, you can access MS-SQL servers via ODBC.

[blueb]

Dean said:

I can use SQLite because it is embedded or comes with the system as a dll, and does not require any setup on a server/host.

Same applies to MariaDB, if used as above.

[skywalk]

Thank you. That is what I wanted to know. What I am doing is correct. While 75% of my customers use the system on LAN's, 99% only use 1, 2 or 3 workstations. It is unusual for more.

I cannot understand this quote?
SQLite on a network drive is risky. Less risky if only for reads.
How do your customers install your app?
You rely on an existing SQLite.dll somewhere?
What version and options are compiled for the existing sqlite3.dll?
Why can't your app also install libmariadb?

[the.weavster]

That is what I wanted to know. What I am doing is correct.

I'm not so sure. If your application is on one PC and the SQLite database file is on another ( which is how it seems if I've understood you correctly ) then it's contrary to recommended use. From the SQLite website:

Is the data separated from the application by a network? → choose client/server

If your application is a server that resides on the same physical machine as the SQLite database ( e.g. a HTTP / JSON-RPC server that exposes an API and HTML / JS UI that consumes that API ) then that would be OK:

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario.

[normeus]

It sounds like DeanH has the database file on a shared network drive and other computers open that database file, no need to install anything elso on the shared drive only the data file.
You should at least implement some kind of file locking procedure.
Blueb's suggestion of using "libmariadb.dll" sounds really interesting.

Norm

[DeanH]

My apologies for not describing the setup. Normeus has it right. No software runs on the host (server or workstation P-to-P). The database and exe's are in a shared folder on the host. Workstations have a mapped drive back to that folder and open a PB produced exe. Example L:\Bm.exe. SQLite is embedded in the exe via UseSQLIteDatabase(). It runs on the workstation, not the host. All processing is done on the workstation; it simply sees the host as another drive. I have used this arrangement since 1990, originally with the old DOS version (written in ZBasic). I used record and file locking extensively with few problems. There was no database, just fixed-record-size or sequential files. I later adopted QDBM when I switched to GFA then ZB5. It automatically locked. So that's the setup. Nothing fancy.

SQLite documentation states that it is ACID compilant without any special commands, and any write functions like Update or Insert lock the entire file for a single user. Begin / Commit limits it to a single user while writing. I do not do this when reading (Select). My question was whether or not that is needed. Apparently not. My tests confirmed that.

As I indicated before, I cannot change to another database system. There are over 2,000 users in various countries. I have inquired by my employer and was told firmly that only MS-SQL would be accepted beyond what I currently do and no other DB system. The shared folder approach I have used has worked quite well for a long time. It has been remarkably stable considering over 1,000 schools use my system every week day. There have only been a few cases of malformed database errors. Most never see it. This has even happened on stand-alones so I do not believe sharing has been a cause. My main issue is speed of access when 3 or 4 workstations are using the DB at roughly the same time. A few users (not all, and not the majority) have reported slow speed in this situation. Hence the question about WAL mode.

Otherwise, I am quite happy with SQLite and the way PureBasic handles it.

[tored]

Greetings

https://sqlite.org/whentouse.html

Client/Server Applications


If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

SQLite claims that file locking may be buggy on network drives, but if you have run this setup for a long time without major issues I would say you are safe. SQLite is remarkably reliable.

Opening a closing the connection when needed is probably fine, you can always measure the time for doing so but my guess is that the following query is in magnitude much more expensive, thus the opening of connection is probably not even noticeable. Remember that some run SQLite on high performance PHP websites where each request opens a new connection without issues.

Benefit of opening connection only when need is that any errors that may occur is local to when doing the query.

However WAL should not be used on a network drive

https://www.sqlite.org/wal.html

All processes using a database must be on the same host computer; WAL does not work over a network filesystem. This is because WAL requires all processes to share a small amount of memory and processes on separate host machines obviously cannot share memory with each other.

One thing to note is that when you have a lot of concurrent operations on the SQLite database, queries may fail with SQLITE_BUSY

https://www.sqlite.org/rescode.html#busy

You may need some strategy to handle that

Good writeup about the topic
https://activesphere.com/blog/2018/12/2 ... qlite-busy

Other things to investigate

Run ANALYZE and PRAGMA optimize or check if already enabled

https://www.sqlite.org/lang_analyze.html
https://www.sqlite.org/pragma.html#pragma_optimize

Investigate if you need to run VACUUM, check if it’s automatically enabled. Can be expensive to run.

https://sqlite.org/lang_vacuum.html

PRAGMA page_size. Compare against filesystem block size, should be equal (unsure for network drives). Changing page size requires VACUUM

https://www.sqlite.org/pragma.html#pragma_page_size

Check PRAGMA cache_size

https://www.sqlite.org/pragma.html#pragma_cache_size

[tored]

About your database schema, do you store any large blobs together with other columns? If you do consider either store on blob directly on disk or have blobs in separate table. This helps performance when querying and updating tables by moving blobs to separate table.

Check you indexes, typically a database needs to recalculate the index after every write on the table (that is why it is good to wrap multiple writes in a transaction), if you have too many indexes or badly designed indexes it can reduce performance.

Log your slow queries, debug them with EXPLAIN. Avoid (full table) SCAN.

https://www.sqlite.org/eqp.html