I recently went for an interview with a software company that claims to write enterprise level software. During the interview, the interviewer remarked that its better to use the database simply to store the data. No stored procedures or triggers (or rather they should be kept to an absolute minimum).
Now I thought that he mentioned this purely from a code maintenance point of view (as described in this PSE question, thanks @gnat), however he mentioned that it was for performance reasons. I know that the company uses C# and MSSQL server for a backend. Is his statement valid and how would the performance be increased by keeping all code logic off the database?
5
Performance does neither increase or decrease because “you put most of the code into database” or because “you keep the code out of the database”. The key point is to put the right parts of the code into the database (or to keep them out). Parts which helps to reduce the network traffic might be a good fit for stored procedures. Parts which do heavy calculations might be better kept out of the database (since otherwise you risk to make your database server a processing bottleneck).
For the remaining code (which is typically >90% for an enterprise system), a decision based on “what is better maintainable and evolvable” makes the most sense. And even that may be opionated.
0
Generally if you push more code to DB and if you do it right, it should result in performance increase. And I don’t see why the interviewer claim its other way round.
Personally I would not put any code into DB simply due to ease of maintenance and ease of unit testing. In most of the applications performance improvement due to moving logic into DB does not worth the headaches followed.
The main reason for not having code in the database is testability and maintenance. It is relatively hard to test the database code and it is a kind of hidden logic, which will become hard to maintain as you logic is spread all over and regress especially in the maintenance phase of your project.
7