SQL Server Database performance optimization

SQL Server Database performance optimization

Recent project needs, did a period of time SQL Server performance optimization, encountered some problems, but also accumulated some experience, now summed up, with June share. 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, switches, and so on. This article focuses on how to improve the SQL statement, and there will be another discussion on how to improve the index.
How to improve some of the principles of SQL statements:

1. Request fields on demand, say goodbye to "select *"
The extraction of a field must be based on the principle of "how much to use" and avoid using "SELECT *". To do such an experiment, table Tbla has 10 million data:

Select top 10000 c1, C2, C3, C4 from Tbla ORDER BY C1 desc--spents: 4673 milliseconds
Select top 10000 c1, C2, C3 from Tbla ORDER by C1 desc--spents: 1376 milliseconds
Select top 10000 c1, C2 from Tbla ORDER BY C1 desc--spents: 80 milliseconds

As a result, each time we extract a single field, the data extraction speed will be correspondingly improved. But the speed of ascension depends on the size of the field you discard.
In addition, for the "select *" problem, you can refer to this article:
Http://www.cnblogs.com:80/goodspeed/archive/2007/07/20/index_coverage.html

2. field name and table name to write specifications, note case
This point to pay more attention, if the case is wrong, although the SQL can still execute normally, but the database system will spend a certain amount of overhead and time to write your specifications correctly before you execute SQL. Write the right words, the time will be saved.
Normal: Select Top Ten dtetransaction, txtsystem_id from Tbltransactionsystem
Careless: Select Top Ten dtetransaction, txtsystem_id from Tbltransactionsystem

3. Proper use of transition tables
Sorting a subset of tables and creating temporary tables can sometimes speed up queries. It helps to avoid multiple sorting operations, and in other ways simplifies the work of the optimizer. 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 the query is to be executed more than once, all unpaid customers can be found in a temporary file and sorted by the customer's 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 in the temporary table in the following way:

Select Cl,c2 from Temp_cust_with_balance WHERE postcode> "98000"

The rows in the staging table are less than the rows in the primary table, and the physical order is the required order, reducing disk I/O, so the query effort can be significantly reduced. Note: When you create a staging table, it does not reflect changes to the primary table. When data is frequently modified in the primary table, be careful not to lose data.

4. Do not perform function calculations in the Where condition
The consequence of this is that the operation will be performed on each row, which will cause the index of the column to fail and trigger a full table scan. The following sql:

SELECT * from the users where year (dtecreated) < 2007

can be changed into

SELECT * from users where dtecreated < ' 2007-01-01 '

This will use an index for dtecreated to improve query efficiency.

5. In (not) operator and EXISTS (not EXISTS) operator
Sometimes a column is compared to a series of values. The simplest approach is to use subqueries in the WHERE clause. You can use subqueries in the WHERE clause in two ways. 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)

The advantages of SQL in write are easier to write and easy to understand, which is more suitable for modern software development style. However, SQL performance with in is always lower, while the second format is much more efficient than the first. The steps from SQL to parse SQL with in is the following differences from SQL without in:
SQL attempts to convert it to multiple tables of connections, if the conversion is not successful, then execute in the subquery, and then query the outer table records, if the conversion is successful, the direct use of more than one table connection method query. This shows that using in SQL at least one more conversion process. General SQL can be converted successfully, but for the inclusion of grouping statistics and other aspects of SQL cannot be converted.
In the second format, the subquery starts with ' SELECT 1 '. Use the EXISTS clause no matter what data the subquery extracts from the table it only looks at the WHERE clause. This way, the optimizer does not have to traverse the entire table and can do its work only by indexing (this assumes that the column used in the WHERE statement has an index). As opposed to the in clause, exists uses a concatenated subquery, which is more difficult to construct than in subqueries.
By using exist, the database system first checks the main query and then runs the subquery until it finds the first match, which saves time. When the database system executes an in subquery, the subquery is executed first, and the resulting list is stored in an indexed temporary table. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries.
Instead of not, use not exists as much as possible, although both use not (which cannot be used to slow down the index), the not exists is more efficient than the not-in query.

6. Is null or is not NULL operation (determines whether the field is empty)
cannot be indexed by NULL, any column that contains null values will not be included in the index because the B-tree index is not an index null value. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance.
Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.
Recommended scenario: Replace with other operations with the same function, such as a is not null change to a>0 or a> ', etc. Also set the field is not allowed to be empty, but with a default value instead of a null value, such as a datetime field, you can set the default time to "1900-01-01".

7. > and < operator (greater than or less than operator)
The greater than or less than the operator generally does not need to adjust, because it has an index will be indexed to find, but in some cases it can be optimized, such as a table has 1 million records, a numeric field A, 300,000 records of a=0,30 Records of the A=1,39 million records of a=2,1 Records of the a=3. Then the effect of a>2 and a>=3 is very different, because A&GT;2 SQL will find the record index for 2 and then compare, while A>=3 SQL can find the record index of =3 directly. Can be considered together with a nonclustered index.

8. Like operator
The LIKE operator can apply a wildcard query, where the wildcard combination may reach almost arbitrary queries, but if used poorly it can produce performance problems, such as the "%5400%" query does not reference the index, and the "x5400%" reference to the scope index. Because the index is placed according to the field values in ascending or descending order, like '%* ' this usage, cannot use the orderly data structure, uses the dichotomy method to find the data. A practical example: Use the user identification number behind the business number in the YW_YHJBQK table to query the business number YY_BH like '%5400% ' this condition will result in a full table scan, if changed to yy_bh like ' x5400% ' or yy_bh like ' b5400% ' will benefit The performance of the two-range query with YY_BH Index is certainly greatly improved.

9. Appropriate and inappropriate in the query conditions
Query parameters can include actions: =, <, >, >=, <=, between, partial like. Among them, like when used in this way will use the index: as ' *% ', but like '%* ' does not use the index.
Inappropriate query parameters are: not,! =, <>,!>,!<, not EXISTS, no in, no like, and some improper usage, such as: Calculation of data, negative queries, use of functions on the left side of the equals sign, use or. The above syntax does not have to be indexed, reducing the efficiency of the program.

10. Use Delete sparingly

In general, some of the logic of deleting data will be implemented more or less in stored procedures. For a small number of tables, the problem is not very big. But for large data tables, deleting data with delete can have a certain effect on the performance of the stored procedure. Because delete takes a full table-by-scan approach, it is a transactional operation that is counted into the SQL Server transaction log. Not only increases the running time, but also writes log files frequently, which causes the log file to be too large and consumes disk space excessively. Therefore, you can use the truncate operation instead of delete,truncate is not counted into the transaction log, but also without the condition of the deletion, the execution is very fast. Or you can simply drop the table and recreate it, sometimes faster than delete.

PS: The two methods of emptying SQL Server log files from the 10th

One way: Empty the log.

1. Open Query Analyzer, enter command dump TRANSACTION database name with NO_LOG

2. Open Enterprise Manager again--right click on the database you want to compress--all tasks--shrink the database--Shrink the file--Select the log file--in the contraction mode to choose to shrink to XXM, here will give an allowable contraction to the minimum number of M, directly enter this number, OK.

The other approach has some risk, because SQL Server log files are not written to the database master file immediately, such as improper processing, can cause data loss.

1: Delete Log

Detach Database Enterprise Manager-> Server-> Database-> Right-click-> Detach Database

2: Delete log file

Attach Database Enterprise Manager-> Server-> Database-> Right-click-> Attach Database

This method generates a new log with a size of more than 500 K.

SQL Server database Performance optimization SQL statement