SQL Server Performance Tuning 1

server| Performance
1, in the program, to ensure that in the implementation of the function of the foundation, minimizing the number of accesses to the database, minimizing the number of accesses to the table by searching the parameters, minimising the result set, and reducing the burden of the network; the ability to separate the operations as much as possible, to improve the response rate each time; When you use SQL in a data window, Try to put the index in the selected first column, the structure of the algorithm as simple as possible, when querying, do not use wildcard characters such as SELECT * FROM T1 statements, use a few columns to select a few columns such as: Select Col1,col2 from T1 , as far as possible, limit the number of result set rows, such as: SELECT top-col1,col2,col3 from T1, because in some cases users do not need so much data. Instead of using database cursors in your application, cursors are a useful tool, but require more overhead than using regular, set-oriented SQL statements, and fetching data in a specific order.

2, to avoid the use of incompatible data types. For example, float and int, char and varchar, binary, and varbinary are incompatible. Incompatibility of data types may make the optimizer unable to perform some optimizations that could have been done. For example:
SELECT name from employee WHERE Salary > 60000
In this statement, if the salary field is a money type, it is difficult for the optimizer to optimize it because 60000 is an integer number. Instead of waiting for run-time conversion, we should convert an integral type into a coin type when programming.

3. Avoid function or expression operations on fields in the WHERE clause, which causes the engine to discard the use of indexes for full table scans. Such as:
SELECT * from T1 WHERE f1/2=100
should read:
SELECT * from T1 WHERE f1=100*2

SELECT * FROM record WHERE SUBSTRING (card_no,1,4) = ' 5378 '
should read:
SELECT * FROM record WHERE card_no like ' 5,378% '

SELECT Member_number, first_name, last_name from
WHERE DATEDIFF (Yy,datofbirth,getdate ()) > 21
should read:
SELECT Member_number, first_name, last_name from
WHERE dateOfBirth < DATEADD (Yy,-21,getdate ())
That is, any action on the column will result in a table scan, which includes database functions, evaluation expressions, and so on, to move the action to the right of the equal sign whenever possible.

4. Avoid using operators such as!= or ">", is null, or not NULL, in, and not, because this causes the system to not use the index, and can only search the data in the table directly. For example:
SELECT ID from employee WHERE ID!= ' b% '
The optimizer will not be able to determine the number of rows that will die through the index, so you need to search all rows of the table.

5. Use numeric fields as much as possible, and some developers and database administrators like to put fields containing numeric information
is designed to be character-type, which reduces the performance of queries and connections and increases storage overhead. This is because the engine compares each character of the string in the processing of the query and the connection back, and it is enough for the numeric type to compare it once.

