If you are responsible for a SQL Server-based project, or you have just contacted SQL Server, you may have to face some database performance, this article will provide you with some useful guidance (most of them can also be used for others DBMS). Here, I don't plan to introduce the trick to use SQL Server, and I can't provide a plan to cure all diseases. What I do is to summarize some experience - About how to form a good design. These experiences come from the lessons I have experienced in the past few years. I have seen many of the same design errors again and again. Do you know the tool you use? Don't underestimate this, this is the most critical one I tell in this article. Maybe you also see a lot of SQL Server programmers don't have the useful tools that all T-SQL commands and SQL Server are available. "What? I want to waste a month to learn the sql commands I will never use." You may say this. Yes, you don't need to do this. But you should use a weekend to browse all T-SQL commands. Here, your task is to understand, in the future, when you design a query, you will remember: "Right, there is a command that can fully realize the function I need", so I want to see this command. . Don't use a cursor to let me repeat: Don't use a cursor. If you want to destroy the performance of the entire system, they are your most effective preferred approach. Most beginners use a cursor without aware of their impact on performance. They take up memory and use their incredible way to lock the table, in addition, they are like snails. The worst thing is that they can make every performance optimization you can do with your DBA. I don't know if you know that every FETCH is executed, it is equal to executing a select command? This means that if your cursor has 10,000 records, it will execute 10,000 Select! If you use a set of SELECT, UPDATE or DELETE to complete the work, it will be more efficient. Beginners generally believe that the use of cursors is a more familiar and comfortable programming method, which can lead to bad performance. Obviously, the overall purpose of SQL is what you want to achieve, not how it is achieved. I used T-SQL to renovate a cursor-based store, that table is only 100,000 records, and the original stored procedure is completed for 40 minutes, and the new stored procedure is only 10 seconds. Here, I think you should see something that is not a competent programmer! ! ! We can write a small program to acquire and process the data and update the database, which is sometimes more effective. Remember: For cycles, T-SQL is powerless. I will re-remind: There is no benefit to use the cursor. In addition to the work of DBA, I have never seen any work that can be effectively completed using the cursor. Standardize your data table why not standardize the database? There are about two excuses: for performance considerations and pure because of lazy. As for the second point, you have to pay for this reason sooner or later. And about performance, you don't need to optimize things that are not slow. I often see some programmers "reverse standardization" database, their reason is "The original design is too slow", but the result is often what they make the system slower. DBMS is designed to handle specification databases, so remember: Design the database according to standardized requirements. Don't use SELECT * This is not easy to do, I know much, because I often do this.
However, if you specify the columns you need in SELEC, it will bring the following benefits: 1 Reduce internal deposits and network bandwidth 2 You can get a more secure design 3 to check all the needs from index optimizer Columns Learn that you will have a robust index for your database, that is, it is a merit. It is simply an art that you have to do this. Whenever you add an index for a table, SELECT will be faster, but INSERT and DELETE have slowed down because there are many additional jobs that create maintenance indexes. Obviously, the key to the problem here is: What kind of operation do you want to do with this table. This problem is not very good to grasp, especially when DELETE and UPDATE are involved, because these statements often contain select commands in the WHERE section. Don't create an index for the "Gender" column First, we must understand how the index accelerates access to the table. You can understand the index as a way to divide the table based on a certain standard. If you create an index to a column similar to "gender", you just divide the table to two parts: men and women. What is the meaning of this division in dealing with 1,000,000 records? Remember: Maintenance index is time-consuming. When you design an index, follow this rule: Based on the number of different contents, you may contain more than less arrangements, such as: Name province gender. Use transactions to use transactions, especially when the query is time consuming. If there is a problem in the system, this will save you. Generally, some experienced programmers have experience ----- You often encounter some unpredictable situations that cause the stored procedure to crash. Be careful with the deadline to access your table in a certain order. If you lock in the table a, lock Store on the table B, then lock them in this order during all stored procedures. If you (inadvertent), you can lock the table B in a stored process, lock the table a, which may cause a deadlock. If the lock order is not well designed in advance, the deadlock is not easy to find. Don't open big data sets in the 9CBS Technology Forum :), a problem that is often raised is: How can I quickly add 100,000 records to ComboBox? This is wrong, you can't do this. Very simple, your users want to browse 10,000 records to find the required records, he will definitely curse you. Here, what you need is a better UI, you need to display no more than 100 or 200 records for your user. Do not use the server-side game with the server-side cursor, the client cursor can reduce the system overhead of the server and the network, and reduce the lock time. When using the parameter query, I saw a problem like this in the 9CBS technology forum: "SELECT * from a where a.id = 'a'b, because the single query has an exception, what should I do?", And a common answer Yes: instead of single quotes with two single quotes. This is wrong. This will not be cured, because you will encounter such problems in other characters, let alone causing serious bugs, in addition to this, so that SQL Server's buffer system cannot play a role. Use the parameters to query, the bottom of the suction, these issues are all unsatisfactory. The quantity of the database programmers used in the development of large data are not large when the program encoding is not large, and the amount of data of the end user is very large. Our usual practice is wrong. The reason is very simple: now the hard drive is not very expensive, but the performance problem is to be noticed when it has been ignored? Don't use the insert to import a large number of data, please don't do this, unless it is necessary.
