如何提高数据库的查询效率（How to improve the query efficiency of database）.docVIP

如何提高数据库的查询效率（How to improve the query efficiency of database） Reprint - how to improve the efficiency of database queries? 1, use Profiler to track queries, get the time needed to query, find the SQL problem, and index optimizer to optimize the index 2, pay attention to the difference between UNion and UNion all. UNION all okay 3, pay attention to the use of DISTINCT, when necessary, do not use, and it will slow down the query as UNION. Duplicate records are no problem in the query 4, do not return rows and columns that are not needed when querying 5, use sp_configure,query, governor, cost, limit, or SET QUERY_GOVERNOR_COST_LIMIT to limit query consuming resources. When evaluating the resource consumed by the query exceeds the limit, the server automatically cancels the query and kills it before the query. SET LOCKTIME sets the lock time 6, use select top 100 / 10 Percent to limit the number of rows returned by the user or SET ROWCOUNT to restrict the rows of the operation 7, before SQL2000, generally do not use the following words: IS NULL, , ! = ! , !, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE%500, because they not all table scan is cited ropewalking. Not in the words WHere column name and function, such as Convert, substring and so on, if must use function, create a calculated column to create index instead. Can also be accommodated written: WHERE SUBSTRING (firstname, 1,1) =mfirstname likem% to WHERE (index scan), must function and listing separate. And indexes cant be built too much and too big. NOT IN will repeatedly scan the table, using EXISTS, NOT EXISTS, IN, LEFT OUTER to replace JOIN, especially the left connection, while the Exists is faster than IN, NOT is the slowest operation. If the value of the column contains a null, before its index doesnt work, now 2000 of the optimizer can handle the. The same is the IS NULL, NOT, NOT EXISTS, NOT IN can optimize her, and or not optimization, use index. 8, use Query Analyzer to see if the query plan and eval