tuning an simple insert-statement

Discussion:

(too old to reply)

Pascal Ziegler

2003-09-06 07:50:58 UTC

hi,

i have to insert several values with a simple insert-statement.

e.g.

INSERT INTO xy VALUES (1, 2, 3, 4); -- really simple ;-)

(the table has just a primary-key index!!)

i did this with oracle 9i and cache from intersystem! cache
was about 5 times faster than oracle!! is this just
the way it is or can i improve the performance of oracle!??
and how?

thanks
- pascal

Ron Reidy

2003-09-06 11:36:29 UTC

Permalink

What is slow? How long does it take? Are you performing 100s of 1000s
of these hard-coded things? Are there triggers firing? have you traced
a session and determined what waits if any are occuring?

What expectations do you have for speed?

Post by Pascal Ziegler
hi,
i have to insert several values with a simple insert-statement.
e.g.
INSERT INTO xy VALUES (1, 2, 3, 4); -- really simple ;-)
(the table has just a primary-key index!!)
i did this with oracle 9i and cache from intersystem! cache
was about 5 times faster than oracle!! is this just
the way it is or can i improve the performance of oracle!??
and how?
thanks
- pascal

--
Ron Reidy
Oracle DBA

Pascal Ziegler

2003-09-06 14:47:25 UTC

Permalink

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. i am a oracle-rookie, i have to do this comparison for
my degree disertation (university of zurich). now i'm looking for
possibilities to improve the performance of oracle. i'm not looking for
the final solution... but just points where i can start to tune my oracle.

what else:
- there are no triggers firing.
- i havent traced my function... but I thought that my statement is not
that complex that i can find the problem with tracing... (?)

by the way: is this a right presumption: I can't improve the performance
of an insert-statement, when i build a data-cartridge (with an own
index-structure). i think with an index it even gets slower...?

thank you for the help! :-)

- pascal

Post by Ron Reidy
What is slow? How long does it take? Are you performing 100s of 1000s
of these hard-coded things? Are there triggers firing? have you traced
a session and determined what waits if any are occuring?
What expectations do you have for speed?

andrewst

2003-09-06 15:08:06 UTC

Permalink

Originally posted by Pascal Ziegler

Post by Pascal Ziegler
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. i am a oracle-rookie, i have to do this
comparison for
my degree disertation (university of zurich). now i'm looking for
possibilities to improve the performance of oracle. i'm not
looking for
the final solution... but just points where i can start to tune my oracle.
- there are no triggers firing.
- i havent traced my function... but I thought that my statement is not
that complex that i can find the problem with tracing... (?)
by the way: is this a right presumption: I can't improve the
performance
of an insert-statement, when i build a data-cartridge (with an own
index-structure). i think with an index it even gets slower...?
thank you for the help! :-)
- pascal

Post by Ron Reidy
What is slow? How long does it take? Are you performing 100s

of 1000s

Post by Ron Reidy
of these hard-coded things? Are there triggers firing? have

you traced

Post by Ron Reidy
a session and determined what waits if any are occuring?
What expectations do you have for speed?

Post by Pascal Ziegler
hi,
i have to insert several values with a simple insert-

statement.

Post by Ron Reidy

Post by Pascal Ziegler
e.g.
INSERT INTO xy VALUES (1, 2, 3, 4); -- really simple ;-)
(the table has just a primary-key index!!)
i did this with oracle 9i and cache from intersystem!

cache

Post by Ron Reidy

Post by Pascal Ziegler
was about 5 times faster than oracle!! is this just
the way it is or can i improve the performance of

oracle!??

Post by Ron Reidy

Post by Pascal Ziegler
and how?
thanks
- pascal

Use bind variables

--
Posted via http://dbforums.com

Jim Kennedy

2003-09-06 16:30:04 UTC

Permalink

Post by andrewst
Originally posted by Pascal Ziegler

Post by Pascal Ziegler
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. i am a oracle-rookie, i have to do this
comparison for
my degree disertation (university of zurich). now i'm looking for
possibilities to improve the performance of oracle. i'm not
looking for
the final solution... but just points where i can start to tune my oracle.
- there are no triggers firing.
- i havent traced my function... but I thought that my statement is not
that complex that i can find the problem with tracing... (?)
by the way: is this a right presumption: I can't improve the
performance
of an insert-statement, when i build a data-cartridge (with an own
index-structure). i think with an index it even gets slower...?
thank you for the help! :-)
- pascal

Post by Ron Reidy
What is slow? How long does it take? Are you performing 100s

of 1000s

Post by Ron Reidy
of these hard-coded things? Are there triggers firing? have

you traced

Post by Ron Reidy
a session and determined what waits if any are occuring?
What expectations do you have for speed?

Post by Pascal Ziegler
hi,
i have to insert several values with a simple insert-

statement.

Post by Ron Reidy

