Re: Performance and reliability of c-tree vs. Sybase or Oracle

In article <3389CB7B.1767_at_teletrader.com>, Josef Holzer <holzer_at_teletrader.com> wrote:
>I am currently using a c-tree (Faircom) database for collecting
>pricedata of fiancial instruments. As I want to improve the reliability
>of the database as well as to increase the number of datarecords, I
>would like to move to a more robust database like Sybase or Oracle. But
>I do not want to lose performance compared to my existing c-tree
>database that also runs fine on a desktop computer.

You are going to be hard-pressed to match the performance of a procedural storage language that doesn't perform logging (I assume) using a standard relational database. The real problem is that relational buys you lots of flexibility and transparency at a cost of performance.

>The database should be able to store about 50.000 instruments that have
>updates between 1 and 10.000 records per day (some instruments even more
>often). At some peak times the database has to handle about 1000 updates
>per second.

A single connection updating 1000 records/sec in a single table is going to hammer most databases (well, without taking exotic solutions such as multiple servers with middle-ware or smart-clients). I have seen this sort of thing done, but you'll find that you can't have much else going on in the database (at least on the table) while you are performing updates.

>To handle such update frequencies I have implemented some caching as
>well as buffering: I store blocks of small price records (Instrument,
>Time, Last, Vol) in one database record.
>
>The structure of the tables are very simple: (Instrument, Time, Last,
>Vol).
>No complex queries are required. The most often used transactions are:
>1) Append new price record (up to 1000 per second)
>2) Get a large block of price records of one instrunent.
>
>Could anybody tell me, if this kind of task can be done by a Sybase or
>Oracle database with the same performance as by a c-tree database or
>even better?
>Do I have to take care of some special design considerations?

It sounds like the caching trick you are performing more-or-less breaks with the relational model (you can probably shoe-horn it in there but you will find it doesn't perform well and isn't very flexible).

One option would be to look into some of the newer object/relational technologies..many of them support a time-series datatype that would at least properly model your problem, but I really question wether any of them can keep up with the data rates that you are suggesting.

You may want to try to go with a hybrid approach...use c-tree as a staging area during bursts of high activity, then use a separate thread to migrate records into a relational database. I don't know if this fits with your problem, but its an idea.

-scott

-- 
Scott C. Gray                 gray_at_voicenet.com     "my keybard is brken"
Sybase Professional Services  scott.gray_at_sybase.com
   http://www.voicenet.com/~gray/sqsh.html