Looking for a local database for D2009+ [closed]

Question

I'm trying to update a legacy app that does all its data storage in a hacked-together system of BDE Paradox files. The program works pretty well, under certain narrow conditions, but it has serious performance issues.

I'd like to try and improve things by updating to a better database system. What I need is a local database, preferably one where I can store the whole thing in one file instead of the current "one or more files per table" system. It has to support foreign-key relationships and table indexing, and it has to be able to return a result quickly from a query of a table with hundreds of thousands of elements.

This last one is important. The current system is indexed, but that doesn't seem to matter much. All the queries seem to run in O(N) time where N is the total size of the table, and it gets horrifically slow when the tables start to get large. I'm not really sure why, but that has to go away.

And it has to work under D2009 and later. Can anyone provide some recommendations?

Answer 1

Another vote here for embedded Firebird (and Firebird in general)!

I've just had an awesome experience porting an Interbase 6.0 app to embedded Firebird 1.5; after a short while reading the docs, the actual conversion took literally 20 minutes and now my app runs happily in Vista and Windows 7. If you don't need multi-user support then I'd seriously look at embedded Firebird (and if you do need multi-user support then why not look at regular Firebird anyway).

It's a single file for the db and a couple of small DLLs for the engine, and it's easy to deploy, maintain and backup. There are any number of tools to help during development and the technical support in the Delphi community for IB and Firebird is second-to-none.

The SQL support is excellent with constraints, triggers and stored procedures (we also have UDFs to help augment the language - DLLs which can be written in Delphi and used as in-line functions etc in your database. Very fast, very flexible).

Your final point about performance - well Interbase was always pretty snappy anyway, and my experience with embedded Firebird thus far is that it 'screams' - really, really impressed.

Answer 2

I've used this SQLite Wrapper with good success under D2009. I had it up and running in a matter of minutes. It has indexing and very low overhead. (This one is free and you don't need anything else besides the SQLite Dll)

There is also a commercial SQLite wrapper from Delphi Inspiration and the site says that they have a free for non-commercial and educational use license as well. I haven't used that one.

I've also used the Firebird embedded, but you then also need to have connectivity components to talk to it. I have IBObjects and that's what I use for both the server and embedded versions. I have tried other free Firebird database components but haven't really found any that I like or that I felt confident in.

[EDIT]
Since the majority of people are suggesting Firebird, here are some connectivity components for Firebird that I've tried in the past or that I've heard of:

Mercury Database Objects - Free/Opensource
IBObjects - Commercial (I've bought this one myself)
FIBPlus - Commercial
Firebirds ODBC Driver - Free/Opensource
ZeosLib - Free/Opensource

Answer 3

There's some good information available in this question - SQLite3 and Firebird Embedded seem to be good options.

Answer 4

Concurrency?

I used SQLite in one (non-Delphi) project and was very happy with it.

Otherwise, I think the embedded single-file DBMS of choice for Delphi seems to be Firebird.

Answer 5

Try Advantage Database, offered by Sybase (purchased from Extended Systems)

http://marketing.ianywhere.com/forms/ADS91-30-Day

It's free if you don't need client/server or internet functionality.

The downside is it's not 100% VCL, so the VCL included statically links to DLLs.

If the app ever needs to scale, you won't have to change databases again.