This article is our daily collection and arrangement of sqlserver database optimization experience and considerations,The basic principles of sql statement optimization are introduced in detail.Including the use of indexes, queries, and cursors
There are many online tutorials on sql optimization.But it's more messy.I have sorted it out recently.Write it down and share it with you,There are mistakes and deficiencies.Also please correct me.
Considerations for optimizing the database:
1. Key fields are indexed.
2, using stored procedures,It makes SQL more flexible and efficient.
3. Back up the database and clear junk data.
4. Optimization of sql statement syntax.(You can use sybase's sql expert, but unfortunately I did not find the unexpired serial number)
5. Clean up and delete logs.
Basic principles of SQL statement optimization:
1. Use indexes to traverse tables faster.
The index created by default is a non-clustered index,But sometimes it is not optimal.Under a non-clustered index,Data is physically stored randomly on data pages.A reasonable index design should be based on the analysis and prediction of various queries.In general:①. There are a lot of duplicate values, and often there are range queries (between,> ;,>=,<Consider building a clustered index;②. Often access multiple columns at the same time,And each column contains duplicate values, consider creating a combined index;③. The combined index should try to make key queries form index coverage,The leading column must be the most frequently used column.
2.is null and is not null
You cannot use null as an index, and any columns containing null values will not be included in the index.Even if the index has multiple columns,As long as one of these columns contains null, the column is excluded from the index.In other words, if a column has a null value,Even indexing the column will not improve performance.Any statement optimizer that uses is null or is not null in a where clause is not allowed to 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 the in operator subqueries were rewritten to use the exists subquery.
4. Minimize the use of format conversions in mass queries.
5. When in SQL Server 2000, if the stored procedure has only one parameter,And it is of type output. You must give this parameter an initial value when calling this stored procedure.Otherwise, a call error will occur.
6, order by and gropu by
Using the order by and group by phrases, any kind of index can help select performance.Note that if there are null values in the index column, the optimizer cannot optimize.
7, any operation on the column will cause the table scan,It includes database functions, calculation expressions, etc.Whenever possible, move the operation to the right of the equal sign.
8, in, or clauses often use worksheets,Invalidate the index.If there are not a lot of duplicate values,Consider splitting up the clauses.The index should be included in the broken clause.
9, set showplan_all on View the execution plan.dbcc checks database data integrity.dbcc (database consistency checker) is a set of programs used to verify the integrity of the SQL server database.
10. Use cursors sparingly
In some cases where cursors must be used,Consider moving eligible data rows into temporary tables,Then operate on the temporary table definition cursor,This can significantly improve performance.
Note:The so-called optimization is that the where clause uses an index,Non-optimization occurs when a table scan or additional overhead occurs.Experience shows thatThe biggest improvement of sql server performance is due to the logical database design, index design and query design.on the other hand,The biggest performance problems are often caused by deficiencies in these same areas.In fact, the essence of SQL optimization is on the premise of correct results,With statements recognized by the optimizer,Make full use of the index,Reduce the number of table scans, and try to avoid table searches.
In fact, the performance optimization of SQL is a complicated process.These are just a manifestation at the application level,In-depth research will also involve resource allocation at the database layer, flow control at the network layer, and overall design of the operating system layer.
The above content is all about the daily collection and arrangement of sqlserver database optimization experience and precautions.Hope you like it.
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- dart - flutter: the instance member'stars' can't be accessed in an initializer error