multiple Stored procedures dependent on one another. Should be done in code or Stored procedure?

I was trying to decide whether or not I should try to consolidate my stored procedures in my database or do it code. Basically, I have one table (Analytics) that has a foriegn key to the other table (Urls). When a user accesses certain pages, the first table will try to create the entry. In the stored procedure for this table (Analytics), if the row already exists, it will update otherwise, create the row. I was thinking of sending back the ID and then updating the other table. OR I could update the other table in the same stored procedure. There will also be 2 other tables that in the same page hit will do something similar.

I'm conflicted because my object oriented side wants each stored procedure to only do one thing, but I know that this will be happening on each page and want to get what I can out of optimization if possible.

What are thoughts on the speed difference on multiple calls versus one call to the Database. is it really going to cause an issue?

Answers


What are thoughts on the speed difference on multiple calls versus one call to the Database. is it really going to cause an issue?

Yes, it certainly could.

For each database call there are connection overheads, network IO overheads and extra work for the database server and client - these are slow. Additionally, structuring stored procedures in such a way can easily lead you into an N+1 problem.

In general, if you can structure things so that you do as much as possible in one query, your database and application would be faster and more responsive, as expensive and slow resource usage is kept to a minimum.


Need Your Help

Nested signup form does not render correctly after invalid submission

ruby-on-rails ruby ruby-on-rails-4

I have a combined/nested signup form, which registers a new organization plus a member (two models with a 1:many relationship).

Chrome & Opera radio box-shadow is square - Any workaround?

html css google-chrome cross-browser opera

Chrome and opera both have bug in the styling of radio inputs, so that the shadow appears around a square box, rather than the native element.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.