关闭

T-SQL 编程规范和优化技巧

611人阅读 评论(0) 收藏 举报

T-SQL 编程规范和优化技巧

1. 变量/函数/存储过程的命名(Variable/UDF/Stored Procedure Naming)

 
2. T-SQL 编码标准
·        一个嵌套代码块中的语句使用四个空格的缩进。(上述代码中的多行 SELECT 语句是一个 SQL 语句。)在同一语句中开始新行时,使 SQL 关键字右对齐。将代码编辑器配置为使用空格,而不是使用制表符。这样,不管使用何种程序查看代码,格式都是一致的。
·        大写所有的 T-SQL 关键字,包括 T-SQL 函数。变量名称及光标名称使用混和大小写。数据类型使用小写。
·        表名别名要简短,但意义要尽量明确。通常,使用大写的表名作为别名,使用 AS 关键字指定表或字段的别名。
·        当一个 T-SQL 语句中涉及到多个表时,始终使用表名别名来限定字段名。这使其他人阅读起来更清楚,避免了含义模糊的引用。
·        当相关数字出现在连续的代码行中时(例如一系列 SUBSTRING 函数调用),将它们排成列。这样容易浏览数字列表。
·        使用一个(而不是两个)空行分隔 T-SQL 代码的逻辑块,只要需要就可以使用。
·        声明 T-SQL 局部变量(例如 @lngTableID)时,使用适当的数据类型声明和一致的大写。
·        始终指定字符数据类型的长度,并确保允许用户可能需要的最大字符数,因为超出最大长度的字符会丢失。
·        始终指定十进制数据类型的精度和范围,否则,将默认为未指定精度和整数范围。
·        使用错误处理程序,但要记住行首 (BOL) 中的错误检查示例不会象介绍的那样起作用。用来检查 @@ERROR 系统函数的 T-SQL 语句 (IF) 实际上在进程中清除了 @@ERROR 值,无法再捕获除零之外的任何值。(即使示例起作用,它们也只能捕获最后发生的一个错误,而不是您更想捕获的第一个错误。)必须使用 SET 或 SELECT 立即捕获错误代码,如前面示例所示。如果状态变量仍然为零,应转换到状态变量。
·        避免使用“未声明的”功能,例如系统表中未声明的列、T-SQL 语句中未声明的功能或者未声明的系统存储过程或扩展的存储过程。
·        不要依 赖任何隐式的数据类型转换。例如,不能为数字变量赋予字符值,而假定 T-SQL 会进行必要的转换。相反,在为变量赋值或比较值之前,应使用适当的 CONVERT 函数使数据类型相匹配。另一个示例:虽然 T-SQL 会在进行比较之前对字符表达式进行隐式且自动的 RTRIM,但不能依赖此行为,因为兼容性级别设置非字符表达式会使情况复杂化。
·        不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空,应使用 IS NULL 或 IS NOT NULL 进行比较,或者使用 ISNULL 函数。
·        不要使用 STR 函数进行舍入,此函数只能用于整数。如果需要十进制值的字符串形式,应先使用 CONVERT 函数(转至不同的范围)或 ROUND 函数,然后将其转换为字符串。也可以使用 CEILING 和 FLOOR 函数。
·        使用数学公式时要小心,因为 T-SQL 可能会将表达式强制理解为一个不需要的数据类型。如果需要十进制结果,应在整数常量后加点和零 (.0)。
·        不要在 T-SQL 代码中使用双引号。应为字符常量使用单引号。如果没有必要限定对象名称,可以使用(非 ANSI SQL 标准)括号将名称括起来。
 
