SQL statements for optimizing SQL Server database performance

Document directory

• 1. Ask for fields as needed and say goodbye to "SELECT *"
• 2. The field name and table name should be standardized, and case sensitive should be noted.
• 3. Use the transition table as appropriate
• 4. Do not perform function compute in the where condition.
• 5. IN (not in) operator and EXISTS (not exists) Operator
• 6. is null or is not null operation (judge whether the field IS empty)
• 7.> and <operator (greater than or less than operator)
• 8. LIKE Operator
• 9. The appropriate and inappropriate query Conditions

Recently, the project needs to optimize the performance of SQL Server for a period of time. Some problems have been encountered and some experience has been accumulated. Here, we will summarize and share it with you. SQL Server performance optimization involves many aspects, such as good system and database design, high-quality SQL writing, appropriate data table index design, and even various hardware factors: network Performance, server performance, operating system performance, and even network cards and switches. This article focuses on how to improve SQL statements. Another article will discuss how to improve indexes.

How to Improve the principles of SQL statements:

1. Ask for fields as needed and say goodbye to "SELECT *"

The field extraction must follow the principle of "How much is used to raise the number" to avoid the use of the "SELECT *" operation. After doing this experiment, the table tblA has 10 million data:

Select top 10000 c1, c2, c3, c4 from tblA order by c1 desc time: 4673 Ms

Select top 10000 c1, c2, c3 from tblA order by c1 desc time: 1376 milliseconds

Select top 10000 c1, c2 from tblA order by c1 desc time: 80 ms

From this point of view, each time we extract one field less, the data extraction speed will be correspondingly improved. However, the speed of improvement depends on the size of the discarded field.

2. The field name and table name should be standardized, and case sensitive should be noted.

Pay more attention to this point. If the case is incorrect, although the SQL statement can still be executed normally, the database system will spend a certain amount of time and overhead to normalize the data you have written, then execute the SQL statement. If it is correct, this time will be saved.

Normal: select top 10 dteTransaction, txtSystem_id from tblTransactionSystem

Careless: select top 10 dtetransaction, txtsystem_id from tbltransactionsystem

3. Use the transition table as appropriate

Sort a subset of a table and create a temporary table, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. For example:

SELECT cust. name, rcvbles. balance ,...... Other columns

FROM cust, rcvbles

WHERE cust. customer_id = rcvlbes. customer_id

AND rcvblls. balance> 0

AND cust. postcode> 98000"

Order by cust. name

If this query is executed multiple times but more than once, you can find all the unpaid customers in a temporary file and sort them by customer name:

SELECT cust. name, rcvbles. balance ,...... Other columns

INTO temp_cust_with_balance

FROM cust, rcvbles

WHERE cust. customer_id = rcvlbes. customer_id

AND rcvblls. balance> 0

Order by cust. name

Then, query the temporary table in the following way:

SELECT * FROM temp_cust_with_balance

WHERE postcode> 98000"

The temporary table has fewer rows than the primary table, and the physical order is the required order, which reduces disk I/O, so the query workload can be greatly reduced. Note: after a temporary table is created, the changes to the master table are not reflected. Do not lose data when the data in the master table is frequently modified.

4. Do not perform function compute in the where condition.

The consequence of this operation is that the operation will be performed on each row, which will cause the index of the column to fail and trigger the full table scan. The following SQL statement:

Select * from users where YEAR (dteCreated) <2007

You can change it to select * from users where dteCreated <'2017-01-01 '. This will use the index for dteCreated to improve query efficiency.

5. IN (not in) operator and EXISTS (not exists) Operator

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. You can use two subqueries in the where clause. As follows:

The first method uses the in operator:

Select a. id from tblA a where a. id in (select B. id from tblB B)

The second method uses the exist operator:

Select a. id from tblA a where exists (select 1 from tblB B where B. id = a. id );

SQL statements written IN are easy to write and understand, which is suitable for modern software development. However, the SQL Performance of IN is always relatively low, and the second format is far more efficient than the first one. The SQL Execution steps are used to analyze the differences between SQL statements using IN and SQL statements without IN:

SQL tries to convert it to the join of multiple tables. If the conversion fails, it first executes the subquery IN and then queries the outer table records, if the conversion is successful, multiple tables are directly connected for query. It can be seen that at least one conversion process is added to SQL statements using IN. General SQL statements can be converted successfully, but SQL statements that contain grouping statistics cannot be converted.

In the second format, the subquery starts with 'select 1. Use the EXISTS clause to query the data extracted from the table without a pipe. It only displays the where clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where statement has an index ). Compared with the IN clause, EXISTS uses connected subqueries, which is more difficult to construct than IN subqueries.

By using EXIST, the database system first checks the primary query, and then runs the subquery until it finds the first match, which saves time. When executing an IN subquery, the database system executes the subquery and stores the obtained result list IN a temporary table with an index. Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then runs the primary query. This is why EXISTS is faster than IN queries.

At the same time, do not exists should be used as much as possible to replace not in, although both use NOT (the index cannot be used to reduce the speed), not exists is more efficient than not in query.

6. is null or is not null operation (judge whether the field IS empty)

Null cannot be used as an index. Any column containing null values will not be included in the index, because the B-tree index does not index null values. 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.

Recommended Solution: replace it with other operation operations with the same function, such as changing a is not null to a> 0 or a>. In addition, the field cannot be blank, but a default value is used to replace null values. For example, a datetime field can be set ".

7.> and <operator (greater than or less than operator)

If the value is greater than or less than the operator, you do not need to adjust it. Because it has an index, index search is used, but in some cases it can be optimized. For example, a table has 1 million records, for A numeric field A, 0.3 million records A = 3. Therefore, the effect of executing A> 2 and A> = 3 is very different, because when A> 2, the SQL will first find the record index of 2 and then compare it, when A> = 3, SQL directly finds the record Index = 3. It can be considered together with non-clustered indexes.

8. LIKE Operator

The LIKE operator can be used for wildcard queries. The wildcard combinations in the LIKE operator can be used for almost any queries. However, poor use may result in performance problems, for example, LIKE '% 100' does not reference the index, while LIKE 'x5400%' references the range index. Because the index is placed in ascending or descending order based on the field values, like '% *' is used, and the ordered data structure cannot be used to search for data using the binary method. An actual example: the user ID following the Business ID in the YW_YHJBQK table can be used to query the Business ID YY_BH LIKE '% 100'. This condition will generate a full table scan, if you change to YY_BH LIKE 'x5400% 'OR YY_BH LIKE 'b5400%', the index of YY_BH will be used to query the two ranges, and the performance will be greatly improved.

9. The appropriate and inappropriate query Conditions

The query parameters can include the following operations: =, <,>, >=, <=, BETWEEN, and some like. In this example, like will use the index like '* %', but like '% *' won't use the index.

Improper query parameters include: NOT ,! =, <>,!> ,! <, Not exists, not in, not like, and so on. There are some improper usage such as calculation of data, negative query, function on the left side of the equal sign, and use OR. The above syntax does not need to be indexed, reducing program efficiency.