Post by Pascal Ziegler
e.g.
INSERT INTO xy VALUES (1, 2, 3, 4); -- really simple ;-)
(the table has just a primary-key index!!)
i did this with oracle 9i and cache from intersystem!

cache

Post by Ron Reidy

Post by Pascal Ziegler
was about 5 times faster than oracle!! is this just
the way it is or can i improve the performance of

oracle!??

Post by Ron Reidy

Post by Pascal Ziegler
and how?
thanks
- pascal

Use bind variables
--
Posted via http://dbforums.com

Also, think about it, Oracle supports very high rates of transactions -
look at tpc-c benchmarks. Do you see cache in the list? (no, wonder why)
To measure a system just using single insert as a benchmark is not a very
good benchmark. How robust is cache at handling transactions or recovering
from a system crash?

Jim

Pascal

2003-10-08 10:16:43 UTC

Permalink

hi billy.

I asked a question about performance of oracle in comparison with cache
long time ago and you gave me good hints where to go further with my
analyses.

now I have a rather technical question to ask you:
you told me that you could write a little fast database by taking more
shortcuts. what do you understand with shortcuts? I have do answer the
question why oracle is slower than other databases (focus: my simple
application). what are the technical differences in the implementation
of these db's? I'm not looking for details, but rather for concepts.
perhaps you can give me some points where to start with my analysis.
perhaps you know a good book or a good homepage!?

thank you for your help! :-)

- pascal

Pascal Ziegler wrote in

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

Billy Verreynne

2003-10-10 12:37:35 UTC

Permalink

Post by Pascal
you told me that you could write a little fast database by taking more
shortcuts. what do you understand with shortcuts?

Eliminating overheads. Ignoring scalebility. Ignoring crash recovery.
Simply put - streaming persistant objects to disk and memory using
very fast and very basic file and memory streams. A million objects
can be loaded (and constructed) like that (yep, prototype code was
written and benchmarked) within seconds.

Post by Pascal
I have do answer the question why oracle is slower than other databases
(focus: my simple application).

It is not really slower IMO. Not if you are doing it correctly and not
if you are making the same comparisons.

Post by Pascal
what are the technical differences in the implementation
of these db's? I'm not looking for details, but rather for concepts.

May I suggest the Oracle Concepts manual at http://tahiti.oracle.com ?
- it will provide a much better (and more accurate) explaination.

Post by Pascal
perhaps you can give me some points where to start with my analysis.

IMO that's pretty difficult. Cache is an object database. Oracle is
object relational data processing platform. It is far beyond a
database. It incorporates operating system features and application
server features.

The speed of a single transaction is irrelevant. What is relevant is
the speed of 10 million transactions. How well the data processing
platform (aka database) can scale.

There's a Linux cluster used within EA Arts (or one of its
subsidiaries). It uses Oracle RAC. It runs far over 20,000+ SQL
*statements* a second. Not transactions - *statements*.

On an older K-class HP-UX platform, I've seen 1.2+ billion rows
processed (reading over 1 Terrabyte of data) within 13 hours. You work
out the maths for that per second. :-)

Can something like Cache scale as well? Can it *really* handle such
huge loads without showing strains and cracks?

OTOH though, if you are running a small database setup, do you need
that?

Horses for courses. Which is why you cannot simply compare product A
with product B and say product B is better. Never mind taking a
*single* specific feature from product A and compare that with product
B.

The comparison must be qualified. Must have the same baseline
criteria. And that is IMO not possible. And even if you have an ideal
closed system to do that comparison, the results will have no real
world meaning or application.

What you can do is look at the technical aspects of said feature. Then
comment on that from a pure technical perspective (not comment, not
compare ;-).

Post by Pascal
perhaps you know a good book or a good homepage!?

Start with tahiti.oracle.com and look at the Oracle Concepts manual.

--
Billy

Daniel Morgan

2003-09-06 17:29:15 UTC

Permalink

What possible relationship can there be between a cache and an insert?

If you want to improve the speed of inserts ...

1. Faster drives and controllers
2. Spread I/O out over more physical disks with stripping
3. Optimize checkpointing
4. Use bulk binding
5. Use the APPEND hint

I have no idea what "intersystem" is ... but if you have a 500%
difference in speed between system X and system Y. Look for the real
reason. This is almost undoubtedly not it.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
***@x.washington.edu
(replace 'x' with a 'u' to reply)

Alan

2003-09-12 15:42:16 UTC

Permalink

You can't measure something withour affecting it. That being true, different
systems may have different means of implementing a measurement. Bottom line
is what you may be seeing is a difference in measurement techniqies rather
than a difference in insert perfromance. Also, your test is faulty. You
can't project that because system A took twice as long as system B to do 10
inserts that it will take twice as long to do 10,000 inserts. A may be
faster than B.

As I have always said, the only meaningful test (to you) is a test with the
actual data you will use under the typical load and environment in which you
will be using it.