Re: Embedded SQL inherently faster than others? - Mailing list pgsql-general

> -----Original Message-----
> From: Kevin [mailto:leinfidel@netscape.net]
> Sent: Tuesday, May 04, 2004 10:18 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Embedded SQL inherently faster than others?
>
>
> Hello,
>
> I've read when using embedded sql with an Oracle database,
> it's faster than other methods of database interaction, due
> to the way it interacts with the Oracle DB.

Generalizations like this are never totally true.  Certainly it can be a
very fast way.

> I've been trying to find out if this is the case for postgres as well.
>
> I'm talking specifically about the parts of the program
> interacting wtih the database, I know that obviously a C
> program is faster than most other programs, in other respects.

This is also not true.  The dominant time absorbed by a program is a
function of the algorithms employed.  A java program that is O(n) will
stomp the stuffing's out of an assembly program that is O(N^2).  C will
usually be faster than interpreted languages, but only by a small
constant factor.  The speed difference between C, C++ and Fortran (all
compiled languages) is negligible.  If you use some of the advanced
object techniques like RTTI in C++ then you will have a small speed
penalty for the benefit of improved abstraction abilities.

> I'm looking for absolute maximum database result speed and if
> that means writing something in C and embedded SQL, versus
> Perl using DBI or whatever...that's fine.

Benchmark your application.  90% of the time will be in ten percent of
the code.  Chances are good that adding an index or optimizing a nested
loop or some simple trick will be the thing that makes your program
monumentally faster.  It may be that you write 95% of your application
in Perl and 5% as a compiled Embedded SQL program.  But premature
optimization is a root of many evil things.

If you want your PostgreSQL application to be fast, there are a few
things you should consider.

HARDWARE:
First, you can buy very fast machines for a very reasonable price.
Price out an Athlon 64 bit system with a striped raid array and several
gigs of ram.  Let's suppose that it is $6000 when you have all the bells
and whistles attached.  That sounds like a lot, but look at how few
hours will be needed to cover the cost, if we billed at only $50/hour
(which is a ridiculously low consulting rate).  120 hours is only 3
weeks time.  The standard calculation for programming rates for
debugged, documented and delivered code is ten debugged lines per hour.
(That includes everything from the meetings and requirements gathering
all the way through documentation and training).  Any way, that means
the machine costs the same as 1200 lines of code.  People who buy
supercomputers aren't so dumb after all.

SYSTEM TUNING:
Often, you can improve a system quite a bit by tuning it.
Read articles like these:
http://www.phpbuilder.com/columns/smith20010821.php3?print_mode=1
http://www.argudo.org/postgresql/soft-tuning.php
http://cbbrowne.com/info/postgresql.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.linuxjournal.com/article.php?sid=4791
http://candle.pha.pa.us/main/writings/pgsql/performance.pdf
http://forums.devshed.com/t84334/s.html
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php

OPTIMIZING PROCEDURES:
Now, let's suppose that you have bought the hardware and tuned your
database and your application is not fast enough.  Here is what you
should do:
Run gprof or some other profiler on each part of the system to find out
where the time is going.  When you have found the hot spots, then
consider optimizing those only.
You should have definite performance goals.  Do not optimize merely for
the sake of optimization.  It tends to obfuscate code when done poorly
and 80% of software cost is maintenance.  So in performing optimization,
it is possible that you are creating a system that will be more
expensive to maintain.
To optimize, here are the correct steps:
1.  Profile to find out what is slow
2.  Look for alternative algorithms.  Little tweaky things like compiled
code verses interpreted or rewriting a recursive algorithm into the same
algorithm with iteration only result in a small constant speedup.  A
fundamental algorithm change (or something as simple as creating a
unique index) can result in monumental speedups of factors of hundreds
or thousands.
3.  Once the problem is clearly identified, get the input from some of
your peers on it.  Pair programming is one of the really good ideas that
has come out of extreme programming camps.
4.  Benchmark your proposed changes to see if you really do get the
level of speedup that you hope.  If you reach your written performance
goal, then STOP.  It's kind of fun to make things go faster but it is
easy to get carried away.
5.  If it is still not fast enough, examine some alternatives.  What
about a 4 CPU AMD 64 bit machine with 16 gigs of ram?  What about 15K
RPM Ultra320 SCSI drives in a striped array?  What about hiring an
expert consultant?  What about reading up on algorithms for the problem
at hand?

PROGRAMMING RESOURCES:
There are other things to think about also.  Suppose I deliver a nice,
fast C solution to a client, but they have no expert C programming
resources in their organization.  How are they going to maintain it?
Same for Java or C# or Perl or PHP or whatever.  We should look at the
resources available to the client and match those to our solution to
some degree.