Re: tuning an simple insert-statement

Pascal Ziegler <pascal.ziegler_at_bluewin.ch> wrote in

> my task is to compare the performance of a function in cache with a
> similar implementation in oracle. the insert-statements in the function
> are dynamic (not hard-coded), but for my measurement I used a hard-coded
> statement. cache needs about 70 microseconds to insert the values...
> oracle is about 3-4 times slower. i know that this measurement can't be
> very accurate and there are a lot of other things that influence my
> measurement. but the point is, (after severeal measurements) that oracle
> is always slower.

Pascal, this is IMO a pointless and meaningless comparison (and please feel free to pass this on to your lecturer). I can write a little db server that will do this in 2x (if not more) faster than Cache. How? Because I will take even more shortcuts.

Do you know *what* Oracle does with an insert transaction? Do you know what Cache does? There is a big difference between the two.

Oracle is a high-end ass-kicking database (bluntly put). It is not designed for single insert performance. Cache is designed as an OO database and optimised for exactly that.

You want single insert performance, you try "my Delphi OO database" with it bare bones parser and tmemory file stream and various other hacks. It will kick the crap out of any database system when it comes to your kind of performance comparisons (just a pity that your performance viewpoint is not shared by the real world as this would have made my database the top selling top performing db in the world, making me a billionare).

We do not measure Oracle performance ito how fast a single insert transaction is. We measure it in bulk processing of data, high volume OLTP, OLAP, scalebility, flexibility, and doing the wonderful weird things that are part and parcel of real word database processing. That is why we (and many others) use Oracle.. not for how fast it can do a single insert transation. The whole is greater than the sum of its parts.

If you are really really serious on doing this comparison... then you need to dig hard and deep into *WHAT* Oracle and Cache do when inserting in order to have any chance of comparing apples with apples. (keeping in mind that I will still beat that type of performance with a home rolled Delphi db server).

--
Billy