数据库的优化 1、 用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集合的SQL语句需要更大的开销；按照特定顺序提取数据的查找。2、 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如: SELECT name FROM employee WHERE salary ＞ 60000 在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。 3、 尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ 应改为: SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 应改为: SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE()) 即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。4、 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != 'B%' 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
5、 尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段 设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。6、 合理使用EXISTS,NOT EXISTS子句。如下所示： 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) 两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。 如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如： IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') 可以写成： IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如： 1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名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) 三种写法都可以得到同样正确的结果，但是效率依次降低。7、 尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。 见如下例子： SELECT * FROM T1 WHERE NAME LIKE ‘%L%’ SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ SELECT * FROM T1 WHERE NAME LIKE ‘L%’ 即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。8、 分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。 例： 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 第二句将比第一句执行快得多。9、 消除对大型表行数据的顺序存取 尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。如： SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 解决办法可以使用并集来避免顺序存取： SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT ＊ FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。10、 避免困难的正规表达式 LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT * FROM customer WHERE zipcode LIKE “98_ _ _” 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如 果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询 时就会利用索引来查询，显然会大大提高速度。 11、 使用视图加速查询 把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序 操作，而且在其他方面还能简化优化器的工作。例如： 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 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个 视图中，并按客户的名字进行排序： 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 然后以下面的方式在视图中查询： SELECT ＊ FROM V_CUST_RCVLBES WHERE postcode>“98000” 视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘 I/O，所以查询工作量可以得到大幅减少。12、 能够用BETWEEN的就不要用IN SELECT * FROM T1 WHERE ID IN (10,11,12,13,14) 改成： SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14 因为IN会使系统无法使用索引,而只能直接搜索表中的数据。13、 DISTINCT的就不用GROUP BY SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID 可改为： SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10 14、 部分利用索引 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' 如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。 15、 能用UNION ALL就不要用UNION UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源16、 不要写一些不做任何事的查询 如：SELECT COL1 FROM T1 WHERE 1=0 SELECT COL1 FROM T1 WHERE COL1=1 AND COL1=2 这类死码不会返回任何结果集，但是会消耗系统资源。17、 尽量不要用SELECT INTO语句。 SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。18、 必要时强制查询优化器使用某个索引 SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改成： SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45) 则查询优化器将会强行利用索引IX_ProcessID 执行查询。
19、 虽然UPDATE、DELETE语句的写法基本固定，但是还是对UPDATE语句给点建议： a) 尽量不要修改主键字段。 b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。 c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。 d) 避免UPDATE将要复制到其他数据库的列。 e) 避免UPDATE建有很多索引的列。 f) 避免UPDATE在WHERE子句条件中的列。上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试，看实现相同功能的SQL语句哪个执行时间最少，但是数据库中如果数据量很少，是比较不出来的，这时可以用查看执行计划，即：把实现相同功能的多条SQL语句考到查询分析器，按CTRL+L看查所利用的索引，表扫描次数（这两个对性能影响最大），总体上看询成本百分比即可。 简单的存储过程可以用向导自动生成：在企业管理器工具栏点击运行向导图标，点击”数据库”、”创建存储过程向导”。复杂存储过程的调试：在查询分析器左边的对象浏览器（没有？按F8）选择要调试的存储过程,点右键，点调试，输入参数执行，出现一个浮动工具条，上面有单步执行，断点设置等。
一、不合理的索引设计 ----例：表record有620000行，试看在不同的索引下，下面几个 SQL的运行情况： ---- 1.在date上建有一非个群集索引 select count(*) from record where date > '19991201' and date < '19991214'and amount > 2000 (25秒) select date,sum(amount) from record group by date (55秒) select count(*) from record where date > '19990901' and place in ('BJ','SH') (27秒) ---- 分析： ----date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在 范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。 ---- 2.在date上的一个群集索引 select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000 （14秒） select date,sum(amount) from record group by date （28秒） select count(*) from record where date > '19990901' and place in ('BJ','SH')（14秒） ---- 分析： ---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范 围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范 围扫描，提高了查询速度。 ---- 3.在place，date，amount上的组合索引 select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000 （26秒） select date,sum(amount) from record group by date （27秒） select count(*) from record where date > '19990901' and place in ('BJ, 'SH')（ < 1秒） ---- 分析： ---- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引 用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组 合索引中，形成了索引覆盖，所以它的速度是非常快的。 ---- 4.在date，place，amount上的组合索引 select count(*) from record where date > '19991201' and date < '19991214' and amount > 2000( < 1秒) select date,sum(amount) from record group by date （11秒） select count(*) from record where date > '19990901' and place in ('BJ','SH')（ < 1秒） ---- 分析： ---- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并 且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。 ---- 5.总结： ---- 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要 建立在对各种查询的分析和预测上。一般来说： ---- ①.有大量重复值、且经常有范围查询 （between, >, < ，>=, < =）和order by 、group by发生的列，可考虑建立群集索引； ---- ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引； ---- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 二、不充份的连接条件： ---- 例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在 account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况： select sum(a.amount) from account a, card b where a.card_no = b.card_no（20秒） ---- 将SQL改为： select sum(a.amount) from account a, card b where a.card_no = b.card_no and a. account_no=b.account_no（ < 1秒） ---- 分析： ---- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用 card上的索引，其I/O次数可由以下公式估算为： ---- 外层表account上的22541页+（外层表account的191122行*内层表card上对应外层 表第一行所要查找的3页）=595907次I/O ---- 在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用 account上的索引，其I/O次数可由以下公式估算为： ---- 外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一 行所要查找的4页）= 33528次I/O ---- 可见，只有充份的连接条件，真正的最佳方案才会被执行。 ---- 总结： ---- 1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方 案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的 表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘 积最小为最佳方案。 ---- 2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连 接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,30 2)。 三、不可优化的where子句 ---- 1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢： select * from record where substring(card_no,1,4)='5378'(13秒) select * from record where amount/30 < 1000（11秒） select * from record where convert(char(10),date,112)='19991201'（10秒） ---- 分析： ---- where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不 进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么 就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样： select * from record where card_no like '5378%'（ < 1秒） select * from record where amount < 1000*30（ < 1秒） select * from record where date= '1999/12/01' （ < 1秒） ---- 你会发现SQL明显快起来！ ---- 2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL： select count(*) from stuff where id_no in('0','1') （23秒） ---- 分析： ---- where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化 为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果 相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略" ，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉 重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完 成时间还要受tempdb数据库性能的影响。 ---- 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时 间竟达到220秒！还不如将or子句分开： select count(*) from stuff where id_no='0' select count(*) from stuff where id_no='1' ---- 得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒， 在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程： create proc count_stuff as declare @a int declare @b int declare @c int declare @d char(10) begin select @a=count(*) from stuff where id_no='0' select @b=count(*) from stuff where id_no='1' end select @c=@a+@b select @d=convert(char(10),@c) print @d ---- 直接算出结果，执行时间同上面一样快！ ---- 总结： ---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。 ---- 1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时 要尽可能将操作移至等号右边。 ---- 2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把 子句拆开；拆开的子句中应该包含索引。 ---- 3.要善于使用存储过程，它使SQL变得更加灵活和高效。 ---- 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可 以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实S QL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会 涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。1、要合理使用索引 索引是数据库一个重要的构成部分，很多人都会忽略它，其实索引的根本目的就是 为了提高查询效率。 使用原则如下： 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则 由优化器自动生成索引。 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要 建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就 无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度 。 如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。 在写sql语句时就必须注意有些写法是会使得数据库无法使用索引的，比如IS NULL IS NOT NULL，IN ，NOT IN 等 2．避免或简化排序 应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生 输出时，优化器就避免了排序的步骤。以下是一些影响因素： ●索引中不包括一个或几个待排序的列； ●group by或order by子句中列的次序与索引的次序不一样； ●排序的列来自不同的表。 为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可 能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应 当试图简化它，如缩小排序的列的范围等。 3．消除对大型表行数据的顺序存取 在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存 取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10 亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学 生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要 做连接，就要在“学号”这个连接字段上建立索引。 还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的 where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使 用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该 改为如下语句： SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT ＊ FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。 4．避免相关子查询 一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中 的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此 应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的 行。 5．避免困难的正规表达式 MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗 费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _” 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如 果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询 时就会利用索引来查询，显然会大大提高速度。 另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非开始子串，因而这个语句也不会 使用索引。 6．使用临时表加速查询,SQL2000中还可以使用表变量来代替临时表 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序 操作，而且在其他方面还能简化优化器的工作。例如： 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 如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个 临时文件中，并按客户的名字进行排序： 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 INTO TEMP cust_with_balance 然后以下面的方式在临时表中查询： SELECT ＊ FROM cust_with_balance WHERE postcode>“98000” 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘 I/O，所以查询工作量可以得到大幅减少。 注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意 不要丢失数据。 7．用排序来取代非顺序存取 非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这 一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。 有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。 如果你不太明白SQL语句的执行过的话. 记住这样的原则. 1. 对字段的计算会引起全表扫描. 所以,能用: select * from 表 where 字段=1 就不要用: select * from 表 where 字段-1=0 2. 必要的索引对提高数据处理速度很重要.因此,对于经常要排序,进行条件比较的字段,要建立索引.(注意区分复合索引和单独索引) 3. 善于利用存储过程/视图,化繁为简 4. 对于不能确定效率的查询分析语句,将它复制到查询分析器中.按Ctrl+L进行分析. 看看它的执行要经过那些步骤. 每个步骤的执行时间大慨需要占用的时间百分比. 在进行表扫描的步骤中,是否利用上了你创建的索引. 如果还有其他查询方法,再分析其他查询方法,通过比较确定最优的查询方法. 查询优化建议 有些查询天生就消耗大量资源。这与基本的数据库和索引问题有关。这些查询的效率并不低，因为查询优化器会以最有效的可能方式实现这些查询。然而，它们确实消耗大量资源，而且 Transact-SQL 面向集合的性质使这些查询看起来效率很低。查询优化器的智能水平无法消除这些构造的固有资源成本。与不复杂的查询相比，这些查询的固有成本十分昂贵。虽然 Microsoft? SQL Server? 2000 使用最佳的访问计划，但受到基础构造可能性的限制。例如，下列类型的查询可能占用大量资源： 返回大结果集的查询 高度不唯一的 WHERE 子句 不过有一些针对优化查询和提高查询性能的建议，其中包括： 添加更多的内存（尤其是如果服务器运行许多复杂查询而且其中几个查询执行很慢） 在有多个处理器的计算机上运行 SQL Server。多个处理器使 SQL Server 得以利用并行查询。有关更多信息，请参见并行查询处理。 考虑重新编写查询。 如果查询使用游标，则确定如果使用效率更高的游标类型（如快速只进游标）或单纯查询能否更有效地编写游标查询。单纯查询的性能一般优于游标操作。一组游标语句通常是一个外循环操作，在此操作中，一旦使用内部语句便开始处理外循环内的每行，因此可考虑使用 GROUP BY 或 CASE 语句或改为使用子查询。 如果应用程序使用循环，可考虑在查询内放入循环。应用程序常包含带参数化查询的循环，该循环执行许多次并要求运行应用程序的计算机与 SQL Server 之间有网络往返。可改为使用临时表创建一个更复杂的查询。只需提供一个网络往返，查询优化器即会更好地优化这个查询。 不要对同一查询内的单个表使用多个别名以模拟索引交叉。模拟索引交叉已没有必要，因为 SQL Server 会自动考虑索引交叉并且可以在同一查询内的相同表上使用多个索引。 只在必要时才使用查询提示。若查询使用在 SQL Server 早期版本上执行的提示，则应在不指定提示的情况下对该查询进行测试。提示会防碍查询优化器选择更好的执行计划。有关更多信息，请参见 SELECT。 利用 query governor 配置选项和设置。可以使用 query governor 配置选项阻止执行长时间运行的查询，从而防止消耗系统资源。默认情况下，query governor 配置选项允许执行所有查询，而不考虑查询所需的时间。然而，可以将查询调控器设置到最大秒数，以允许执行所有连接的所有查询或只允许执行特定连接的查询。查询调控器基于估计的查询成本而不是实际的已用时间，因此没有任何运行时开销。它还在长时间运行的查询开始前便将其停止，而不是先运行这些查询直到达到某些预定义的限制为止。
非常感谢gogodiy。很详细，很好的例子。 期待更多的高手来分享自己的经验啊。
调优博客：http://www.cnblogs.com/Amaranthus/ --以下内容为引用 近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现，有很多似是而非或者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以前的经验和测试结果进行总结了。我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。一、分析阶段一 般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能 是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。二、设计阶段设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。以下是性能要求设计阶段需要注意的：1、 数据库逻辑设计的规范化数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。第2规范 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。第3规范 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。2、 合理的冗余完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。3、 主键的设计主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。4、 外键的设计外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作 了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好 其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。5、 字段的设计字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：A、数据类型尽量用数字型，数字型的比较比字符型的快很多。B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。E、 自增字段要慎用，不利于数据迁移。6、 数据库物理存储和环境的设计在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。这里需要注意文件组的作用，适用文件组可以有效把IO操作分散到不同的物理硬盘，提高并发能力。7、 系统设计整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择CS结构、三层的CS结构等，不同的系统结构其性能的关键也有所不同。系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。8、 索引的设计在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。关于索引的选择，应改主意：A、 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。B、 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。C、 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。D、 一个表不要加太多索引，因为索引影响插入和更新的速度。三、 编码阶段编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。下面罗列一些编程阶段需要注意的事项：1、 只返回需要的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A、横向来看，不要写SELECT 的语句，而是选择你需要的字段。B、 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。D、对于聚合查询，可以用HAVING子句进一步限定返回的行。2、 尽量少做重复的工作这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：A、 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。B、 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。C、 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。D、 合并对同一表同一条件的多次UPDATE，比如UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’ UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’ 这两个语句应该合并成以下一个语句UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’ E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。F、 不要写一些没有意义的查询，比如SELECT FROM EMPLOYEE WHERE 1=23、 注意事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：A、事务操作过程要尽量小，能拆分的事务要拆分开来。B、 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。C、 事务操作过程要按同一顺序访问对象。D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。4、 注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、其他情况下，应该控制临时表和表变量的使用。E、 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。G、 注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。5、 子查询的用法子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。关于相关子查询，应该注意：A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = ’BUSINESS’) 可以改写成：SELECT A.PUB_NAME FROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = ’BUSINESS’ AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL SELECT TITLE FROM TITLES WHERE NOT EXISTS (SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID) 可以改写成：SELECT TITLE FROM TITLES LEFT JOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_ID WHERE SALES.TITLE_ID IS NULL B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID IN (SELECT PUB_ID FROM TITLES WHERE TYPE = ’BUSINESS’) 可以改写成：SELECT DISTINCT A.PUB_NAME FROM PUBLISHERS A INNER JOIN TITLES B ON B.TYPE = ’BUSINESS’ AND A.PUB_ID=B. PUB_ID C、 IN的相关子查询用EXISTS代替，比如SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID IN (SELECT PUB_ID FROM TITLES WHERE TYPE = ’BUSINESS’) 可以用下面语句代替：SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS (SELECT 1 FROM TITLES WHERE TYPE = ’BUSINESS’ AND PUB_ID= PUBLISHERS.PUB_ID) D、不要用COUNT()的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：SELECT JOB_DESC FROM JOBS WHERE (SELECT COUNT() FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 应该改成：SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE ON EMPLOYEE.JOB_ID=JOBS.JOB_ID WHERE EMPLOYEE.EMP_ID IS NULL SELECT JOB_DESC FROM JOBS WHERE (SELECT COUNT() FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 应该改成：SELECT JOB_DESC FROM JOBS WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID) 6、 慎用游标数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。A、字符串连接的例子这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：DECLARE @NAME VARCHAR(20) DECLARE @NAME VARCHAR(1000) DECLARE NAME_CURSOR CURSOR FOR SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID OPEN NAME_CURSOR FETCH NEXT FROM RNAME_CURSOR INTO @NAME WHILE @@FETCH_STATUS = 0 BEGIN SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME FETCH NEXT FROM NAME_CURSOR INTO @NAME END CLOSE NAME_CURSOR DEALLOCATE NAME_CURSOR 可以如下修改，功能相同：DECLARE @NAME VARCHAR(1000) SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID B、 用CASE WHEN 实现转换的例子很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如表结构CREATE TABLE 料件表( 料号 VARCHAR(30), 名称 VARCHAR(100), 主单位 VARCHAR(20), 单位1 VARCHAR(20), 单位1参数 NUMERIC(18,4), 单位2 VARCHAR(20), 单位2参数 NUMERIC(18,4) ) GO CREATE TABLE 入库表( 时间 DATETIME, 料号 VARCHAR(30), 单位 INT, 入库数量 NUMERIC(18,4), 损坏数量 NUMERIC(18,4) ) GO 其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：DECLARE @料号 VARCHAR(30), @单位 INT, @参数 NUMERIC(18,4), DECLARE CUR CURSOR FOR SELECT 料号,单位 FROM 入库表 WHERE 单位 0 OPEN CUR FETCH NEXT FROM CUR INTO @料号,@单位 WHILE @@FETCH_STATUS-1 BEGIN IF @单位=1 BEGIN SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) UPDATE 入库表 SET 数量=数量@参数,损坏数量=损坏数量@参数,单位=1 WHERE CURRENT OF CUR END IF @单位=2 BEGIN SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号) UPDATE 入库表 SET 数量=数量@参数,损坏数量=损坏数量@参数,单位=1 WHERE CURRENT OF CUR END FETCH NEXT FROM CUR INTO @料号,@单位 END CLOSE CUR DEALLOCATE CUR 可以改写成：UPDATE A SET 数量=CASE A.单位 WHEN 1 THEN A.数量B. 单位1参数 WHEN 2 THEN A.数量B. 单位2参数 ELSE A.数量 END, 损坏数量= CASE A.单位 WHEN 1 THEN A. 损坏数量B. 单位1参数 WHEN 2 THEN A. 损坏数量B. 单位2参数 ELSE A. 损坏数量 END, 单位=1 FROM入库表 A, 料件表 B WHERE A.单位1 AND A.料号=B.料号 C、 变量参与的UPDATE语句的例子SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：在SELECT A,B,C,CAST(NULL AS INT) AS 序号 INTO #T FROM 表 ORDER BY A ,NEWID() 产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：DECLARE @A INT DECLARE @序号 INT UPDATE #T SET @序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END, @A=A, 序号=@序号 D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。
7、 尽量使用索引建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。为了使得优化器能高效使用索引，写语句的时候应该注意：A、不要对索引字段进行运算，而要想办法做变换，比如SELECT ID FROM T WHERE NUM2=100应改为SELECT ID FROM T WHERE NUM=1002SELECT ID FROM T WHERE NUM2=NUM1如果NUM有索引应改为SELECT ID FROM T WHERE NUM=NUM12如果NUM1有索引则不应该改。发现过这样的语句：SELECT 年,月,金额 FROM 结余表 WHERE 100年+月=2007100+10 应该改为：SELECT 年,月,金额 FROM 结余表 WHERE 年=2007 AND 月=10 B、 不要对索引字段进行格式转换日期字段的例子：WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’应该改为WHERE日期字段〉=’2008-08-15’ AND 日期字段’2008-08-16’ISNULL转换的例子：WHERE ISNULL(字段,’’)’’应改为WHERE字段’’WHERE ISNULL(字段,’’)=’’不应修改WHERE ISNULL(字段,’F’) =’T’应改为 WHERE字段=’T’WHERE ISNULL(字段,’F’)’T’不应修改C、 不要对索引字段使用函数WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’应改为WHERE NAME LIKE ‘ABC%’日期查询的例子：WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期 =’2005-11-30′ AND 日期 ’2005-12-1‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)0应改为WHERE 日期 ’2005-11-30‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期 ’2005-12-01‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)0应改为WHERE 日期=’2005-12-01‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期=’2005-11-30‘D、不要对索引字段进行多字段连接比如：WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’应改为WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’8、 注意连接条件的写法多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。B、 连接条件尽量使用聚集索引C、 注意ON部分条件和WHERE部分条件的区别9、 其他需要注意的地方经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：A、程序员注意、关心各表的数据量。B、 编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。C、 每个SQL语句尽量简单D、不要频繁更新有触发器的表的数据E、 注意数据库函数的限制以及其性能10、 学会分辩SQL语句的优劣自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。A、 查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优化的余地。也可以用语句SET SHOWPLAN_ALL ON要执行的语句SET SHOWPLAN_ALL OFF查看执行计划的文本详细信息。B、 用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及IO数据，从而分析语句的效率。C、 可以用WINDOWS的系统性能检测器，关注CPU、IO参数四、 测试、试运行、维护阶段测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。主要进行语句优化、索引优化等。试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：A、 可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。B、 可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。C、 可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。D、五、 网上资料中一些说法的个人不同意见1、 “应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT ID FROM T WHERE NUM IS NULL可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：SELECT ID FROM T WHERE NUM=0”个人意见：经过测试，IS NULL也是可以用INDEX SEEK查找的，0和NULL是不同概念的，以上说法的两个查询的意义和记录数是不同的。2、 “应尽量避免在 WHERE 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。”个人意见：经过测试，也是可以用INDEX SEEK查找的。3、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT ID FROM T WHERE NUM=10 OR NUM=20可以这样查询：SELECT ID FROM T WHERE NUM=10UNION ALLSELECT ID FROM T WHERE NUM=20”个人意见：主要对全表扫描的说法不赞同。4、 “IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：SELECT ID FROM T WHERE NUM IN(1,2,3)对于连续的数值，能用 BETWEEN 就不要用 IN 了：SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”个人意见：主要对全表扫描的说法不赞同。5、 “如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：SELECT ID FROM T WHERE NUM=@NUM可以改为强制查询使用索引：SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”个人意见：关于局部变量的解释比较奇怪，使用参数如果会影响性能，那存储过程就该校除了，我坚持我上面对于强制索引的看法。6、 “尽可能的使用 VARCHARNVARCHAR 代替 CHARNCHAR ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。”个人意见：“在一个相对较小的字段内搜索效率显然要高些”显然是对的，但是字段的长短似乎不是由变不变长决定，而是业务本身决定。在SQLSERVER6.5或者之前版本，不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多，所以对于那些版本，我们都是推荐使用定长字段存储一些关键字段。而在2000版本，修改了不定长字符串字段的比较方法，与定长字段的比较速度差别不大了，这样为了方便，我们大量使用不定长字段。7、 关于连接表的顺序或者条件的顺序的说法，经过测试，在SQL SERVER，这些顺序都是不影响性能的，这些说法可能是对ORACLE有效。
1、 用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集合的SQL语句需要更大的开销；按照特定顺序提取数据的查找。2、 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary ＞ 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。 3、 尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。4、 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如:
SELECT id FROM employee WHERE id != 'B%'
优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
5、 尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段
设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。6、 合理使用EXISTS,NOT EXISTS子句。如下所示：
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)
两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成：
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：
1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名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)
三种写法都可以得到同样正确的结果，但是效率依次降低。7、 尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子：
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。8、 分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。
例：
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
第二句将比第一句执行快得多。9、 消除对大型表行数据的顺序存取
尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。如：
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR
order_num=1008
解决办法可以使用并集来避免顺序存取：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。10、 避免困难的正规表达式
LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如
果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询
时就会利用索引来查询，显然会大大提高速度。
11、 使用视图加速查询
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序
操作，而且在其他方面还能简化优化器的工作。例如：
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
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个
视图中，并按客户的名字进行排序：
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 然后以下面的方式在视图中查询：
SELECT ＊ FROM V_CUST_RCVLBES
WHERE postcode>“98000”
视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘
I/O，所以查询工作量可以得到大幅减少。12、 能够用BETWEEN的就不要用IN
SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)
改成：
SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14
因为IN会使系统无法使用索引,而只能直接搜索表中的数据。13、 DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
14、 部分利用索引
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'
如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。
15、 能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源16、 不要写一些不做任何事的查询
如：SELECT COL1 FROM T1 WHERE 1=0
SELECT COL1 FROM T1 WHERE COL1=1 AND COL1=2
这类死码不会返回任何结果集，但是会消耗系统资源。17、 尽量不要用SELECT INTO语句。
SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。18、 必要时强制查询优化器使用某个索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45)
改成：
SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)
则查询优化器将会强行利用索引IX_ProcessID 执行查询。
19、 虽然UPDATE、DELETE语句的写法基本固定，但是还是对UPDATE语句给点建议：
a) 尽量不要修改主键字段。
b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d) 避免UPDATE将要复制到其他数据库的列。
e) 避免UPDATE建有很多索引的列。
f) 避免UPDATE在WHERE子句条件中的列。上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试，看实现相同功能的SQL语句哪个执行时间最少，但是数据库中如果数据量很少，是比较不出来的，这时可以用查看执行计划，即：把实现相同功能的多条SQL语句考到查询分析器，按CTRL+L看查所利用的索引，表扫描次数（这两个对性能影响最大），总体上看询成本百分比即可。
简单的存储过程可以用向导自动生成：在企业管理器工具栏点击运行向导图标，点击”数据库”、”创建存储过程向导”。复杂存储过程的调试：在查询分析器左边的对象浏览器（没有？按F8）选择要调试的存储过程,点右键，点调试，输入参数执行，出现一个浮动工具条，上面有单步执行，断点设置等。
----例：表record有620000行，试看在不同的索引下，下面几个 SQL的运行情况：
---- 1.在date上建有一非个群集索引
select count(*) from record where date >
'19991201' and date < '19991214'and amount >
2000 (25秒)
select date,sum(amount) from record group by date
(55秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH') (27秒)
---- 分析：
----date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在
范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。
---- 2.在date上的一个群集索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （14秒）
select date,sum(amount) from record group by date
（28秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（14秒）
---- 分析：
---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范
围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范
围扫描，提高了查询速度。
---- 3.在place，date，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （26秒）
select date,sum(amount) from record group by date
（27秒）
select count(*) from record where date >
'19990901' and place in ('BJ, 'SH')（ < 1秒）
---- 分析：
---- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引
用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组
合索引中，形成了索引覆盖，所以它的速度是非常快的。
---- 4.在date，place，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000( < 1秒)
select date,sum(amount) from record group by date
（11秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（ < 1秒）
---- 分析：
---- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并
且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。
---- 5.总结：
---- 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要
建立在对各种查询的分析和预测上。一般来说：
---- ①.有大量重复值、且经常有范围查询
（between, >, < ，>=, < =）和order by
、group by发生的列，可考虑建立群集索引；
---- ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
---- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 二、不充份的连接条件：
---- 例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在
account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况： select sum(a.amount) from account a,
card b where a.card_no = b.card_no（20秒）
---- 将SQL改为：
select sum(a.amount) from account a,
card b where a.card_no = b.card_no and a.
account_no=b.account_no（ < 1秒）
---- 分析：
---- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用
card上的索引，其I/O次数可由以下公式估算为：
---- 外层表account上的22541页+（外层表account的191122行*内层表card上对应外层
表第一行所要查找的3页）=595907次I/O
---- 在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用
account上的索引，其I/O次数可由以下公式估算为：
---- 外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一
行所要查找的4页）= 33528次I/O
---- 可见，只有充份的连接条件，真正的最佳方案才会被执行。
---- 总结：
---- 1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方
案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的
表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘
积最小为最佳方案。
---- 2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连
接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,30
2)。
三、不可优化的where子句
---- 1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
select * from record where
substring(card_no,1,4)='5378'(13秒)
select * from record where
amount/30 < 1000（11秒）
select * from record where
convert(char(10),date,112)='19991201'（10秒）
---- 分析：
---- where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不
进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么
就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select * from record where card_no like
'5378%'（ < 1秒）
select * from record where amount
< 1000*30（ < 1秒）
select * from record where date= '1999/12/01'
（ < 1秒）
---- 你会发现SQL明显快起来！
---- 2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：
select count(*) from stuff where id_no in('0','1')
（23秒）
---- 分析：
---- where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化
为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果
相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"
，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉
重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完
成时间还要受tempdb数据库性能的影响。
---- 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时
间竟达到220秒！还不如将or子句分开：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
---- 得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，
在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d
---- 直接算出结果，执行时间同上面一样快！
---- 总结：
---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。 ---- 1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时
要尽可能将操作移至等号右边。
---- 2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把
子句拆开；拆开的子句中应该包含索引。
---- 3.要善于使用存储过程，它使SQL变得更加灵活和高效。
---- 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可
以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实S
QL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会
涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。1、要合理使用索引
索引是数据库一个重要的构成部分，很多人都会忽略它，其实索引的根本目的就是
为了提高查询效率。
使用原则如下：
在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则
由优化器自动生成索引。 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要
建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就
无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
。
如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。 在写sql语句时就必须注意有些写法是会使得数据库无法使用索引的，比如IS NULL
IS NOT NULL，IN ，NOT IN 等 2．避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生
输出时，优化器就避免了排序的步骤。以下是一些影响因素：
●索引中不包括一个或几个待排序的列；
●group by或order by子句中列的次序与索引的次序不一样；
●排序的列来自不同的表。
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可
能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应
当试图简化它，如缩小排序的列的范围等。 3．消除对大型表行数据的顺序存取
在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存
取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10
亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学
生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要
做连接，就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的
where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作
order_num=1008
虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使
用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该
改为如下语句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。 4．避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中
的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此
应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的
行。 5．避免困难的正规表达式
MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗
费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如
果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询
时就会利用索引来查询，显然会大大提高速度。
另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE
zipcode[2，3] >“80”，在where子句中采用了非开始子串，因而这个语句也不会
使用索引。 6．使用临时表加速查询,SQL2000中还可以使用表变量来代替临时表
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序
操作，而且在其他方面还能简化优化器的工作。例如：
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
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个
临时文件中，并按客户的名字进行排序：
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
INTO TEMP cust_with_balance
然后以下面的方式在临时表中查询：
SELECT ＊ FROM cust_with_balance
WHERE postcode>“98000”
临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘
I/O，所以查询工作量可以得到大幅减少。
注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意
不要丢失数据。 7．用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这
一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。
有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。 如果你不太明白SQL语句的执行过的话. 记住这样的原则.
1.
对字段的计算会引起全表扫描.
所以,能用:
select * from 表 where 字段=1
就不要用:
select * from 表 where 字段-1=0 2.
必要的索引对提高数据处理速度很重要.因此,对于经常要排序,进行条件比较的字段,要建立索引.(注意区分复合索引和单独索引)
3.
善于利用存储过程/视图,化繁为简
4.
对于不能确定效率的查询分析语句,将它复制到查询分析器中.按Ctrl+L进行分析.
看看它的执行要经过那些步骤.
每个步骤的执行时间大慨需要占用的时间百分比.
在进行表扫描的步骤中,是否利用上了你创建的索引.
如果还有其他查询方法,再分析其他查询方法,通过比较确定最优的查询方法. 查询优化建议
有些查询天生就消耗大量资源。这与基本的数据库和索引问题有关。这些查询的效率并不低，因为查询优化器会以最有效的可能方式实现这些查询。然而，它们确实消耗大量资源，而且 Transact-SQL 面向集合的性质使这些查询看起来效率很低。查询优化器的智能水平无法消除这些构造的固有资源成本。与不复杂的查询相比，这些查询的固有成本十分昂贵。虽然 Microsoft? SQL Server? 2000 使用最佳的访问计划，但受到基础构造可能性的限制。例如，下列类型的查询可能占用大量资源：
返回大结果集的查询 高度不唯一的 WHERE 子句
不过有一些针对优化查询和提高查询性能的建议，其中包括：
添加更多的内存（尤其是如果服务器运行许多复杂查询而且其中几个查询执行很慢） 在有多个处理器的计算机上运行 SQL Server。多个处理器使 SQL Server 得以利用并行查询。有关更多信息，请参见并行查询处理。
考虑重新编写查询。
如果查询使用游标，则确定如果使用效率更高的游标类型（如快速只进游标）或单纯查询能否更有效地编写游标查询。单纯查询的性能一般优于游标操作。一组游标语句通常是一个外循环操作，在此操作中，一旦使用内部语句便开始处理外循环内的每行，因此可考虑使用 GROUP BY 或 CASE 语句或改为使用子查询。
如果应用程序使用循环，可考虑在查询内放入循环。应用程序常包含带参数化查询的循环，该循环执行许多次并要求运行应用程序的计算机与 SQL Server 之间有网络往返。可改为使用临时表创建一个更复杂的查询。只需提供一个网络往返，查询优化器即会更好地优化这个查询。
不要对同一查询内的单个表使用多个别名以模拟索引交叉。模拟索引交叉已没有必要，因为 SQL Server 会自动考虑索引交叉并且可以在同一查询内的相同表上使用多个索引。 只在必要时才使用查询提示。若查询使用在 SQL Server 早期版本上执行的提示，则应在不指定提示的情况下对该查询进行测试。提示会防碍查询优化器选择更好的执行计划。有关更多信息，请参见 SELECT。
利用 query governor 配置选项和设置。可以使用 query governor 配置选项阻止执行长时间运行的查询，从而防止消耗系统资源。默认情况下，query governor 配置选项允许执行所有查询，而不考虑查询所需的时间。然而，可以将查询调控器设置到最大秒数，以允许执行所有连接的所有查询或只允许执行特定连接的查询。查询调控器基于估计的查询成本而不是实际的已用时间，因此没有任何运行时开销。它还在长时间运行的查询开始前便将其停止，而不是先运行这些查询直到达到某些预定义的限制为止。
期待更多的高手来分享自己的经验啊。
--以下内容为引用
近期因工作需要，希望比较全面的总结下SQL SERVER数据库性能优化相关的注意事项，在网上搜索了一下,发现很多文章,有的都列出了上百条,但是仔细看发现，有很多似是而非或者过时(可能对SQL SERVER6.5以前的版本或者ORACLE是适用的)的信息，只好自己根据以前的经验和测试结果进行总结了。我始终认为，一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。所以我希望按照软件生命周期的不同阶段来总结数据库性能优化相关的注意事项。一、分析阶段一 般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能 是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。二、设计阶段设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。以下是性能要求设计阶段需要注意的：1、 数据库逻辑设计的规范化数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。第2规范 每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。第3规范 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。2、 合理的冗余完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。3、 主键的设计主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。4、 外键的设计外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作 了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好 其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。5、 字段的设计字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：A、数据类型尽量用数字型，数字型的比较比字符型的快很多。B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。E、 自增字段要慎用，不利于数据迁移。6、 数据库物理存储和环境的设计在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。这里需要注意文件组的作用，适用文件组可以有效把IO操作分散到不同的物理硬盘，提高并发能力。7、 系统设计整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择CS结构、三层的CS结构等，不同的系统结构其性能的关键也有所不同。系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。8、 索引的设计在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。关于索引的选择，应改主意：A、 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。B、 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。C、 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。D、 一个表不要加太多索引，因为索引影响插入和更新的速度。三、 编码阶段编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。下面罗列一些编程阶段需要注意的事项：1、 只返回需要的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A、横向来看，不要写SELECT 的语句，而是选择你需要的字段。B、 纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。C、 注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。D、对于聚合查询，可以用HAVING子句进一步限定返回的行。2、 尽量少做重复的工作这一点和上一点的目的是一样的，就是尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：A、 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。B、 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。C、 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。D、 合并对同一表同一条件的多次UPDATE，比如UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’
这两个语句应该合并成以下一个语句UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’
WHERE EMP_ID=’ VPA30890F’
E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。F、 不要写一些没有意义的查询，比如SELECT FROM EMPLOYEE WHERE 1=23、 注意事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：A、事务操作过程要尽量小，能拆分的事务要拆分开来。B、 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。C、 事务操作过程要按同一顺序访问对象。D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。4、 注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、其他情况下，应该控制临时表和表变量的使用。E、 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。G、 注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题。5、 子查询的用法子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。关于相关子查询，应该注意：A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID NOT IN
(SELECT PUB_ID
FROM TITLES
WHERE TYPE = ’BUSINESS’)
可以改写成：SELECT A.PUB_NAME
FROM PUBLISHERS A LEFT JOIN TITLES B
ON B.TYPE = ’BUSINESS’ AND
A.PUB_ID=B. PUB_ID
WHERE B.PUB_ID IS NULL SELECT TITLE
FROM TITLES
WHERE NOT EXISTS
(SELECT TITLE_ID
FROM SALES
WHERE TITLE_ID = TITLES.TITLE_ID)
可以改写成：SELECT TITLE
FROM TITLES LEFT JOIN SALES
ON SALES.TITLE_ID = TITLES.TITLE_ID
WHERE SALES.TITLE_ID IS NULL
B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
(SELECT PUB_ID
FROM TITLES
WHERE TYPE = ’BUSINESS’)
可以改写成：SELECT DISTINCT A.PUB_NAME
FROM PUBLISHERS A INNER JOIN TITLES B
ON B.TYPE = ’BUSINESS’ AND
A.PUB_ID=B. PUB_ID
C、 IN的相关子查询用EXISTS代替，比如SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
(SELECT PUB_ID
FROM TITLES
WHERE TYPE = ’BUSINESS’)
可以用下面语句代替：SELECT PUB_NAME
FROM PUBLISHERS
WHERE EXISTS
(SELECT 1
FROM TITLES
WHERE TYPE = ’BUSINESS’ AND
PUB_ID= PUBLISHERS.PUB_ID)
D、不要用COUNT()的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT() FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
应该改成：SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
WHERE EMPLOYEE.EMP_ID IS NULL
SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT() FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
应该改成：SELECT JOB_DESC FROM JOBS
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)
6、 慎用游标数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。另外，我们可以用SQL SERVER的一些特性来代替游标，达到提高速度的目的。A、字符串连接的例子这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把JOB_ID=10的EMPLOYEE的FNAME连接在一起，用逗号连接，可能最容易想到的是用游标：DECLARE @NAME VARCHAR(20)
DECLARE @NAME VARCHAR(1000)
DECLARE NAME_CURSOR CURSOR FOR
SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID
OPEN NAME_CURSOR
FETCH NEXT FROM RNAME_CURSOR INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME
FETCH NEXT FROM NAME_CURSOR INTO @NAME
END
CLOSE NAME_CURSOR
DEALLOCATE NAME_CURSOR
可以如下修改，功能相同：DECLARE @NAME VARCHAR(1000)
SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME
FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID
B、 用CASE WHEN 实现转换的例子很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用CASE WHEN语句进行必要的判断处理，而且CASE WHEN是可以嵌套的。比如表结构CREATE TABLE 料件表(
料号 VARCHAR(30),
名称 VARCHAR(100),
主单位 VARCHAR(20),
单位1 VARCHAR(20),
单位1参数 NUMERIC(18,4),
单位2 VARCHAR(20),
单位2参数 NUMERIC(18,4)
)
GO
CREATE TABLE 入库表(
时间 DATETIME,
料号 VARCHAR(30),
单位 INT,
入库数量 NUMERIC(18,4),
损坏数量 NUMERIC(18,4)
)
GO
其中，单位字段可以是0，1，2，分别代表主单位、单位1、单位2，很多计算需要统一单位，统一单位可以用游标实现：DECLARE @料号 VARCHAR(30),
@单位 INT,
@参数 NUMERIC(18,4),
DECLARE CUR CURSOR FOR
SELECT 料号,单位 FROM 入库表 WHERE 单位 0
OPEN CUR
FETCH NEXT FROM CUR INTO @料号,@单位
WHILE @@FETCH_STATUS-1
BEGIN
IF @单位=1
BEGIN
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)
UPDATE 入库表 SET 数量=数量@参数,损坏数量=损坏数量@参数,单位=1 WHERE CURRENT OF CUR
END
IF @单位=2
BEGIN
SET @参数=(SELECT 单位1参数 FROM 料件表 WHERE 料号 =@料号)
UPDATE 入库表 SET 数量=数量@参数,损坏数量=损坏数量@参数,单位=1 WHERE CURRENT OF CUR
END
FETCH NEXT FROM CUR INTO @料号,@单位
END
CLOSE CUR
DEALLOCATE CUR
可以改写成：UPDATE A SET
数量=CASE A.单位 WHEN 1 THEN A.数量B. 单位1参数
WHEN 2 THEN A.数量B. 单位2参数
ELSE A.数量
END,
损坏数量= CASE A.单位 WHEN 1 THEN A. 损坏数量B. 单位1参数
WHEN 2 THEN A. 损坏数量B. 单位2参数
ELSE A. 损坏数量
END,
单位=1
FROM入库表 A, 料件表 B
WHERE A.单位1 AND
A.料号=B.料号
C、 变量参与的UPDATE语句的例子SQL ERVER的语句比较灵活，变量参与的UPDATE语句可以实现一些游标一样的功能，比如：在SELECT A,B,C,CAST(NULL AS INT) AS 序号
INTO #T
FROM 表
ORDER BY A ,NEWID()
产生临时表后，已经按照A字段排序，但是在A相同的情况下是乱序的，这时如果需要更改序号字段为按照A字段分组的记录序号，就只有游标和变量参与的UPDATE语句可以实现了，这个变量参与的UPDATE语句如下：DECLARE @A INT
DECLARE @序号 INT
UPDATE #T SET
@序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END,
@A=A,
序号=@序号
D、如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项FAST_FORWARD），一般只需要静态游标（选项STATIC）。E、 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。
7、 尽量使用索引建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。为了使得优化器能高效使用索引，写语句的时候应该注意：A、不要对索引字段进行运算，而要想办法做变换，比如SELECT ID FROM T WHERE NUM2=100应改为SELECT ID FROM T WHERE NUM=1002SELECT ID FROM T WHERE NUM2=NUM1如果NUM有索引应改为SELECT ID FROM T WHERE NUM=NUM12如果NUM1有索引则不应该改。发现过这样的语句：SELECT 年,月,金额 FROM 结余表
WHERE 100年+月=2007100+10
应该改为：SELECT 年,月,金额 FROM 结余表
WHERE 年=2007 AND
月=10
B、 不要对索引字段进行格式转换日期字段的例子：WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’应该改为WHERE日期字段〉=’2008-08-15’ AND 日期字段’2008-08-16’ISNULL转换的例子：WHERE ISNULL(字段,’’)’’应改为WHERE字段’’WHERE ISNULL(字段,’’)=’’不应修改WHERE ISNULL(字段,’F’) =’T’应改为 WHERE字段=’T’WHERE ISNULL(字段,’F’)’T’不应修改C、 不要对索引字段使用函数WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’应改为WHERE NAME LIKE ‘ABC%’日期查询的例子：WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期 =’2005-11-30′ AND 日期 ’2005-12-1‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)0应改为WHERE 日期 ’2005-11-30‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期 ’2005-12-01‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)0应改为WHERE 日期=’2005-12-01‘WHERE DATEDIFF(DAY, 日期,’2005-11-30′)=0应改为WHERE 日期=’2005-11-30‘D、不要对索引字段进行多字段连接比如：WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’应改为WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’8、 注意连接条件的写法多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别的注意。A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。B、 连接条件尽量使用聚集索引C、 注意ON部分条件和WHERE部分条件的区别9、 其他需要注意的地方经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：A、程序员注意、关心各表的数据量。B、 编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。C、 每个SQL语句尽量简单D、不要频繁更新有触发器的表的数据E、 注意数据库函数的限制以及其性能10、 学会分辩SQL语句的优劣自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。A、 查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优化的余地。也可以用语句SET SHOWPLAN_ALL ON要执行的语句SET SHOWPLAN_ALL OFF查看执行计划的文本详细信息。B、 用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及IO数据，从而分析语句的效率。C、 可以用WINDOWS的系统性能检测器，关注CPU、IO参数四、 测试、试运行、维护阶段测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。主要进行语句优化、索引优化等。试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：A、 可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。B、 可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。C、 可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。D、五、 网上资料中一些说法的个人不同意见1、 “应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT ID FROM T WHERE NUM IS NULL可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：SELECT ID FROM T WHERE NUM=0”个人意见：经过测试，IS NULL也是可以用INDEX SEEK查找的，0和NULL是不同概念的，以上说法的两个查询的意义和记录数是不同的。2、 “应尽量避免在 WHERE 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。”个人意见：经过测试，也是可以用INDEX SEEK查找的。3、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT ID FROM T WHERE NUM=10 OR NUM=20可以这样查询：SELECT ID FROM T WHERE NUM=10UNION ALLSELECT ID FROM T WHERE NUM=20”个人意见：主要对全表扫描的说法不赞同。4、 “IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：SELECT ID FROM T WHERE NUM IN(1,2,3)对于连续的数值，能用 BETWEEN 就不要用 IN 了：SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”个人意见：主要对全表扫描的说法不赞同。5、 “如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：SELECT ID FROM T WHERE NUM=@NUM可以改为强制查询使用索引：SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”个人意见：关于局部变量的解释比较奇怪，使用参数如果会影响性能，那存储过程就该校除了，我坚持我上面对于强制索引的看法。6、 “尽可能的使用 VARCHARNVARCHAR 代替 CHARNCHAR ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。”个人意见：“在一个相对较小的字段内搜索效率显然要高些”显然是对的，但是字段的长短似乎不是由变不变长决定，而是业务本身决定。在SQLSERVER6.5或者之前版本，不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多，所以对于那些版本，我们都是推荐使用定长字段存储一些关键字段。而在2000版本，修改了不定长字符串字段的比较方法，与定长字段的比较速度差别不大了，这样为了方便，我们大量使用不定长字段。7、 关于连接表的顺序或者条件的顺序的说法，经过测试，在SQL SERVER，这些顺序都是不影响性能的，这些说法可能是对ORACLE有效。
还有，就是查询调优，不仅仅是一个参数，一个设置的问题。这个是一个动态的过程。当前的调整，仅对当前一段时间内有效。调节之后，会产生后续的问题。需要实际操作者，进行分析控制。