> -----Original Message----- > From: Kevin [mailto:leinfidel@netscape.net] > Sent: Tuesday, May 04, 2004 10:18 AM > To: pgsql-general@postgresql.org > Subject: 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. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
