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

转载 2007年09月26日 23:53:00

T-SQL Program Rule and Tips

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 EXISTSLEFT 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排序,特别在多用户的环境下.
  • 如果需要在一个包含JOINSELECT语句进行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进行分析.
 
  

相关文章推荐

T-SQL脚本优化技巧

1)对于SELECT/UPDATE语句必须显示的定义所有的列,避免使用星号。 2)在执行SELECT/INSERT/UPDATE/DELETE语句时,请考虑执行规划的重用,尽量考虑用SP-EXECUT...

SQL编程优化技巧

  • 2016-08-29 09:25
  • 1.20MB
  • 下载

T-SQL技巧收集——拆分字符串

在开发中,很多时候都需要处理拆分字符串的操作。下面收集了几种方法供大家分享,其中的逗号可以改为多种有需要的符号,但是不能针对多种符号同时存在的例子。有待各位补充: 说明: 将字符串转换为正规化的数...

t-sql使用技巧与注意事项

以下并非本人整理,但是看后感觉相当不错,特此分享。 1、应用程序中,保证在实现功能的基础上,尽量减少对数据库的访问次数;通过 搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负...

一些T-sql技巧

一、 只复制一个表结构,不复制数据                      &...

《Windows游戏编程大师技巧》 T3D游戏控制台程序实例及第一部分总结

现在我们要创建一个Windows外壳程序,将单调的Windows运行细节隐藏, Win32/DirectX编程就变得类似于32位DOS的编程过程。在构建好的代码结构下, 我们只需关心Game_*几...
  • dc_726
  • dc_726
  • 2012-04-22 15:20
  • 2543
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)