Shulou(Shulou.com)06/01 Report--

Programmers and database design case analysis, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Personally, programmers are full of respect and admiration, and it is my greatest regret that I have not been a programmer in my life. It is indeed great for them to create this world.

In the program development of SQL stored procedures, there is such an idea, that is, I just need to complete the function, indeed, a small amount of data to complete the function, I can write my stored procedures as a "methodology", back and forth calls, can also be my stored procedures, written into a "Korean TV series", or a Japanese "Sadako".

Why do you say that? because in the stored procedures I have read, there are really "Zhengzi", which are basically based on the completion of functions, and the rest are "Zhengzi".

You've seen a stored procedure that falls from scratch, all insert into select.. case...when when join..... Join where. Group by order by

Have you ever seen a stored procedure full of update. Set. Where xxx exist (select.)

I guess you have seen it, and in the programmer's eyes, whatever, your sentence provides me to write like this, I can write like this, and my function is good, what's wrong with me?

The following is the "trigger" designed by a financial software company

I wish I was NICE, KIND, be a gentleman in the eyes of others.

But I deeply regret such programming and the design of rows that do not understand the database at all. In such a design, we are waiting for customer complaints and even anger.

And colleagues on this matter, a discussion, the same point of view, from the logical design, to the formation of the code, can only express deep regret emotion and attitude. Because our customers are definitely not calm, Buddhist customers, when your system is slow to death, they are bound to change their face and answer, it's not my problem, the system is so slow that I can't even work?

After painstaking efforts, I finally got such a "reply". I think there must be tens of thousands of alpacas flying in DEVELOPERS's mood.

But the question is, when developing, if you think about the final result, will you still do the following?

The 1 update statement is followed by a bunch of conditions, associated tables, and takes a long time before UPDATE.

2 insert into select statement, followed by a pile of JOIN of various tables, various judgments, which take a long time

3 do not try to avoid the use of cursors, the entire cursor + loop (still in the internal)

4 A pile of if else if else seems to have entered the labyrinth

5 perform extremely complex TRIGGER design on the plugged port

In the end, it fell into a strange circle, and the programming of the database was written like a TV series of "Korea 108".

Then how to avoid such a problem?

1 UPDATE do not follow a bunch of conditions after a good UPDATE, UDPATE must be fast, you can judge what you need in UDPATE post-processing, first carry out a select to format it, variable, and so on, it is not that difficult, but your customers will not push you to the "cliff" because the system is running slowly.

2 INSERT Please good INSERT INSERT INTO should not exist in large systems, how to deal with it see above

3 Vernier, if there is no way, then use, do not use frequently no problem, otherwise pray, customers do not complain.

4 with regard to the design of TRIGGER, it is disabled in many systems, of course, we should analyze the specific problems, but I am really speechless about the ORACLE TRIGGER design in the picture above.

The reasons for the existence of temporary tables in stored procedures are as follows

1 complex multi-table query, the database optimization engine in Niu B, he also miscalculated, whether because of statistical data errors, or sentence writing errors, complex queries, if into multiple simple queries, there is no harm, then how to become a simple query, to undertake the intermediate results, it is natural to use temporary tables.

2 temporary tables can be indexed to improve the efficiency of query (some databases also have memory tables)

3 since it is a temporary table, the result set should not be very large, if it is very large, it is another topic.

So in large systems, please try to separate the operation of DML from the operation of SELECT, not insert select, update select, this is not good, but also easy to bring more problems, and complex locks.

So much for the answers to the questions about programmers and design case analysis in the database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.