choosing database engine

[n1ghtowl]

im getting ready to write a new app. before I start though I am wanting to decide what database to use for it.

my requirments are:
1. must be a stand alone(such as Access) possibly sql server express???
2. I need the ability to do large databases.  (access = 2GB, SQL S. E=4GB)
3. Speed

I know with access my app can run on a windows system without any added installs where Sql server express needs to be installed.
All of the records will be exactly the same size

the data will be "random" bytes.(0-255)
I thought creating binary files to store this data but I am not sure on:

a) any size limits when dealing with VB6
b) single read size limit(such as can read ???K from file at a time)
c) appending speed(once the data is written to the database/file it wont be
       changed. new entrys will be added but never changed.

If any one can share their expreriance I would really appreciate it
Thanks

[si_the_geek]

There are various things that are important when considering data storage, several of which you haven't mentioned, such as:

• What is the data you are storing? (Is it just a single value of X bytes per row, or Y fields of X bytes each, or ...?)
• How much data are you intending to store (in the long term)?
• How are you getting the data? (is it in text files?)
• How are you intending to work with the data once it has been saved? (will you be searching? editing?)
• ...

As to your questions on files...
a) It depends on what you are doing. Text data tends to be limited at (I think) 2GB.
b) As above.
c) Gets noticably slower as the file size increases (with databases this doesn't happen, unless you get near the size limit, or with Access you have corruption).

I know with access my app can run on a windows system without any added installs

Not quite true.. you will probably need to install Jet and MDAC (see the 'install Access' article in our DB FAQ's for more info).

[n1ghtowl]

What is the data you are storing? (Is it just a single value of X bytes per row, or Y fields of X bytes each, or ...?)

the records each consist of 1 byte each

How are you getting the data? (is it in text files?)

the data is being created by the app as it runs.

How much data are you intending to store (in the long term)?

the amount of data may be in the line of trillions of records adventually

How are you intending to work with the data once it has been saved? (will you be searching? editing?)

the records wont be searched as they will be read in order
they will always be read in order any time the App is used record 1 will be read then 2, 3 .....

Gets noticably slower as the file size increases (with databases this doesn't happen, unless you get near the size limit, or with Access you have corruption).

does the slowing down happen even for sequential reads and appends or is it just for searching?

I know databases will slow down for searches as the size grows as it has to look through more records . as I am just reading 1 after the other im not sure if this will be an issue regardless of what I use.

This will also only need to run on W2k and XP, I dont need 9x or ME compatability or even 2003.
I hope this helps clear up what I am doing some

[szlamany]

You cannot read a database table "in order" - you must have an IDENTITY column that "auto-numbers" as rows are inserted.

So your table would have two columns - SeqNumber and your "one byte".

Inserting rows into this table would be extremely fast - that's what DB are all about. Even as you approach millions and millions of rows.

Now it could be said that storing your "bytes" in a simple binary file might be better - but there would be no sharing of that data - and you would have to read the entire file into memory just to add a new byte to the end of the pile.

Download SQL SERVER EXPRESS - it's free - you will have to test it to see how it behaves as this is not really something I think any of us has done.

I have many tables at my customer sites with millions of rows - but we never return all the rows and columns in a single recordset.

I just did a "SELECT AMTTYPE FROM PAYHISTORY_T" - this is a single character column (AMTTYPE is) - it returned 4.5 million rows in under 10 seconds (and that was into a grid-like display in QUERY ANALYZER).

[si_the_geek]

As you are expecting to get potentially trillions of bytes, files are unlikely to be enough.. but then so is SQL Server Express, as it has a 4 GB size limit (see here).

I don't know if any other "free" DBMS's would allow that kind of size.. the only one that I think might is MySQL. If there isn't one, you will need to either buy DBMS software (which could be extremely expensive), or use a method of splitting data across multiple files/databases.


To be honest as this is purely sequential data, I would personally be looking at using files. Using a database in this way will be unlikely to be as fast as files, but it would give a bit more stability (in terms of backups etc).

It would definitely be a good idea to give SQL Server Express a go tho, so that you can compare it to a file based version.