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?