How many database applications should be stored in stored procedures?

Question

I am writing a second interface to a database application to get around some of the shortcomings of the original interface. Unfortunately, if I create new entries, the expected audit trail entries will not be generated. Of course, it would be nice if the database design worked with such details in table triggers or provided a stored procedure API for operations such as inserting new records into these tables.

Should applications be designed this way in general? How many database applications should be stored in stored procedures?

Answer 1

Our teams have a rule - any data coming into or out of the database must go through a stored procedure. We even went so far as to create restrictions on our data access components to enforce this.

As for other things, such as accounting, audit trails, etc., we also put them in stored procedures. Triggers are convenient, but we found that for the purpose of audit logs related to who, what, and when updates, not all the data we want is available for the trigger. The only time we ever used triggers was to keep a complete change history of each record in the table, but even then, looking back, it caused maintenance problems, and sproc would be better for us.

Answer 2

It totally depends on your environment. The answer to the question is really not a coding problem, or even an analysis problem, but a business solution.

If your database supports only one application and is rather rigidly integrated with it, then for reasons of flexibility it is better to put your logic in your application program. In these circumstances, treating a database simply as a simple data repository using common functionality loses little and gains flexibility — with providers, deployment, deployment, and more — and many of the purist arguments that make the crowd of “data databases” defiantly true.

On the other hand, if you are working with a corporate database, which can usually be identified using several access paths, it is very important to secure as much as possible. At the very least, all relevant restrictions should be included, and, if possible, access to data should only be through views and procedures. Whining programmers should be ignored in these cases, because ...

• With a corporate database, an asset is valuable, and invalid data or actions can have business-threatening consequences. Your main concern is to protect the business, not how convenient access is for your coders.
• Such databases, by definition, gain access to several applications. You need to use the abstraction offered by the stored procedures so that the database can be modified when application A is updated and you do not have the resource to update application B.
• In the same way, encapsulating business logic in SP rather than in application code makes it possible to more easily and reliably implement changes in such logic in business than if such logic were embedded in application code. For example, if a tax calculation changes its operation less and more reliable, if the calculation has to be changed in one SP than several applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data, where it is unique - therefore, if you have a specialized application, then the logic for this application can be implemented in this application, but the logic is more widely applicable business should be implemented in a joint venture.

In your case, you have several applications accessing the same database, so you need to move the logic and audit functions from the application level to the database. I myself usually added audit functions to the database itself, because if I was ever asked to investigate the problem of fraud (and this happened more than once), I just feel like I can stand up and be confident in my findings with much more than if if it were at the application level - there are simply fewer opportunities for reservations.

Encoders that plunge into religious wars for using or not SP, as a rule, worked only in one environment or another, so they extrapolate their limited experience to a cast-iron position, which will really be justified and correct in the context from which they come, but miss big picture. As always, you must decide on the needs of the business / customers / users and not what type of coding methodology you prefer.

Answer 3

As a rule, I prefer to store as much data as possible in SP; A semi-general level of data access will handle disconnecting requests and presenting data to the interface.

The old wisdom of "increased performance" with SP is really no longer relevant *, but I found maintenance much easier with this method.

However ... I avoid triggers like the plague. They are difficult to maintain, almost impossible to debug and lead to any unforeseen side effects.

---

* The general perception is that stored procedures work better than special SQL queries. However, this is not true:

SQL Server 2000 and SQL Server 7.0 contain a number of changes to the processing of statements that extend many of the efficiencies of using stored procedures for all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure compiled at run time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 store execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

- SqlServer Books Online

Answer 4

Fraud is much easier to commit to a database that has dynamic SQL coming from the application because permissions are set at the table level. Most scammers are committed by current employees, who often have easy access to the server. Disgruntled employees also like to do things to destroy data. Exactly two people should have access to the tables in your production database, if it has any financial data, dba and one alternative dba. Everyone else should only have tasks that are described using stored procedures and nothing else. Auditing should be at the database level or practically useless. If I check at the application level in an application that does not use stored procs (the two are usually executed together), it is easy for someone to directly access the tables, modify them, and then not track. Audit logs should capture data changes that can only be made where data changes.

