What are the benefits of using many complex stored procedures

Question

For a typical 3-story application, I saw that in many cases they use many complex stored procedures in the database. I cannot benefit from this approach. In my personal understanding, the following flaws in this approach:

• Transactions become rude.
• Business logic goes into the database.
• Many calculations are performed on the database server, and not on the application server. Meanwhile, the database still has to do its original job: maintain the data. The database server can become a bottleneck.

I can guess that there may be 2 advantages:

• Change business logic without compilation. But SP is much harder to maintain and test than Java / C # code.
• Reduce the number of database connections. However, in the general case, the database bottleneck is the io hard drive, not the network one.

Can someone tell me about the benefits of using a large number of stored procedures, and not that the work was done at the level of business logic?

Answer 1

In principle, the advantage No. 2 of your list of problems is that if you process a lot in the database backend, then it is processed there and does not depend on the application accessing the database.

- -, . , - .. - .

- - , , , script, Excel , - , .

procs BLL .

, Views for read Stored Procs /, . , , , .

proc - , - .

, : , . , procs - , - procs , .

Answer 2

.

, .

- , -, () .

FYI, "/" , , , .

Answer 3

appServer sqlServer - . , . , . , - - 3 A. 10 B A. 100 C B. 5 C . appServer sqlServer appServer. , , 5 appServer, 1) , 2) .

Answer 4

. . , . , . -, .

:

• - . , .
• -, : .
• SQL    .    ,    . ,    ,    ,   ,   .

: PL/SQL, T-SQL .. , . , , , .

Answer 5

, - Db, .

SP db , , (, ), , ( ) , db.

- " " db sps . , , .

Answer 6

, . , BL , . Driven. SPROC. , -, -, .

, SPROC , . , . SPROC, db, .

Answer 7

, . -/ . , .

, ,

• .
• , , /, . SP , DB .
• , . , Oracle.

, , DB - -.

Answer 8

.

, , , , . ( , 100 , ) ( / , / ).

, ( - ..), , , .

, , SQL ( , ) , / . , , , .

As in most cases, a reasonable compromise / balance is needed. Stored procedures should be used enough to increase efficiency and security, but you do not want your server to become immodest.

Answer 9

"There are the following flaws in this approach: ... Business logic is included in the database.

In accordance with the "logic of business processes" you mean "compliance with business rules", the DBMS EXACTLY where the "business logic" belongs.