Local vs. Network Database Speeds

English General
1

I’ve demoed using both the local and network databases. I’ve noticed that the network database synchronize and playlist creation are very fast, while the same local database actions are much slower.

My guess is that my report is already known and the speeds are normal behaviour. However, I simply wished to report in case this is an unknown problem.

Best Regards, Alec

2

It’s essentially a PostgreSQL vs. SQLite thing.

In my opinion, it’s obvious that a full-featured SQL database system like PostgreSQL is much faster than SQLite, which stores all data in a single file.

3

Very Good. I just wish to report these observations in case there is an unknown issue. Thank you, Torben.

Best Regards, Alec

4

I took another look at the (somewhat outdated) SQLite Optimization FAQ, and I read about the “synchronous” setting. It is also described in the SQLite documentation: http://www.sqlite.org/pragma.html (you need to scroll down a bit)

With the default setting (“FULL”), SQLite waits for the filesystem to write any changes to the disk before returning from an UPDATE or INSERT statement. This is very safe, because the database file can never corrupt if your computer crashes in the middle of an operation, but it is also very slow.

I have tried to set the value to OFF, and in fact, the write performance increases significally. It’s as fast as PostgreSQL then. But I’m unsure if it’s a good idea to set this as a default value, because it bears a certain risk. Perhaps it’s a good idea to offer the setting as an option in the database configuration dialog, so each user can pick whatever he prefers. If you make backups of your database file regularly (and you should do so), then it might be safe for you to use the OFF setting and benefit from the increased performance.

For the moment - and this applies to Build 790 which I just uploaded - you can set the synchronous mode manually to OFF (or NORMAL) by adding the following line to the respective section in database.ini:

Synchronous=OFF

My “Database1” section for my local mAirListDB now looks like this:

[Database1]
Type=mAirListDBLite
Enabled=on
DatabaseID=3C7678A8EDA3BCC73F54941AB1128CD0
Filename=d:\projects\mAirList\trunk\database.db
Synchronous=OFF

Please give it a try.

5

Nice idea. Have you tested the NORMAL setting or not? It looks like the best choice because it seems on paper to offer a good balance between the safety of the MOST critical operations waiting for the hardware, while ‘trusting’ disk caching to handle the less critical operations.

Of course, if NORMAL offers little performance benefit, then probably OFF is a good choice, provided that you understand (as a user) that you are at the mercy of Windows write-behind disk caching, thus subject to data loss or corruption in case of a power outage or similar problem. Oh, and (presumably?) also if you close mAirListDB before a write operation has actually completed on disk. :o Similar to pulling out your USB stick without Safely Removing Hardware. :smiley:

BFN
CAD

6

Closing the application in the middle of a write operation is not possible (unless you “close” it by killing its process), because mAirList will wait for all database operations to complete before terminating.

I have tested NORMAL, but it’s almost as slow as FULL.

7

OK, I’ll try the OFF setting and let you know.

BFN
CAD