Database optimization methods and SQL statement Optimization Principles

Database optimization methods and SQL statement Optimization Principles
Author: yashi Source: SCID
[Abstract] database optimization methods and SQL statement optimization principles: optimize databases by indexing key fields, backing up databases, and clearing junk data; optimize SQL statements with minimal format conversion when using indexes and massive queries.
[Keyword] how to optimize database SQL optimization database:

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. (You can use Sybase SQL expert. Unfortunately, I did not find the unexpired serial number)

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. In a non-clustered index, data is physically stored on the data page randomly. Reasonable index design should be based on the analysis and prediction of various queries. Generally speaking: ①. you can create a cluster index for columns with a large number of duplicate values and frequent range queries (between, >,<<=, <=), order by, and group; ②. multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can create a composite index. composite indexes should try to overwrite key queries. The leading column must be the most frequently used column. Although indexes can improve performance, the more indexes, the better. On the contrary, too many indexes will lead to low system efficiency. Each time you add an index to a table, you must update the index set.

◆ 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 there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from 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 statement optimizer that uses is null or is not null in the WHERE clause cannot use indexes.

◆ 3. In and exists

Exists is far more efficient than in. It is related to full table scan and range scan. Almost all 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 the parameter an initial value when calling the 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. During query, try to move the operation to the right of the equal sign.

◆ 8. In And or clauses usually use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.

◆ 9. Set showplan_all on. DBCC checks database data integrity.

DBCC (Database Consistency Checker) is a group used to verify the integrity of the SQL Server database.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 and then defining the cursor on the temporary table, which can significantly improve the performance.

Summary:

Optimization means that the WHERE clause uses the index. If the index is not optimized, table scanning or additional overhead occurs. It has been verified that the greatest improvement of SQL Server performance is due to the logical database design,

Index design and query design. Conversely, the biggest performance problem is often caused by these deficiencies in the same aspect. In fact, the essence of SQL optimization is to use the statements that can be identified by the optimizer on the premise that the results are correct to fully utilize the index, reducing the number of I/O scans on the table, avoid table search as much as possible. In fact, SQL Performance optimization is a complex process. The above is only a manifestation of the application layer, in-depth research will also involve resource configuration at the database layer, traffic control at the network layer, and the overall design of the operating system layer.

 

Optimize databases, SQL,

Chen Dongfang, responsible editor]