Looking for a local database for D2009+

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

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

Answer 2

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 3

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 4

Another option would be to use ADO and a microsoft access database. The only disadvantage is that the user has to have the Jet engine and MDAC installed... which most machines do. The advantage to this is that it makes upsizing to MSSQL easy. Just change the connection string to point to the SQL Server database, and make a few minor query changes.

Answer 5

I've used NexusDB for years and it's a small, reliable, flexible database. It's written in Delphi, comes with full source and can be compiled completely into your application (no DLLs to distribute) or run as a client server system.

It's hard to know whether it will meet your performance requirements but I haven't had a problem with my SQL query performance provided I was indexing the right fields. It's a one file per table product but don't let that stop you taking a look.

It's a commercial product but they offer a DCU only version that can only be used in single user/embedded applications for free.

Answer 6

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.

Answer 7

I would recommend using Postgresql as database, we use it in all projects that we work and tested it with over 4 million records in one table and worked pretty well.

Answer 8

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 9

Postgresql is very good but it is a heavy machinery it is closer to oracle so you can do very heavy apps but a bit of a pain to maintain

Firebird is fantastic embedded or not for connectivity in 2009 you can use FIB plus from devrace.com they have a trial version which just show a nag screen so if it not a commercial app it is ok.

else if it is a commercial app you can spend the 300 $ and buy it, I used also devart components for interbase/firebird and they are very good too if you want free uses zeos but you get what you pay for http://sourceforge.net/projects/zeoslib/ SQL lite is not a single file and if it is multi user it sucks

good luck Philippe Watel ([email protected])

Answer 10

I'm working at finishing up a conversion of a large application that has used BDE/Paradox for a local database and Oracle 8i for a remote db.

I'm using UniDAC from DevArt. That allows me a single component set (completely free from the old BDE) that can hit MSSQLServer as a local db and continue to hit Oracle as my remote. I have the prospect of being able to switch databases at either end much more easily now, just by changing providers.

I like this approach, and the components seem to be quite well done.

Jay (D2007)