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
Let me repeat again: 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 sheet
Why not normalize 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 it, I know much, because I often do this. However, if you specify the columns you need in Select, then the following benefits: 1 Reduce internal storage and network bandwidth
2 You can get a safer design
3 Read all the required columns from the index optimizer
Understand what you want to do
Create a robust index for your database, 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 a transaction
Please use a transaction, 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
Visit 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 server-side cursors
Compared with the server-side cursor, the client game can reduce the system overhead of the server and the network, and also reduce the lock time.
Use parameter query
Sometimes, 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 the popular answer is: use Two single quotes instead of 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.
Use large data volume when program encoding
Programmers used in the development of test databases general data volume is not large, often is that the amount of data of the end user is 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. Using UTS or BCP so you can both flexibly and speed.
Pay attention to timeout problems
When querying the database, the default of the general database is relatively small, such as 15 seconds or 30 seconds. Some query runtimes are longer than this length, especially when the data volume of the database is constantly growing.
Don't ignore the issue of simultaneously modifying the same record
Sometimes, two users will modify the same record at the same time, so that the latter modifier has modified the previous modifier operation, some updates will be lost. Handling this situation is not very difficult: Create a TimeStamp field, check it before writing, if allow, incompatible with modification, if there is a conflict, prompt the user.
When inserting a record in the detail table, don't perform SELECT MAX (ID) in the home table
This is a universal error that causes errors when two users are inserted in the same time. You can use Scope_Identity, Ident_current and @@ identity. If possible, do not use @@ Identity, because it will cause some problems in the case of triggers (see discussion here).
Avoiding will be set to NULLABLE
If possible, you should avoid it to NULLABLE. The system will assign an additional byte for each row of Nullable columns, which will bring more system overhead when queries. In addition, it is complex to NULLABLE to make the encoding because each access must be checked.
I am not saying nulls is the root of trouble, although some people think this. I think if "empty data" is allowed in your business rules, then you will have a good role in nullable, but if you use nullable in the following case, it is simply self-discipline.
Customername1
CustomerAddress1
CustomeMail1
Customername2
CustomerAddress2
CustomeMail3
Customername1
CustomerAddress2
CustomeMail3
If this happens, you need to standardize your table.
Try not to use the TEXT data type
Do not use it unless you use text to process a big data. Because it is not easy to query, the speed is slow, and a lot of space is used. In general, VARCHAR can better deal with your data.
Try not to use a temporary table
Try not to use a temporary table unless you have to do this. General usage subquers can replace a temporary table. Using a temporary table will bring system overhead, if you use COM to program, it will bring you a lot of trouble, because COM uses the database connection pool and the temporary table is existence from start to end. SQL Server provides some alternatives, such as Table data types.
Learn to analyze inquiry
SQL Server query analyzer is your good partner, how you can understand how the query and index affect performance.
Use reference integrity
Define the primary, unique constraints, and foreign keys, which can save a lot of time.
