MSSQL Performance Optimization

1. Use as few cursors as possible
The reason is very simple; that is, the cursor algorithm is the most original computer algorithm (like statements such as for. If, one query is used for computation; the efficiency is extremely low );
SQL statements use a set of operations. The speed is much faster. If an index is used, the speed is very fast (with a pointer ).
2. Create an index.
A. Clustered index:
Clustered index is the same for disk storage and logical display.
The primary key of the MSSQL table is generally a clustered index; the primary key (uniquely identified for each record );
The created primary key will automatically be a clustered index;
If there is a very large table (with millions of rows), there will be similar fragments in disk storage for a long time (the disk filling rate is low; it is generally caused by frequent deletion );
The simplest way to improve its performance is to remove the primary key of the table and save it; then reset the primary key and save it;
(This table will be sorted again on the disk; The performance will certainly improve)
B. Non-clustered indexes:
Non-clustered indexes are used to create small additional tables (a tree structure; most of them are B or B + trees );
Reading (traversing SQL statements such as select) tables is fast, but the performance of writing (Update, delete. insert, and other SQL statements) tables is slightly reduced.
We recommend that you set no more than three non-clustered indexes for tables with a large data volume (saving extra disk workload ).
Do not create indexes for columns similar to "gender.
3. deadlock:
A thread is reading a record; other threads are waiting to read this record;
In MSSQL, as long as the threads occupying that record are removed for a long time, the deadlock will be removed.
In MSSQL, the lock is for each row of records (so the performance is good ).
The causes of frequent locks include:
A. Use transaction statements in SQL statements (especially when the query is time-consuming in transactions ).
B. The connection conflict (not closed) of the foreground application ).
C. Multi-table joint query (especially when a large data set is opened ).
4. SQL statement Optimization
A. "Is null" "not" "or" "in" does not use Indexes
B. Avoid using computation or function processing on index columns (indexes may cause high performance loss). "%"; some may even cause full index performance loss.
C. Avoid using '*' in select (rather than listing all fields ).
D. Avoid related subqueries (select in select ).
E. In the where condition, "=> exists> in" (performance)
F. "Order by" "group by" "having" distinct "and other statements should be used with caution (because they are not efficient; they are used to process data first in a temporary table ).
G. If a clustered Index consists of two fields (TT1 and TT2), TT1 is in front of the index, and half of the clustered index is used if only the TT1 field is used in the where condition;
If the TT1 and TT2 fields are used to determine the where condition, the clustered index will be used;
In the where condition, if only the TT2 field is used for determination, the clustered index will not be used;
5. Try not to use the text data type
Do not use text unless you use text to process a large amount of data. Because it is not easy to query and slow, it will waste a lot of space if it is not used well.
Generally, varchar can better process your data.
6. Try not to use temporary tables
Try not to use temporary tables unless you have. Generally, subqueries can replace temporary tables. Using a temporary table will incur system overhead,
If the front-end code is used for the database connection pool, the temporary table exists from beginning to end. SQL Server provides some alternatives, such as the table data type.
7. Use as few foreign keys and triggers as possible.
In MSSQL, the performance of these functions is not very good. You can use the following table (it will be used to determine the relevant table; in many cases, it is not required). In the background, resources are consumed greatly.
I 'd rather write more related table operation code at the front end.