Single Stored procedure call or multiple database calls?

Question

I am developing a MVC application where the view can fetch data using a stored procedure call which has multiple different queries or calling all of them individually directly from model. I am really confused about which approach makes a good practice ?

Thanks

Answer 1

I would go for a single call to the stored procedure in almost all cases (I will talk of the exception at the end).

Reasons:

• Whoever maintains the stored procedure may introduce some extra logic (be it for performance reasons or business reasons) that you will have to either duplicate in your own direct calls, or - even worse - might miss altogether due to miscommunication with whoever is maintaining the stored procedure. (Even if you maintain both, you will have to spend effort on duplication). This is #1 reason: using a dedicated interface ensures correctness and avoid duplication
• Every time you interact with your DB you incur in a small (but not null) overhead to open the connection (or retrieve it from a pool), marshalling and unmarshalling data over the wire, network latency and so on. Having a single entry point (your Stored Procedure) will amortize these better.
• It is possible (but it really depends on a lot of different factors so it is not a guarantee of anything) that the DB engine can further optimize its workload by having everything in a single transaction context. I.e. maybe it issues two consecutive queries which are similar enough that a some of the indexes/records are buffered in the DB cache and so can be accessed faster by the second query).

Possible exception: your application has a sort of "zooming" process where you load first the header of a multirecord structure, and need the lower level details only when/if the user requires those. In this case it might be better to access these on the fly. I would still prefer the previous solution unless I can prove (i.e. testing with realistic loads) that the detail records are big enough to make this a burden on the front-end. Please resist the temptation to just decide that it is more efficient this way unless you have hard data to backup your "insight". Most of the time this will prove to be a mistake.