What are real and practical examples in the use of Stored Procedures?

Asked

Viewed 1,061 times

2

When participating in an interview for the position of developer, I come across a question related to Stored Procedure (SP) and at first I have theoretical knowledge on the subject, but I have never come across a practical example in the use of (SP) and was unable to set a good example in this situation due to lack of experience on the subject.

Said that conceptually the SP serves, for example, to:

  1. reduce network traffic
  2. improve database performance
  3. create scheduled tasks
  4. decrease risks
  5. create routines for processing

(source: devmedia)

Someone with more experience, could share with a real, practical example that can easily be used in a concise and objective answer on the subject?

mysql sql database stored-procedures

  • Reduce network traffic? How to call the SP (small text) instead of passing a query (larger text)? The difference in general is negligible. 2) It depends on the DBMS but in general the only thing and save a little CPU pq the SP is already compiled (tb can pioerar with the sniffing paramet, etc again many considerations. 3???? 4) SQL Inject? 5) To? Pq?

  • An exmeplo is given by the devmedia himself "When we have several applications written in different languages, or run on different platforms, but perform the same function (in BD)." But I think it would be more useful to study each of the above-mentioned topics to examine each one and then come up with more accurate doubts

  • Things to study p/vc: Parameter Sniffing, SQL Inject Attack, Pq remove business rules from the bank layer to the BL layer

  • @jean consider the question too broad? or little elaborate? or outside the scope? could give some suggestion so that I can improve it?

  • I found questions that do not add much. You should go deeper into the pq of the use (or not) of Sps. I gave up to two quick examples but there are several considerations and for smaller ones that are too extensive for one question. It would be nice to study and come with more punctual doubts (of course it is only a suggestion)

  • @jean I appreciate the suggestion, I will follow it, really wanted to be able to read about some experience in the practice of someone in the community, I imagined that this practical experience, would be well seen by all, but by the down votes I was wrong =(

  • Do not be discouraged the community is great. It is that you gave the impression that you did not research well before asking and there are people who donwvote for it. In the OR it’s the same

Show 2 more comments

2 answers

1

Two examples:

  1. Two applications a web service made in C# and a desktop application made in Delphi need to run a business rule implemented in BD. For better maintenance and management it is better that both access the same SP instead of each trying to implement a query. Thus qq change in this logic would be made by changing the SP instead of generating maintenance in two different systems. Note that the same result could be obtained if both applications could call a DLL that encapsulates these business rules (unless they run in different OS).

  2. Performance. Some routines actually get much faster if implemented directly in the BD. Although I am in favor of keeping the business rules in the business rules layer there are times that is inevitable. In this case the routine can be implemented as an SP, as the SP is stored "compiled" in the BD there can be a performance gain if this routine is called many (thousands of) times (per day/hour). Of course this depends on whether compared to ad hoc queries, non-paracterized queries, etc. Even using SP there may be considerations about SQL Inject (security) and Parameter Sniffing (performance)

1

Let’s look at the possibilities:

  1. reduce network traffic
  2. improve database performance
  3. create scheduled tasks
  4. decrease risks
  5. create routines for processing

In my conception, the main motivation would be number 5. The main idea of Stored Procedures is to encapsulate behavior together with the database, when for any reason, it is not desirable that these are modeled in the application(s) client(s). This is the main reason (but not the only one) that motivates the use of Stored Procedures.

Reasons 3 and 4 are something derived from 5. Reasons 1 and 2, although valid, are optimizations that should only be created as special cases to solve specific problems.

A real example of the use of Stored Procedure would be to create any CRUD procedure, mainly involve multiple tables and place it directly in the database. The advantage of doing this is that access to data is encapsulated and customer complexity is reduced. In the case of item 3, you can even eliminate the need for a client program.

The downside is that changes in requirements and business rules require changes in the database, which tend to be more costly and difficult than changes in customer applications.

Another advantage is that if there are multiple client programs, with the use of Stored Procedures, you centralize the business rules all in one place. However, this advantage is considered outdated today. You can use a web service (SOAP, REST or other) that holds exclusive access to the database and all other client applications would only access it through this web service. In this way, the web service would be responsible for centralizing the business rules, and no longer the database.

Cases 1 and 2 can also be advantageous in those circumstances where you make a SELECT giant with multiple tables and the client application uses this to group, total or do some other processing. You can reduce network traffic between the database server and the client application, and thereby improve performance if these operations are performed directly by the DBMS through Stored Procedures.

Browser other questions tagged mysql sql database stored-procedures

You are not signed in. Login or sign up in order to post.