If data manipulation is complex, stored procs offer the best method for tuning performance.

Another reason for using procs is that it is much easier to reorganize the database as needed if all calls to the table are encapsulated in procs. It is also much easier to upload small changes to prod.

Answer 5

Everything should be in an appropriate place.

The database must protect its perimeter. He cannot assume that only people who are allowed to connect to him are a reliable and trusted application.

Sooner or later, someone is going to connect to it using a buggy application or a buggy client library (Oracle has several clients that allow users to create invalid dates). Or they are going to connect to the author of the report or to Access or Excel.

My preference is not to allow direct access to the table, but to allow access only to views, SP, etc., therefore, role-based permission is controlled there, and the interface contract is formed very exactly the same way you define interfaces for their classes or services at the application level. This does not mean that all / a lot of the application logic is in stored procedures, but it does mean that the database applies certain rules - usually basic, such as referential integrity - so the CAN application makes some assumptions about how the database was previously used in previous versions.

Tech Ed 2009 had a great group discussion, which you can watch here .

Answer 6

IMHO you should create an SP for any logic designed for data processing , and can serve as a good abstraction between the application and the database. A good example of this is password hashing in an application. I always create an SP that creates an entry and hashes the password for me ....

because the hashed password is really a database domain. I also create an SP to check if the password string is the same hash (yes, this is passed to the database, but I trust my database and network).

Forgetting the weak example: when you can separate problems without damaging the design and integrity of your system ... do an abstraction and do it!

The financial sector uses a lot of SPs to create an efficient API on top of the database to handle illegal financial transactions, as they can audit outside the application in stored procedures.

In a typical design, what should be the RDBMS-side and app-side can be quite obvious when you think ... "Does the application need to do this with data, does it bother?"

Answer 7

Different philosophies have different answers, but here is my

• Calculations with heavy use of data are, in particular, all that is very expensive to do outside the database.

• Common code. My last assignment, which used a relational database, was to connect to a library of database functions. Java code knew nothing about this connection. To avoid duplication, any function needed by both the code talking to the library and the Java used was stored in the procedures.

Another problem is to make sure that complex or critical code is tested. This does not mean that it cannot be a stored procedure, just that it can be simpler, at least write it first where testing is simpler.

Obviously, if a function needs to talk to external services (to which the database does not have easy access), you may not want to make this stored procedure.

Answer 8

Here is another point of view: None .

The database is for persistent data.

Your application is for processing. All logic, all code, all decisions, all calculations, all referential integrity, everything should be in your application.

It makes life very simple if you have a simple database containing only data.

Ultimately, many people believe that their stored procedures and triggers created an intricate mess that is difficult (or impossible) to maintain. This is easily prevented by using the database as a fast, large, easy-to-backup data warehouse. Indexes and views and the correct part of the database.

Everything else does not belong.

People raise "problems" as follows.

• The database is "central". Somehow the application server is not central. I do not understand. If you want centralized processing, use the application server. If you want a lot of complex, centralized processing, use an ESB.

• Some processing is "closer to the database." There are many bad examples. Good examples are often associated with surrogate key assignments. I prefer to use the ORM layer (iBatis, or SQLAlchemy or something else) for this. You get the same effect without recording or saving the stored procedure.

• Some processing requires an audit. In fact, processing requires an audit. Use Business Domain objects with ORM and do your audit processing in your application.

  Built-in audit based on RDBMS is good. Any audit you write with stored procedures will not be so good. Decision. Use the registration in your application and the built-in audit features of the database engine. Avoid triggers and SP for this.

• Stored procedures are faster. This is not true. Good transaction design makes things faster. Stored procedures impose a number of restrictions on your architecture, which sometimes forces you to create small, accurate transactions. Try writing the same small focused transaction in Java, and you will find that PL / SQL is actually not faster.

You can live without stored procedures. Ultimately, you will find that a focused transactional design discipline is more valuable than the procedure itself.

Example

"if I create new entries, the expected audit trail entries will not be generated"

If you had the correct definition of the Business Domain class to create your records, it would also create audit records for you in your application.