Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the high-performance optimization method of SQL Server database". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In the past, editors often used SQL SERVER when doing ASP and .NET, but now they use MYSQL most of the time, but some customers want to upgrade or develop compatible on the original SQL platform. Fortunately, PHP is omnipotent, basically all of it can be connected and supported. This article mainly introduces the experience summary of correctly optimizing SQL Server database. It includes the description of the places worthy of attention in the actual operation of optimizing it, as well as the most basic principles of optimizing SQL statements. The following is the main content description of the article.

Considerations for optimizing the database:

1. Index the key fields.

2. Use stored procedures to make SQL more flexible and efficient.

3. Back up the database and clean up the garbage.

4. The optimization of SQL sentence syntax. (you can use SQL Expert, but I can't find the serial number of unexpired.)

5. Clean and delete the log.

The basic principles of SQL statement optimization:

1. Use the index to traverse the table faster.

The index built by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. Reasonable index design should be based on the analysis and prediction of various queries.

Generally speaking:

1. There are a large number of duplicate values and there are often range queries (between, >

< ，>

=

< =)和order by、group by发生的列，可考虑建立群集索引 　　②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引; 　　③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 　　2、IS NULL 与 IS NOT NULL 　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 　　3、IN和EXISTS 　　EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。 　　4、在海量查询时尽量少用格式转换。 　　5、当在SQL SERVER 2000中 　　如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。 　　6、ORDER BY和GROPU BY 　　使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。 　　7、任何对列的操作都将导致表扫描，它包括SQL Server数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 　　8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。 　　9、SET SHOWPLAN_ALL>

10. Use cursors cautiously

In some situations where cursors must be used, consider transferring qualified rows of data into temporary tables, and then manipulating temporary table definition cursors, which can significantly improve performance.

Note: the so-called optimization is that the WHERE clause takes advantage of the index, and non-optimization results in a table scan or extra overhead. Experience has shown that the biggest improvement in SQL Server database performance comes from logic, index design, and query design. On the other hand, the biggest performance problems are often caused by deficiencies in these same aspects.

In fact, the essence of SQL optimization is to reduce the number of table scans and avoid the occurrence of table search by making full use of the index with sentences that can be recognized by the optimizer under the premise of correct results. In fact, the performance optimization of SQL is a complex process, which is only a reflection at the application level. The in-depth study will also involve the resource configuration of the SQL Server database layer, the flow control of the network layer and the overall design of the operating system layer.

This is the end of the content of "High performance Optimization method of SQL Server Database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.