1. In the program, ensure that the number of access to the database is guaranteed, and the number of access to the database is minimized. Try to minimize the number of access to the table, minimize the result set, thereby alleviating the network burden; Separate, improve each response speed; when the data window uses SQL, try to put the used index in the selected first column; the structure of the algorithm is as simple as possible; when the query is query, do not use wildcards such as SELECT * from T1 Socks, choose several columns to choose several columns such as: select col1, col2 from T1; may limit as much as possible, as much as possible, as possible, as possible, such as: SELECT TOP 300 COL1, COL2, COL3 from T1, because in some cases Users do not need so much data. Do not use the database cursor in your application, the cursor is a very useful tool, but more overhead is required than using conventional, viewed SQL statements; to extract data in a specific order.
2. Avoid using incompatible data types. For example, Float and Int, CHAR and VARCHAR, BINARY and VARBINARY are incompatible. The incompatibility of data types may enable optimizers to perform some optimized operations that can be performed. For example: SELECT NAME FROM EMPLOYEE WHERE SALE SALE> 60000 In this statement, such as a SALARY field is Money type, the optimizer is difficult to optimize because 60000 is an integer. We should transform the integer when programming, not to wait until running.
3. Try to avoid functions or expression operations in the WHERE clause, which will cause the engine to discard the use of the index to perform full mete scan. Such as: SELECT * from T1 Where F1 / 2 = 100 should be changed: SELECT * from T1 Where F1 = 100 * 2
Select * from record where substring (CARD_NO, 1, 4) = '5378' should be changed to: select * from record where card_no like '5378%
SELECT member_number, first_name, last_name FROM membersWHERE DATEDIFF (yy, datofbirth, GETDATE ())> 21 should read: SELECT member_number, first_name, last_name FROM membersWHERE dateofbirth 4. Avoid use! = Or <>, is Null or IS Not Null, IN, NOT IN, etc., because this will not use the index without using the index, but can only search the data in the table. For example: Select ID from Employee WHERE ID! = 'B%' optimizer will not be able to determine the number of rows that will hit by index, so you need to search all rows of the table. 5. Try to use digital fields, some developers and database managers like to design fields containing numeric information as characters, which reduces the performance of queries and connectivity, and increases storage overhead. This is because the engine compares the query and connection back to each character in the string, and for digital models, it is enough to compare it. 6. Rational use the exists, NOT EXISTS clause. As shown below: 1.Select Sum (t1.c1) from T1 where (*) 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 have the same result, but the latter efficiency is obviously higher than the former. Because the latter does not generate a large number of lock-up table scans or index scans. If you want to school There is a record in the table, don't use COUNT (*) to be very efficient, and waste server resources. You can replace it with exists. Such as: if (select count (*) from table_name where color = 'xxx') can be written : If exists (Select * from table_name where color ") often needs to write a T_SQL statement to compare a parent result set and sub-result set, so that it is found in the collected records in the parent results, such as: 1.Select a.hdr_key from HDR_TBL A ---- TBL A indicates that TBL uses alias A instead where not exists (SELECT * from DTL_TBL B where a.hdr_key = B.HDR_Key) 2.Select a.hdr_key from HDR_TBL ALEFT JOIN DTL_TBL B on a.hdr_key = b.hdr_key where b.hdr_key is null 3.Select HDR_Key from HDR_TBLWHERE HDR_KEY NOT IN (SELECT HDR_KEY From DTL_TBL) three kinds of writing can get the same correct result, but efficiency is sequentially reduced. 7. Try to avoid using non-header search in the indexed character data. This also makes the engine unreasonable index. 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 has an index The first two queries still cannot use the index to complete the speed of operation, the engine has to complete the task on all the data of the full table. And the third query can use the index to speed up the operation. 8. Dividing the connection conditions, in some case, there may be no connection conditions between the two tables, at which time the connection condition is complete in the WHERE clause, it is possible to greatly improve the query speed. Example: Select SUM (A.Amount) from Account A, Card B Where A.Card_no = B.Card_no Select Sum (A.Amount) from Account A, Card B Where A.Card_no = B.Card_no and a.account_no = B .Account_no The second sentence will be much more faster than the first sentence. 9. Eliminate the order of large table line data although there is index on all check columns, some form of WHERE clause is forced optimizer to use sequential access. Such as: SELECT * FROM orders WHERE (customer_num = 104 AND order_num> 1001) OR order_num = 1008 set of solutions may be used, and to avoid the sequential access: SELECT * FROM orders WHERE customer_num = 104 AND order_num> 1001 UNION SELECT * FROM orders WHERE ORDER_NUM = 1008 This can use the index path to process queries. 10. Avoiding a formal expression like keyword supports wildcard matching, a regular expression. But this match is particularly time consuming. For example: SELECT * from Customer WHERE ZIPCODE LIKE "98_ _ _" Even in this case, in this case, it is also possible to scan in order. If the statement is changed to SELECT * from customer where zipcode> "98000", you will use the index to query when you execute the query, obviously greatly improves the speed. 11. Use view to speed up the query to sort a subset of the table and create a view, sometimes accelerated query. It helps to avoid multiple sorting operations and simplify the work of optimizer in other ways. 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 to Multiple times, more than once, you can find all unpaid customers in a view and sort by the customer's name: create view dbo.v_cust_rcvlbesas select custom.name, rcvbles.balance, ...... Other columns from Cust, rcvbles where custom.customer_id = rcvlbes.customer_id and rcvblls.balance> 0 Order by custom.name Then query in the view in the following manner: SELECT * FROM V_CUST_RCVLBESwhere Postcode> "98000" view is less than the routine in the primary table, and the physical order is required, and the disk I / O is reduced, so query Workload can be greatly reduced. 12, can be changed with INSELECT * from T1 WHERE ID in (10, 11, 12, 13, 14) with Between: Select * from t1 where id betWeen 10 and 14 because I will make the system unused index, and Can only search the data in the table. 13. DISTINCT No GROUP BY Select ORDERID from Details Where Unitprice> 10 Group by ORDERID can be changed to: Select Distinct ORDERID from Details Where Unitprice> 10 14, using the index portion 1.SELECT employeeID, firstname, lastnameFROM namesWHERE dept = 'prod' or city = 'Orlando' or division = 'food'2.SELECT employeeID, firstname, lastname FROM names WHERE dept =' prod'UNION ALLSELECT employeeID FirstName, Lastname from names where city = 'orlando'Union allselect employeeid, firstselect employeeid, firstselect employeeid, firstname, Lastname from names where division =' food 'If the DEPT list has an index, Query 2 can partially use the index, Query 1 cannot be. 15. If you can use the Union All without performing the Select Distinct function, this will reduce a lot of unnecessary resources. 16, don't write some queries that don't do anything, such as: select col1 from t1 where 1 = 0 Select col1 from t1 where col1 = 1 and col1 = 2 This type of dead code does not return any result set, but will consume system resources. 17. Try not to use the SELECT INTO statement. The Select INOT statement will cause the table to lock and prevent other users from accessing the table. 18. If necessary, forced query optimizer uses an index Select * from t1 where nextprocess = 1 and processid in (8, 32, 45) modified: select * from t1 (index = ix_processid) where nextprocess = 1 and processid in ( 8, 32, 45) The query optimizer will force the inquiry of the index ix_processid. 19. Although the write method of Update, the delete statement is basically fixed, but it is also a suggestion for the UPDATE statement: a) Try not to modify the primary key field. b) When modifying a VARCHAR type field, try to use the value of the same length content. c) Try to minimize UPDATE operations for tables containing the UPDATE trigger. d) Avoid UPDATE columns to be copied to other databases. e) Avoid columns with many indexes of Update. f) Avoid columns in the WHERE clause conditions. Above we mentioned that some basic improving query speeds, but in more cases, it is often necessary to repeated test compare different statements to obtain the best solution. The best way is of course test, which is the least time of implementation of the SQL statement that realizes the same function, but if the amount of data is small in the database, it is more comparable, then you can use the implementation plan, ie: A plurality of SQL statements are taken to the query analyzer, press Ctrl L to see the index used, and the number of scans (these two have the greatest impact), and the percentage of performance is percentage. Simple stored procedures can be automatically generated by a wizard: Click the Run Wizard icon in the Enterprise Manager toolbar, click on "Database", "Create Store Wizard". Commissioning of complex stored procedures: Select the stored procedure of the query analyzer (not? Press F8) to select the stored procedure to debug, right-click, point debug, input parameter execution, a floating tool strip, there is a single step execution, Breakpoint settings, etc.
