Thread: 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. 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. 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. thanks for any feedback. Kevin
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Kevin > Sent: 4. maí 2004 17:18 > 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. > Is it? If we are talking about embeded SQL into the C/C++ then I think it is tranlslated into OCI(Oracle Call Interface) calls which are the same API used by not embeded SQL. May be the efficiancy comes in more efficient OCI use when automatically generated than manually written OCI calls, for example, less parse calls? If this is SQLJ(Java) embeded SQL then it is cleary translated into normal JDBC calls - again, the no "other way" to interact the database. If this is PL/SQL stored procedures then yes, appearently the interaction bypasses the whole network layer and probably much more. DB2, as far as I know, uses some precompiled SQL files which may make a difference if used or not used. Oracle is using so called "shared pool" memory region for this reason - it's where Oracle stores compiled SQL. Client API submits SQL strings just literally, db engine searches them in the shared pool (the performance of this search is a big issue for Oracle) - if not found then it makes a big deal(time) to compile. This said we see that what ever Oracle API is used: SQL strings goes to db engine from the client API. Regards, Laimis
> -----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.
* lnd@hnit.is (lnd@hnit.is) wrote:
> DB2, as far as I know, uses some precompiled SQL files which may make a
> difference if used or not used.
Is it much of one if you're using prepared statements? I guess it
depends on how many different queries you do.
Stephen
Attachment
Dann Corbit snip: >>-----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'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... And if you don't have a method for reusing connections or doing some connection pooling then for short running sql, much of the time will be spent on establishing a connection.
> -----Original Message----- > From: Rob [mailto:pgadmin@itsbeen.sent.com] > Sent: Wednesday, May 05, 2004 12:25 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Embedded SQL inherently faster than others? > > > Dann Corbit snip: > >>-----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'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... > > And if you don't have a method for reusing connections or > doing some connection pooling then for short running sql, > much of the time will be spent on establishing a connection. Connection time is never a problem for me. So (as usual) YMMV. Besides connection pooling, other obvious answers would include reusing the existing connections for queries. Consider a web server. If the people who are attaching do not need special rights to data (e.g. all users of the application are at the same level) then the web server can launch a collection of database engines and maintain a thread pool for attaching users that compete for those resources. There is always a way around performance problems. And why all the sudden concern about connection times? Are lots of people actually having a problem with it or is it some angels dancing on the heads of pins kind of a thing?
On Wed, May 05, 2004 at 04:35:33PM -0000, lnd@hnit.is wrote: > DB2, as far as I know, uses some precompiled SQL files which may make a > difference if used or not used. Unless it's changed in version 8, the 'precompiled SQL files' are just generated C code that's turned into C stored procedures. These C stored procedures talk to the database through their normal C interface, so the only benefit you get is that the stored procs are run in the same process as the database. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"