9CBS - Document Center - Database Developer reading: 1418 Comments:
0 Participation Titles About SQLServer Some Precautions Select from GoALER BLOG Keywords About SQL Server Some Precautions If you are responsible for a SQL Server-based project, or you have just contacted SQL Server, you may face some databases Performance issues, this article will provide you with some useful guidelines (most of which can also be used in other dbms). Here, I don't plan to introduce the use of SQL Server tricks, and I can't provide a hundred diseases. The program, 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 the same design mistakes once again. Repeat. Do you know your tool? Don't despise this, this is the most critical one I tell in this article. Maybe you also see that there are many SQL Server programmers do not master all T-SQL commands and SQL Server provides useful tools. "What? I want to waste a month to learn the sql command I will never use?", You may say this. Yes, you don't need this. " But you should browse all T-SQL commands with a weekend. Here, your task is to understand, in the future, when you design a query, you will remember: "Right, there is a command here to fully implement me. The function ", so, to the MSDN to view the exact syntax of this command. Don't use the cursor to repeat again: Don't use the cursor. If you want to destroy the entire system performance, they are your most effective preferred way. Most Both beginners use cursors, but they don't realize their impact on performance. They take up memory, and they use their incredible ways to lock the table, and they are simply like snails. And the worst is, they can make you All performance optimizations that DBA can do is equal to nothing. I don't know if you know that Fetch 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 the cursor is a more familiar and comfortable programming method, which can lead to poor performance. Obviously, the overall purpose of SQL is what you want to achieve, not how it is realized. I used T-SQL to renote a cursor-based storage process. The table has only 100,000 records. The original stored procedure used for 40 minutes. After the execution, the new stored procedure is only 10 seconds. Here, I think you should be able to see something that is unknown. We can write a small program to get and process the data and update. Database, this is sometimes more effective. Remember: For cycles, T-SQL is not powerful. I will remind: Use the cursor. In addition to the DBA work, I have never seen any use of cursors can effectively complete any Work. Standardize your data sheet why not normalize the database? There are about two excuses: for performance considerations and purely because of lazy. As for the second point, you have to pay for it later. And on performance, you don't need Optimization is 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. DBMS is designed To deal with the specification database, remember: Design the database according to the standardized requirements. Don't use SELECT * This is not easy to do, I know it too much, because I often do this. But if you specify in SELECT You need the columns, which will bring the following benefits: 1 Reduce internal storage and network bandwidth 2 You can get a more secure design 3 To the query optimizer, read all the required columns from the index, you will have data The operation is to create a robust index for your database. It is a merit. You can do this is an art. Whenever you add an index for a table, SELECT will be faster, you can insert and Delete has slowed down because it is necessary to create a maintenance index requires many additional work. Obviously, the key to the problem is: What kind of operation is you going to do? This problem is not very good to grasp, especially related delete As Update, because these statements often contain select commands in the WHERE section. Do not create indexes for the "Gender" column First, we must understand how index accelerate access to the table. You can understand the index as a standard on a standard A way to divide. If you create an index to columns like "gender", you just divide the table to two parts: men and women. You are dealing with one There is a table of 1,000,000 records, what is the meaning? Remember: Maintenance index is time-consuming. When you design an index, please follow this rule: According to the number of different contents, the number of different contents is ranked less. , Such as: Name province gender. Please use a transaction, especially when the query is time consuming. If the system has problems, this will save you a life. General experienced programmers have experience ---- - You often encounter some unpredictable situations that will cause the stored procedure to collapse. Carefully dead locks to access your table in a certain order. If you lock Storage A, lock Store on the table B, then in all stored procedures In this order, you must lock them in this order. If you (inadvertent), lock the table B in a stored process, lock the table A, which may cause a deadlock. If the lock order is not pre-detailed The deadlock is not easy to discover. Don't open a big data set in the 9CBS technology forum :), a regularly presented question is: How can I 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 curse you. Here, you need a better UI, you need to be Your user displays no more than 100 or 200 records. Do not use the server-side cursor to compare the server-side cursor, the client game can reduce the system overhead of the server and the network, and reduce the lock time. Use the parameter query Sometimes, I am in 9CBS Technical Forum seeing questions like this: "SELECT * from a where a.id = 'a'