6. Reasonable use of exists,not exists clause. As shown below:
1.SELECT SUM (T1. C1) from T1 WHERE (
(SELECT COUNT (*) from T2 WHERE t2.c2=t1.c2>0)
2.SELECT SUM (T1. C1) from T1where EXISTS (
SELECT * from T2 WHERE T2. C2=t1. C2)
The two produce the same result, but the latter is obviously more efficient than the former. Because the latter does not produce a large number of locked table scans or index scans.
If you want to check whether there is a record in the table, do not use COUNT (*) as inefficient, and waste server resources. You can use exists instead. Such as:
IF (SELECT COUNT (*) from table_name WHERE column_name = ' xxx ')
Can be written as:
IF EXISTS (SELECT * FROM table_name WHERE column_name = ' xxx ')

It is often necessary to write a t_sql statement to compare a parent result set and a child result set to find whether there are records in the parent result set that do not exist in the child result set, such as:
1.SELECT A.hdr_key from Hdr_tbl a----tbl a represents TBL substituting alias a
Where not EXISTS (SELECT * from dtl_tbl b where A.hdr_key = B.hdr_key)

2.SELECT A.hdr_key from Hdr_tbl a
Left JOIN dtl_tbl b on a.hdr_key = B.hdr_key WHERE B.hdr_key is NULL

3.SELECT Hdr_key from Hdr_tbl
WHERE Hdr_key not in (SELECT Hdr_key from DTL_TBL)
The same correct results can be obtained in all three ways, but the efficiency is reduced in turn.

7, as far as possible to avoid in the indexed character data, using a non-beginning letter search. This also makes it impossible for the engine to take advantage of indexes.
See the following example:
SELECT * from T1 WHERE NAME like '%l% '
SELECT * from T1 WHERE substing (name,2,1) = ' L '
SELECT * from T1 WHERE NAME like ' l% '
Even if the Name field is indexed, the first two queries are still unable to use the index to speed up the operation, and the engine has to do a single operation on all of the data in the entire table. A third query can use indexes to speed up operations.

8, the use of the connection conditions, in some cases, two tables may be more than one connection between the conditions, then in the WHERE clause in the complete writing of the join conditions, it is possible to greatly improve the query speed.
Cases:
SELECT SUM (A.amount) from the account A,card B WHERE a.card_no = b.card_no
SELECT SUM (A.amount) from the account A,card B WHERE a.card_no = B.card_no and A.account_no=b.account_no
The second sentence would be much quicker than the first sentence.

9. Eliminate sequential access to large table row data
Although there are indexes on all of the check columns, some forms of the WHERE clause force the optimizer to use sequential access. Such as:
SELECT * FROM Orders WHERE (customer_num=104 and order_num>1001) OR
order_num=1008
Workaround you can use the and set to avoid sequential access:
SELECT * FROM Orders WHERE customer_num=104 and order_num>1001
UNION
SELECT * FROM Orders WHERE order_num=1008
This allows the query to be processed using the index path.

10. The normal expression of avoiding difficulty
The LIKE keyword supports wildcard matching, technically called regular expressions. But this kind of match is especially time-consuming. For example: SELECT * from the customer WHERE zipcode like "98_ _ _"
Even if an index is established on the ZipCode field, the sequential scan is used in this case. Such as
Change the statement to select * from Customer where zipcode > "98000", in the execution of the query
will use the index to query, obviously will greatly improve the speed.
11, use the view to speed up the query
Sorting a subset of a table and creating a view can sometimes speed up queries. It helps avoid multiple sorting
Operation, 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 this query is to be executed multiple times and more than once, all unpaid customers can be found and placed in a
View, and sort by the name of the customer:
CREATE VIEW DBO. V_cust_rcvlbes
As
SELECT cust.name,rcvbles.balance,......other Columns
From Cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
ORDER BY Cust.name

Then query the view in the following way:
SELECT * from V_cust_rcvlbes
WHERE postcode> "98000"
The rows in the view are less than the rows in the primary table, and the physical order is the required order, reducing the disk
I/O, so the query workload can be significantly reduced.

12, can use the between, do not use in
SELECT * from T1 WHERE ID in (10,11,12,13,14)
Change into:
SELECT * from T1 WHERE ID BETWEEN 14
Because in causes the system to not use the index, it can only search the data in the table directly.

13, distinct without GROUP by
SELECT OrderID from the Details WHERE UnitPrice > GROUP by OrderID
Could read:
SELECT DISTINCT OrderID from Details WHERE UnitPrice > 10


14. Partial Utilization Index
1.SELECT EmployeeID, FirstName, LastName
From names
WHERE dept = ' Prod ' or city = ' Orlando ' or division = ' food '

2.SELECT EmployeeID, FirstName, LastName from names WHERE dept = ' PROD '
UNION All
SELECT EmployeeID, FirstName, LastName from names WHERE city = ' Orlando '
UNION All
SELECT EmployeeID, FirstName, LastName from names WHERE Division = ' Food '
If the Dept column is indexed, query 2 can partially use the index, and query 1 cannot.

15, can use union all do not use union
UNION all does not perform the SELECT DISTINCT function, which reduces a lot of unnecessary resources

16, do not write some do not do anything in the query
For example, SELECT COL1 from T1 where 1=0
SELECT COL1 from T1 WHERE col1=1 and col1=2
This type of death code does not return any result sets, but consumes system resources.

17, try not to use SELECT into the statement.
A SELECT inot statement causes table locking and prevents other users from accessing the table.

18. Force the query optimizer to use an index if necessary
SELECT * from T1 WHERE nextprocess = 1 and ProcessID in (8,32,45)
Change into:
SELECT * from T1 (INDEX = ix_processid) WHERE nextprocess = 1 and ProcessID in (8,32,45)
The query optimizer will force the query to execute using the index IX_PROCESSID.

19, although the update, DELETE statements are basically fixed, but also to the UPDATE statement to give some suggestions:
(a) Try not to modify the primary key field.
b When modifying the varchar field, try to replace it with the value of the same length content.
(c) Minimize the update operations for tables containing update triggers.
D avoid the columns that update will replicate to other databases.
E Avoid the update of columns with many indexes.
f) Avoid the column in the WHERE clause condition of the update.


What we mentioned above is some basic considerations to improve query speed, but in more cases, it is often necessary to test and compare different statements to get the best solution. The best way is to test, of course, see the implementation of the same function of the SQL statement which has the fewest execution time, but if the data in the database is very small, is not come out, then you can view the execution plan, namely: the implementation of the same function of multiple SQL statements to Query Analyzer, according to Ctrl+l look at the use of the index, The number of table scans (both of which have the greatest performance impact), as a percentage of the total cost of the poll.
Simple stored procedures can be automatically generated using wizards: Click the Run Wizard icon on the Enterprise Manager toolbar, and click the database, Create Stored Procedure Wizard. Debugging of complex stored procedures: Object Browser on the left side of Query Analyzer (no?) Press F8) to select the stored procedures to debug, point right, point debugging, input parameters to execute, there is a floating tool bar, which has a single step, breakpoint settings.