I would like to know what are the pros and cons to two apparently valid philosophies regarding the programming of an application which makes extensive use of a database.
The first philosophy is to put as much logic inside the SQL used to query the database which will elaborate the various tables and return an almost ready result to the program.
The second philosophy is to query only the strict necessary data from database and elaborate that data with the programming language used.
The differences I came with so far are these:
I) Querying the database yields better performance and less data has to be copied to memory.
II) Elaborating the data with a programming language may be easier than using complex queries in SQL.
III) Elaborating data in the programming language may be more portable than using a specific SQL dialect, after all iteration and arrays of String, Int and Dates can be easily ported to different languages, a difference in SQL dialect on a very complex SQL query may be much less straight-forward.
Can someone please give some insight on the topic?