7

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?

Community
  • 1
Eminem
  • 247
  • 1
  • 3
  • 8
  • 6
    The statement is not valid. The opposite is true. Putting code in the database is to be avoided like the plague for a number of very good reasons, but performance is not one of them. Performance (or misguided worries thereof) is usually the main reason why people do themselves the disservice of putting code in the database. – Mike Nakis Apr 30 '15 at 06:58
  • 1
    @MikeNakis: saying "the opposite is true" is as wrong as the original statement. – Doc Brown Apr 30 '15 at 07:24
  • @DocBrown would you be satisfied if I had written "The opposite is true in the vast majority of cases"? Remember, the statement is: "no stored procedures or triggers, for performance reasons". – Mike Nakis Apr 30 '15 at 07:28
  • 1
    @MikeNakis: in the question I read the statement as "stored procedures [...] should be kept to an absolute minimum for performance reasons", and the opposite of that could be interpreted as "stored procedures [...] should be kept to a maximum for performance reasons", or just "stored procedures will always increase the performance" (I am pretty sure that is not what you meant, but it is like other people could read it ;-)). – Doc Brown Apr 30 '15 at 09:17
  • @DocBrown I see. C-:= – Mike Nakis Apr 30 '15 at 09:43

3 Answers3

10

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.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
3

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.

Low Flying Pelican
  • 1,602
  • 9
  • 13
2

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.

Sajad Deyargaroo
  • 944
  • 6
  • 5
  • 1
    @sajad-Out of curiosity, how would you test the code if it were not moved to the database? – Scott Nimrod Apr 30 '15 at 08:27
  • There are lot of testing frameworks available e.g. for .Net you can use MSTest, NUnit, etc. – Sajad Deyargaroo Apr 30 '15 at 14:08
  • 1
    Yes, but can you provide me an example of unit testing a query? I am just curious how you can't unit test a query in a stored procedure but somehow you can unit test a query in .NET. How would that be possible based on your suggestion? – Scott Nimrod Apr 30 '15 at 14:24
  • We are not talking about queries here. We are talking about logic in stored procedures and triggers. You don't create a stored procedure or trigger for a query for that you have views. – Sajad Deyargaroo Apr 30 '15 at 14:46
  • Okay. Then what type of "logic" would I extract from a database that does not either Create, Read, Update, or Delete, that I could unit test in my .NET solution? – Scott Nimrod Apr 30 '15 at 15:54
  • That is limited only by your imagination. You can create a really big procedure and put all kinds of logic in it. May be you should try to find some real world/legacy app procedures, that will get an idea what I am talking about. – Sajad Deyargaroo Apr 30 '15 at 23:47
  • It is not hard to test code in the database. – Craig Tullis May 05 '15 at 06:20