Since my Generator App is a database related tool for data access code generation maybe it is good idea to share my knowledge and at least in my opinion – some pros and cons of the different approaches when developing information driven applications.
Databases in most cases are not only separate processes in the operating system, they are whole fat systems that do the magic with the data – index, sort, order, transform and so on, so their user can search them fast, filter, save or extract data from them. Some are easier to scale then others. Some provide the basic types, others – like Oracle Database – provide possibility to save, index, export even custom data types like XML and JSON.
In my experience I have never seen a database exposed to the public Internet directly with a HTTP, Socket, or any other interface. This from one standpoint is good idea. The database is focused on the processing of information. This principle is called separation of concerns.
On the data layer on all modern databases you provide build into the database system opportunity for you to write – application specific – code – triggers, procedures and functions. From purely data point of view this is the best option you could make. This database layer does load the data into memory or temporary space but it manages it directly and doesn’t transfer it out of the database system – out of the database application more than the information that is requested. Minimal transfer of data means in most cases better performance. I have had colleagues that write code exactly this way. What is needed for this to be delivered to non-technical user is just the user interface – desktop or web layer that just calls the appropriate functions.
A cons of this approach is that the code is a little bit harder to test and maintain. You don’t have full control on data – you handle it to the database system – and it may be good idea, it may be bad, it may be easy to scale or it may be hard to improve and optimize. It depends – which database you choose, which technologies you are using for the application or interface layer, what is your business case, how much users you are expecting and so on.
The second way of working with data is focusing the logic into the application layer. You have to realize that it is separate process of the operating system of the server. You have to minimize with limiting queries and caching methods – the size of the information and the number of queries that are executed to the database. In my professional experience I have seen several cases where sorting, filtering or / and paging were executed in the memory of the application server. This caused slow response time for the client and the administrators had to execute very frequent restarts.
An architect or a senior software developer should know the business case of his application. If you know your business case, you may do optimizations very early on that will speed up the application and handle a lot more requests with the same hardware. One such optimization is keeping the records in memory – this is if you are using server software platform like Java or .Net where it handles the requests by threads in a single process and not by creating OS level processes on every request like PHP. Sending data from the RAM is the fastest. You should limit the amount of this data according to the business case, because if the records become millions and billions the server may fail. You have hardware restrictions. The RAM memory is usually more expensive and limited in size than the persistent storage.
Other common pitfall is executing a lot of queries to the database. When you retrieve a list of records you often need to query related objects. You should avoid executing (N+1) queries in this case – one query for the list and N – one query for each record. Often these queries are hidden when using Object Relational Mapping libraries and frameworks.
But, if you have relation of type many-to-many where the child relation is just ids, I have found in my experience that it is good idea to separate to several queries. Executing two or three queries in a single request is not that much. With this approach you can avoid loading a lot of (duplicating) data into memory and avoid memory overuse.
When using the application layer as primary one, you could append your own or third party technology for caching the queries or some of the information. For example some ORMs have second level query layer build-in that could be turned on without much coding. You could use third party frameworks. I have used Apache Lucene for full text search for example. If you know your data or design it like this, you could use caching systems, noSQL databases or even dumping some big and slow query result to a file (the data of my Breathe in app is handled this way by the Germans) and serve it statically and update the file in some time interval.
