Database optimization and SQL statement Optimization Principles

Thoughts on optimizing databases:
======================
1. Create an index for the key fields.
2. Using Stored Procedures makes SQL more flexible and efficient.
3. Back up the database and clear junk data.
4. SQL statement syntax optimization. (Sybase SQL expert can be used)
5. Clear and delete logs.

SQL statement optimization principles:
============================
1. Use indexes to traverse tables faster.
The index created by default is a non-clustered index, but sometimes it is not optimal. Non-Cluster Index
The data is physically stored on the data page at random. Reasonable index design should be based on
Analyze and predict various queries. In general: ①. There are a large number of repeated values and frequent range queries
(Between, >,<, >=, <=) and order by, group by columns.
Create a cluster index; ②. Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can create a composite index.
③. The composite index should try to make the key Query Form an index overwrite, and its leading column must be
Is the most frequently used column. Although the index can improve performance, the more indexes, the better.
Leading causes system inefficiency. You can add an index to each table to maintain the index set.
The integration requires corresponding updates.
2. Is null and is not null
Null cannot be used as an index. Any column containing null values will not be included in the index. Even if the index has
If multiple columns contain null
Excluding the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved. Any
The statement optimizer that uses is null or is not null in the WHERE clause is not allowed.
You can use the index.
3. In and exists
Exists is far more efficient than in. It is related to full table scan and range scan. Almost
Some in operator subqueries are rewritten to subqueries using exists.
4. Use as few formats as possible for massive queries.
5. In SQL Server 2000, if the stored procedure has only one parameter and is of the output type
You must give this parameter an initial value when calling this stored procedure. Otherwise
A call error occurs.
6. Order by and gropu
Using the 'ORDER BY' and 'group by' phrases, any index can improve select performance. Note:
If the index column contains a null value, optimizer cannot optimize it.
7. Any column operation will cause table scanning, including database functions and calculation expressions.
Move the operation to the right of the equal sign as much as possible.
8. In And or clauses usually use worksheets to invalidate indexes. If you do not generate a large number of duplicate values, consider
Separate the clause. The split clause should contain the index.
9. Set showplan_all on to view the execution plan. DBCC checks database data integrity.
DBCC (Database Consistency Checker) is a group of SQL Server data used to verify
Database integrity Program .
10. Use cursor with caution
In some cases where a cursor must be used, you can consider transferring qualified data rows to a temporary table before
The table defines the cursor to perform operations, which can significantly improve the performance.
Conclusion: optimization means that the WHERE clause uses indexes. If indexes are not optimized, table scanning or additional overhead occurs. Jing
Check shows that the greatest improvement of SQL Server performance is due to the logical database design,
Index design and query design. On the other hand, the biggest performance problem is often caused by these same aspects.
. In fact, the essence of SQL optimization is that, with the correct results,
Use the statements that can be recognized by the optimizer and use indexes in full to reduce the I/O times of table scans and avoid table searches.
. In fact, SQL Performance optimization is a complex process.
In an embodiment of the application layer, in-depth research will also involve resource configuration at the database layer and traffic control at the network layer.
And the overall design of the operating system layer.