Database suggestion requested

[sandy771]

I have a problem and am after suggestions as to how I might best code a solution.

First off I use Codegear/Borland Builder 2007 and the Developer Express Quantum grid pack

My code reads in a large table, some 5 Million rows, which are a combination of numeric and string fields. My application is basically a browser and the user needs to be able to scroll backwards and forwards through the data set, sort on every column and perform free text searches on the text columns (the text data could be anything from a few characters to about 2K).

The table, which starts life as a CSV, is about 1.5 – 2GB in size but could conceivably be larger, so loading the whole table into memory is not an option.

So my questions are – which is the best (free) database technology (MySQL, SQLExpress, any of the Borland stuff, etc.) to deal with an application such as this. I have tried MySQL with MyDAC and it seems that when I sort on a particular column that the whole database is loaded and my app hangs (it works OK with a smaller data set of about 800K rows).

[Antaeus]

To avoid loading all rows into the memory you need to restrict data to be transferred from the server by adding conditions to the WHERE clause of your query. The more exact conditions you use, the less memory is utilized.

As far as I know, MySQL is not a completely freeware database server. You can consult its license agreement for more details.
You can also take a look at the Firebird + IBDAC combination.

[sandy771]

Thanks Antaeus

I understand that - but I thought that this was a function of the grid component.

I want my users to be able to scroll at will through the entire table and not to have to specify a subset.

I'll loom at the firebird IBDAC stuff