> > i have heard somewhere that writing a stored procedure, is much better than > > firing a sql query(such as select * from table_name) onto the database. > > is it true and if yes how? stored procedures (functions on postgresql) eliminate a lot of overhead. they also provide a lot of covenience of transactions manually opening and closing them. There are also security and design benefits. > This isn't going to be true most of the time, I think. Write SQL where you > can, and where you can't (because you can't express something in SQL), write stored procedures allow the application to do complex things in the database without having complex sql inside. it also allows multiple application technologies to access the database without reimplementing the query in 10 differnet places. what happens when that query changes? > a procedure. There are places where using a stored procedure can be more > efficient, but I think starting with SQL, benchmarking and testing, and then > determining what queries need special attention is the best way to go at the > beginning. > > > also i want to know that is the performnance in java slower as compared to > > cpp, given that the same things is being done. > > Java and cpp performance are not really related to postgresql performance. > You will probably need to ask that on another list. There are many other > reasons to choose one language over another besides speed (in fact, I think > speed is probably not the first thing to think about when choosing a > language). language choice is important such that it may expose none, some, or all, of the internal libpq api (or implement it's own version of the client side protocol). cpp can often be faster because you can make very thin wrappers over the libpq calls (STL is ideal for this). stored procedures, particularly pl/sql funtions, have the enormous advantage in that queries are first class objects inside the procedural code....so you can x := 1 + 3; select * from t where id = x; without nasty string concatination or learning a whole API just to read and write data to the database. Another advantage is that the can also executed by the query engine so you can do: select outstanding_account_balance(account) from account where... these two advantages alone mean huge reductions in code along with corresponding savings in development and real dollars. It has been more or less proven that functional, declaritive style coding has less errors and is more reliable than mixed sql/procedural applciation code given developers with equal skill. thus, I would argue the opposite, use procedures everywhere, keep application code to an absolute minumum, because it is expensive to write and changes frequently. The main reason not to code your logic in the database is to keep your code portable across multiple databases. Around year 2000 I realized pg is the only database I would ever want to use again in my professional career. merlin
