Which database should I use for best performance

Question

Hello,

I am working in Visual Studio 2005, .NET 2.0.

I need to write an application, which listens on COM port and saves incoming data to a database. Main feature: save incoming data (series of 13-digits numbers), if this number allready exists, then mark it as double. For example, there could be these records in database:

0000000000001 OK
0000000000002 OK
0000000000002 Double
0000000000003 OK
0000000000004 OK

I could use SQL database, but I don't know if it is fast enough... Database should be able to store up to 10.000.000 records and write up to 100 records per minute (so it needs to check 100 times per minute if this record allready exists).

Which database should I use? Maybe the whole database would need to be in RAM. Where could I learn more about this?

Thanks

Answer 1

It sounds to me like you need to keep an in-memory representation of your data (that is, write classes that contain it) and update/check toward that in-memory representation rather than against a "real" database.

If you need to store the data long-term, you could save in batches on a regular basis, preferrably during a break in inflow of new data from the COM ports but if that's not going to happen maybe just with a separate thread that saves stuff to the DB while your original thread keeps collecting new data.

Answer 2

If you believe (I don't!) that the operations are too fast for a normal DB to work with, you can use an IMDB (in-memory database), like :

• Oracle TimesTen In-Memory Database 11g.
• IBM solidDB.
• SQLite.
• HSQLDB.

Answer 3

The performance level you're aiming at isn't going to tax any database system severely.

Microsoft SQL Server, Oracle, Informix, MySQL, Postgres, Sybase will all handle this situation easily.

The key is to design your database and memory structures well.

A naiive approach will fail rapidly - but a bit of smart design (using the right table structures, indexes and queries) will work well.

Now, if you wanted to lodge 100 records per second then you'd be talking a challenge.

Answer 4

I think that on decent hardware any database server would be able to handle 100 updates per minute (less then 2 updates per second).

I'd recommend using MySQL as it supports atomic updates that preclude the requirement to use transaction for the "check if exists then update" you need. For example:

INSERT INTO data (number, status) VALUES ('00000001', 'OK') ON DUPLICATE KEY UPDATE status = 'DOUBLE';

There are other SQL servers that support this feature, so the best recommendation is going with what you know (and don't forget to put a unique index on the number column).

Answer 5

You don't need a DB, you need a hash table. you could also use a sorted tree, it will be more space efficient. regarding the sql server, you are not frighting no SQL with a 100 longs a minute...

Answer 6

If all you need is a key value pair, then why not look at a distributed cache?

Velocity or one of the .net memcache ports should work well. The advantage with this approach is that it will scale if your needs expand, and no db server setup/maintenance or licensing costs.

Answer 7

I agree with Bevan.

For further consideration:

• Using a stored procedure so the sql is optimized.
• Using prepared statements.
• When are you reading from the database? You may start off just inserting, and then add in features (reports? etc) for reading, and you'll start dealing with locks, which can kill your performance if you don't plan for it.