SQLite , Derby vs file system

Question

Hi All,

I'm working on a Java desktop application that reads and writes from/to different files. I think a better solution would be to replace the file system by a SQLite database. How hard is it to migrate to this system and embed a database in my application? are there real performance gains? Thanks a lot

Answer 1

I don't think you will get a performance just by changing the data store to SQLite. You should migrate a FS-based store to a relational (SQL) database store if.

• You need atomic operations. - your app can crash at any time and you don't have to worry about corrupt data)
• You need asynchronous operations. - multiple instances of your application can simultaneously modify the data without corrupting it/getting an invalid state.
• You need data normalization. - Products -m2m-> Catalogs
• Automatic Indexes - If you need fast searching capabilities (if your file system isn't already fast enough)
• You need abstraction of complex data operations. - i.e. SELECT SUM(Price) WHERE Price < 10 and the likes

Those are some of the gains you get by switching to SQLite.

You can gets performance gains from SQLite if you also properly utilizes one or more of the above properties of a relational store that you don't have using a file-based system.

Answer 2

I don't think you will get notable performance gains on any standard desktop - as long as your application isn't doing nothing else but torturing the file system with reads and writes :) At the end, performance mainly depends on your code.

If you consider switching to SQLite, you may want to have a look at SQLJet, a pure Java implementation of SQLite. I haven't used it myself, but I think deploying it should be similar to Derby: all you need is add some Jars to your application and you are settled. I would try to avoid using external SQLite (or any other dependency on an external application) as it will considerable complicate the setup of your application.

Answer 3

Embedding Derby in a Java desktop application is straightforward. You simply add the Derby jar to your application, decide where you want to store your data on disk, and write standard SQL and JDBC calls to invoke Derby operations. Well-written Derby applications can deliver extremely high performance but you must pay attention to your schema and transaction design. Derby has good tools for analyzing and addressing performance problems.