From crazy obsession database stored procedures to completely give him up

Early on, I was taught by our leaders that our leader was a web-born man who attached great importance to Web functionality. In his eyes, the database is just a box of data, should not put too much business logic to the database to deal with, should only think of him as a storage data box, we were using MySQL + PHP, at that time MySQL is weaker, do not support stored procedures, triggers, transactions and so on, is exactly in line with the philosophy advocated by our leaders.

Later contacted the ERP, found that the volume of data is very large, all using VB and other processing efficiency is low, speed is slow, the use of stored procedures high efficiency, and some bugs, new features as long as the database server to modify the stored procedures can be, the client does not have to modify the program, feel this stored procedure is very powerful And through the stored procedures can also do other software database interface, since then crazy obsession with stored procedures, database technology, and often study these aspects of technology.

After a few days, contact with Oracle, found that the stored process is incompatible with the sqlsever, some writing, usage, the idea of the gap is also very large, transplant problem is a big trouble, although the theory is completely can be transplanted, but to maintain 2 sets of such a system, a lot of trouble, So began to gradually give up the writing stored process this hobby, try to write a combination of simple SQL statements, as much as possible to write business logic in C #, good debugging, with a deep understanding of C # syntax, business logic written in C # in the development speed, than written in the stored process is much faster, Then the overall function of the system positioning, and gradually gave up the local function of the optimization idea, to consider the overall situation, not bad so 0.1 seconds of performance speed improvement, do not care about these little gaps.

In recent years, due to the in-depth understanding of service-oriented programming, completely abandoned the write stored procedures, as far as possible business logic is written in C #, because the customer may be bought genuine Oracle or purchased a genuine SQL Server, they want your program to run on their legitimate database system, Instead of using your system, buy another set of genuine software.

The main disadvantage of database script based on stored procedure is debugging trouble, the table field in the database changes, do not prompt association error, also have no version control, easily lose script program, and everyone has a local database, it is easy to confuse the stored procedure, It is also a fatal problem to have a uniform normalization of the naming process, which ultimately leads to the fact that no one knows which is the newest stored procedure, and that it gives the hundreds of stored procedures a reasonable name.

The benefits of writing business logic in C # are many, there is a corresponding version of the controller, the previous code can also be found, not afraid of missing code, some modify the program, etc. caused by errors, in the development of the environment can be found in the compilation phase errors, good for association correction, a variety of versions of the database on the transplant is also simpler, Also do not have to fight both sides, and maintain the version of the data, but also maintain the version of the program, the release will encounter 2 aspects need to update the problem. Or unilateral combat is relatively simple, and other systems, such as interface, to the corresponding service procedures for processing, by the service program to be responsible for interaction with other systems, this interaction is much more powerful than the interaction of the underlying database.

I have written a small software, which uses a lot of stored procedures, to my pain summed up is, 1. The tables in the database have been fixed and I don't know which stored procedures need fixing?

2. Sometimes the hands of several databases, testing to test to go, it is easy to not know which is the latest, especially after the Yinianbanzai, and then to find the corresponding database, this problem is obvious.

3. Debugging C # programs is easy, debugging stored procedures, relatively troublesome.

4. Changes in parameters, but also to modify the parameters of stored procedures, and sometimes the associated stored procedures need to be modified, very toss people, and run, to discover these problems.

3. There is also this program can only run on SQL Server, fortunately we are using the D version, want to install the installed, also do not have money, if really want money, it is not a joke.

Now we've developed a lot of systems that don't see the shadow of the stored procedure, of course, we are not to go to extremes, in peacetime some simple system, still really not much need to use stored procedures, can not do without it, more than one thing, now summed up the road, feel or business logic as far as possible write in C # Is the right way to worry and labor.