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?
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.