Header Sample Comments
/*****************************************************************
** File: usp_InsertEquipments.sql
** Name: usp_InsertEquipments
** Desc: Insert new equipment number and type in equipment table
**
** Return Values: ErrorCode1: Descr
**                          ErrorCode2: Descr
**
** Call by: Application Module Name
**
** Parameters:
** Input                    Output                             
** ------------          --------------
** @inPara1          @outPara1
** @inPara2
**
** Auth:
** Date:
** ****************************************************************
** Date                   Author                         Description
** ---------           -------------                    ------------------
** 2006-3-31          Jack                            Fixed: 49 Error handing
*******************************************************************/
 
 数据流程控制的范例
    /*******************************************************
     ** Process description
   ********************************************************/
Begin
      Insert Order (OrderDate, RequestedById,
                           TargetDate, DestinationLocation)
      Values (@OrderDate, @ContractId,
                    @TargetDate, @LocId)
Set  @ErrorCode=@@error
If @ErrorCode<>0
Begin
        RaiseError (‘Error occurred while inserting Order!’,16,1)
        Return @ErrorCode
End
End
 
  • 避免使用 GOTO,错误处理程序中除外
  • IF …Else的嵌套的级数不要超过4级
  • 避免使用游标,如果使用必须显示声明游标的类型,避免使用动态游标.采用批次提交,避免每条都进行提交.                   
 
3. 变量
  • 不能像表列那样定义变量是否为空,这并不是说变量不可以为空,在给没一个变量赋值之前都是空(NULL),所有的输入参数必须进行有效性检查.
  • 对于不同数据类型的赋值,必须进行显示的转换(CONVERT(),CAST())和异常判断.
  • 使用SET对变量赋值,不使用SELECT.
  • 通过全局变量来控制错误和事务,比如@@ERROR,@@ROWCOUNT
 
4.  T-SQL脚本优化技巧
     
  • 对于SELECT/UPDATE语句必须显示的定义所有的列,避免使用星号.
  • 在执行SELECT/INSERT/UPDATE/DELETE语句时,请考虑执行规划的重用,考虑用SP-EXECUTESQL存储过程
  • 优先使用 SELECT...INTO,然后使用 INSERT...SELECT,以避免大量死锁
  • 如果需要删除所有的数据,用TRUNCATE TABLE 代替DELETE
  • 避免使用DISTINCT 语句
  • 如果你需要有限的记录,通过TOP N代替SET ROWCOUNT来控制排序取值.
  • 避免使用SARGABLE的语句在WHERE子句,比如: OR, <>, !=, !<, >!, IS NULL, NOT, NOT IN, NOT LIKE 和LIKE,因为这些操作很难利用已知的索引.
  • 避免使用NOT IN,可以采用IN,EXISTS NOT EXISTS和LEFT JOIN 加空值判断
    --NOT EXISTS, 效率最高
            SELECT a.hdr_key
           FROM hdr_tbl a
            WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
   --LEFT JOIN
           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
   --NOT IN ,效率最低
          SELECT hdr_key
          FROM hdr_tbl
          WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
  •      使用EXISTS判断记录是否存在.
     --不好的写法:
        IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
  正确的写法:   --
          IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
 
  • 避免在GROUP BY中使用HAVING 语句
  • GROUP BY的语句要尽量简单,不要进行GROUP BY语句的嵌套,避免在GROUP BY中包含多余的列
  • 考虑在GROUP BY的列,进行ORDER BY排序,特别在多用户的环境下.
  • 如果需要在一个包含JOIN的SELECT语句进行GROUP BY,请考虑用子查询代替JOIN. 如果必须使用GROUP BY, GROUP BY 的应该列在同一张表.
  • 如果WHERE条件语句有多个AND条件,请确保至少有一个列有索引,如果没有可以建立多列复合INDEX.
  • 对于SQL 无法执行自动优化的WHERE条件语句,可以通过HINTS显示的制定INDEX来提高查询的效率
   --可能不好的写法:
         SELECT * FROM tblTaskProcesses
WHERE    nextprocess = 1
    AND processid IN (8,32,45)
   --正确的写法:
     SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID)
