A few years ago, I had a problem.  A database routine for processing bulk payments for LedgerSMB that was humming along in my test cases was failing when tested under load with real data prior to implementation.  Some testing showed that while it ran fine with small numbers of inputs, it eventually slowed way down as the number of inputs rose.  Andrew Sullivan, who was also on the project through a different company, suggested it was something called a "cache miss" because I was running through a loop, inserting two rows, and then updating a different table.    Although he quickly abandoned that theory, his initial assessment was dead right and within a day I had a fix to the problem.

I learned there why database and application programming are so different.  The same mistake I made I have seen in contributions by many other developers.  I have therefore concluded that it is a universal mistake.

When we program applications, we think in terms of instructions.  We break a problem down into instructions, and we order those to get the right result.  We usually focus on simpler elegant code over more complex code and therefore tend to try to make things as simple as possible.  This was what I was doing and it failed miserably.  I have also had the misfortune of looking through several hundred line stored procedures that were obviously written by application engineers, and where the ability to think in db-land was missing.  Such stored procedures are not maintainable and usually contain a lot of hidden bugs.

In database-level routines, however, it is extremely important to think in set operations.  A clear, complex query with both be easier to read and easier to maintain than a set of smaller simpler queries.  The database is an important math engine.  Use it as one.  Consequently simplicity and elegance in a stored procedure is often the ability to express the most complicated operations in the fewest database queries possible.   Because SQL queries are highly structured (SELECT queries involve a column list, followed by a set of tables linked by JOIN operations, followed by filter operations, etc), it is very easy to track down problems, whether those are bugs or performance issues.

With smaller operations, like I had been doing, the database cannot optimize the operation as a whole, and one may have to contend with problems caching results in an effective way, or the like.  Worse, the operations are not linked together in a predictable way and therefore problems are harder to track down when they involve specific inputs or concurrent operations.

The way we think when we code SQL (including PL/PGSQL) and when we code Perl, Python, or C is just fundamentally different.

Source:http://ledgersmbdev.blogspot.com/2012/02/application-vs-database-programming.html