WHERE    nextprocess = 1
AND processid IN (8,32,45)
             
  • 尽可能避免在WHERE条件语句中使用函数计算,
   --不好的写法:
         WHERE SUBSTRING(firstname,1,1) = 'm'
   --正确的写法:
         WHERE firstname like 'm%'
  • 在WHERE条件语句中,避免在函数中包列,如果无法避免,请考虑在该列建立INDEX
  --不好的写法:
          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())
  • 在WHERE条件语句中,避免使用NOT
   --不好的写法:
           WHERE NOT column_name > 5
   --正确的写法:
           WHERE column_name <= 5
  • 在WHERE条件语句中,推荐使用10位的日前函数.
  --正确的写法:
         SELECT *
        FROM Northwind.dbo.Orders
        WHERE OrderDate > '12/31/1997'
--不好的写法:
      SELECT *
        FROM Northwind.dbo.Orders
        WHERE OrderDate > '12/31/97'
 
  • 避免使用UNION,而是用UNION ALL
  • 使用 SQL-92 标准连接句法,为了提高性能,应优先使用连接,然后使用子查询或嵌套查询,表之间的连接使用INNER JOIN,LEFT JOIN 和RIGHT JOIN,不使用CROSS JOIN和多列表方式.
  • 多表关联避免超过5个,可以通过临时表(表变量),简化复杂的关联.
 
5. 存储过程的开发和优化技巧
  • 避免使用触发器TRIGGER,考虑用存储过程代替触发器
   --与临时表一样,光标并不是不可使用。对小型数据集使用FAST_FORWARD 光标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括合计的例程通常要比使用光标执行的速度快。如果开发时间允许,基于光标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好.
  • 考虑用UDF代替存储过程
 --使用表值 UDF 时要小心,因为在变量(而不是常量)中传递某个参数时,如果在 WHERE 子句中使用该参数,会导致表扫描。还要避免在一个查询中多次使用相同的表值 UDF。但是,表值 UDF 确实具有某些非常方便的动态编译功能
  • 对于频繁调用的存储过程,考虑用SP_RECOMPILE重新编译
  • 使用输出语句代替返回整个数据集,输出语句的执行效率会更加高效 
  • 在存储过程的头部使用SET NOCOUNT ON, 通过@@ROWCOUNT来控制,这样可以减少网络流量和避免潜在的问题, 而在结束时设置 SET NOCOUNT OFF.
  • 不使用SP_作为存储过程的名称,建议用USP_,这个会影响数据库的执行时间.
  • 尽可能使用临时表而不使用临时表,表变量可以减少上锁和重新编译的次数并且表变量不使用TEMPDB的空间,而是全部使用内存来处理数据.
  • 先在例程中创建临时表,最后再显式删除临时表。将 DDL 与 DML 语句混合使用有助于处理额外的重新编译活动
  • 尽可能不要在流程控制语句中使用临时表,比如:IF .. ELSE, WHILE
  • 避免在事务中进行赋值和复杂计算,
  •  
--不好的写法:
          Create procedure proc_1
            As
            Begin
                       Begin transaction
                        -- step 1 verify the data
                        -- step 2 perform calculations
                       -- step 3 get default variable values (date, user info)
                       -- update/insert records
                       commit
            end
  --不好的写法:
            Create procedure proc_1
            As
            Begin
                       -- step 1 verify the data
                       -- step 2 make calculations
                       -- step 3 get default variable values (date, user info)
                       Begin transaction
                        -- update/insert records
                       commit
            end
 
6. 补充说明
  • 上面的一些优化规则只是一般原则,在某些特殊情况下可能会有所差别,如果需要分析T-SQL的性能,可以通过查询分析器的CTRL+L 显示执行规划进行分析,也可以通过 SET STATISTICS PROFILE ON进行分析.



Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=647283

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:8076次
    • 积分:132
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:9篇
    • 译文:0篇
    • 评论:0条
    常用连接