评价过滤运算符选择对T-SQL执行性能的影响机制

转载 2012年03月23日 01:20:44

评价过滤运算符选择对T-SQL执行性能的影响机制


  

介绍

  过滤操作对绝大多数复杂查询的编写来说非常重要。虽然在某些情况下你不需要用到过滤器,例如编写一个用来检索我国所有省份列表的简单查询就不需要;不过在大多数情况下,你需要过滤数据以便通过更精确的设定缩小查询范围。例如,当你需要一份以字母G开头的所有省份名单时,就得用到过滤机制。

  开发人员和数据库管理员有可以用各种运算符来执行这样的过滤操作。在决定使用何种运算符时需要优先考虑的问题是查询性能。可能有那么一些专家声称某些特定运算符要比别的好用,并提供一些经验法则之类的东西来指导您的选择。虽然这些经验法则大体而言还是不错的,不过对于任何的个别案例来说,都存在很多影响查询性能的因素,事实证明这些因素比一般法则更重要。对个别查询的用法和执行计划不加研究,而严格地按照经验规则来选择运算符,可能会导致查询达不到最佳性能。

  选择最佳运算符

  当我们将要为查询决定最佳运算符的选择时,首先要考虑将来该查询的使用情况。如果这个查询的使用频率很低,而且属于某个在活动不频繁时间才运行的进程的一部分(如深夜运行的SQL Server集成服务或数据转换服务包),那么长时间查询可能会比较合适。另一方面,如果可以运行该查询的时间很短,例如主营网上购物的商店,就需要对每项交易都执行一个查询,那么您可能会希望把时间花在获得最佳查询性能上,也就是说不仅要保证客户的体验,也要维护数据库服务器的稳定。

  我们还需要考虑如何为过滤取值,这些值又是从何处获得的。过滤值是单个值还是多个值?某些运算符能容纳的过滤值比其他运算符多。从另外一个表或子查询获得多个过滤值,而不是传递特定值,也可能会影响到你对运算符的选择。

  此外,当我们将要选择运算符时,还有一个重要因素需要考虑,那就是索引。如果某个适当的索引可用时,SQL Query Optimizer通常会用该索引去执行索引查找操作。如果没有可用索引,或索引不是最适合的,那数据库必须先执行表扫描或索引扫描,这样尤其会导致性能下降。不过,如果被查询的表包含的行比较少,那表扫描或索引扫描执行起来确实会比索引查找要快。当评价执行计划时,我们必须要考虑这些条件。

  评价执行计划

  类似于“EXISTS逻辑运算符比IN逻辑运算符更好用”这类大而化之的评价常常会左右我们的思维。在决定选择哪个运算符的时候,我们不能单纯地把某人的教条当圣经来用。更好的做法是评价执行计划,以便为数据库测试我们在其中编写的查询。执行计划为我们揭示是否了执行表扫描或索引扫描,并阐明该查询各步骤的相对性能情况。

  我们可以使用Enterprise Manager 中的Query Analyzer工具(SQL Server 2000)或者通过SQL Server Management Studio(SQL Server 2005)来查看执行计划。

  测试样本查询

  为了更好地阐明测试过程,本文将以AdventureWorks样本数据库为例,通过不同的变化设置来测试一个用来检索三个特定产品销售细节的查询。我们需要准备以下的测试环境:SQL Server 2005标准版、SQL Server Management Studio、SQL Server Profiler和AdventureWorks样本数据库。假设查询的结果会在一个网页应用程序的GridView对象中显示。当页面应用程序处于回传状态时,该GridView对象就会开始组建;因此,必须快速执行查询,而且执行的频率也要很高(假设这是一个访问率很高的网站)。

  本例用三个常数硬编码的ProductID值来过滤查询。

  ProductID = '762'

  ProductID = '754'

  ProductID = '770'

  虽然普遍的做法是最好避免使用编死的硬编码值,本例中使用这类值是为了隔离某些运算符对执行计划的影响,也就是任何来自和运算符本身无关的子查询的影响。

  本例用了两个表:SalesOrderDetail表和Product表,每个表都有若干指定的索引:

  Product表有四个索引:

  PK_Product_ProductID(通过主键建立的聚集索引)

  AK_Product_rowguid(唯一非聚集索引)

  AK_Product_ProductNumber(唯一非聚集索引)

  AK_Product_Name(唯一非聚集索引)

  SalesOrderDetail表有三个索引:


PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID(通过主键建立的聚集索引)

  IX_SalesOrderDetail_ProductID(非聚集索引)

  AK_SalesOrderDetail_rowguid(唯一非聚集索引)

  运算符类型

  用于过滤操作的运算符分为两类:比较运算符和逻辑运算符。

  比较运算符通过对两个值进行比较,确定这两个值是否满足条件。T-SQL中的比较运算符包括:

  等于(=)

  大于(>)

  小于(<)

  大于等于(>=)

  小于等于(<=)

  不等于(<>)

  逻辑运算符对条件进行判断,并返回一个布尔逻辑值,显示是否满足该条件。T-SQL中的逻辑运算符包括:

  ALL

  ANY

  BETWEEN

  EXISTS

  IN

  LIKE

  NOT

  SOME

  我们可以在复杂的过滤操作中同时运用比较运算符和逻辑运算符。当我们要使用多种运算符时,运算的顺序(运算符优先级)可能会影响查询的性能。T-SQL根据运算符的位置和类型来处理运算优先级:当使用的运算符都是相同类型(同是比较运算符)时,按照从左到右的顺序运算;当运算符属于不同类型时,比较运算符的优先级高于逻辑运算符。

  在接下来的例子当中,我们将评价四种最常用的运算符:等于比较运算符(=)和IN、 ANY、EXISTS三个逻辑运算符。

  等于比较运算符和IN逻辑运算符

  正如上面提到的,我们需要用一个查询来检索三个特定产品销售情况的详细资料。我们可以使用带等于比较运算符过滤的WHERE从句或带IN逻辑运算符过滤的WHERE从句来完成这个任务。

  比较运算符只能用来比较两个值。为了检测三个特定ProductID之间的等价关系,我们还需要用到OR运算符。下面就是使用了等于比较运算符的查询语句:

以下是引用片段:
  SELECT
  SalesOrderID,
  CarrierTrackingNumber,
  ProductID,
  UnitPrice
  FROM
  AdventureWorks.Sales.SalesOrderDetail
  WHERE
  (ProductID='762')or
  (ProductID='754')or
  (ProductID='770')

  相比之下,IN逻辑运算符使我们只需简单地使用一列自变量,也不需要使用OR或者AND:

以下是引用片段:
  SELECT
  SalesOrderID,
  CarrierTrackingNumber,
  ProductID,
  UnitPrice
  FROM
  AdventureWorks.Sales.SalesOrderDetail
  WHERE
  ProductIDIN('762','754','770')

  虽然本例中使用了确切值作为IN运算符的自变量,我们还可以通过子查询来获取自变量值。

  尽管两个查询语法区别非常明显,但是当在AdventureWorks数据库和这些特定表下运行时,这两个查询都生成相同的执行计划(见图一)。

  图一. 等于和IN运算符的执行计划:在本例的数据库环境下,这两个查询都生成了相同的执行计划。

  ANY和EXISTS逻辑运算符

  当参数中有任何一项满足了条件,那ANY逻辑运算符会返回一个true布尔值;而对于EXISTS逻辑运算符来说,如果其参数中的子查询返回任意行的话,那EXISTS运算符就会返回true值。测试这些运算符的性能要谨慎对待,因为其中使用的子查询可能会包含其他运算符,将直接影响到测试结果。为了尽量减少这方面的影响,下面的ANY和EXISTS查询实例都包含了一个使用等于比较运算符的子查询。

  下面是测试ANY逻辑运算符的查询语句:

以下是引用片段:
  SELECT
  SalesOrderID,
  CarrierTrackingNumber,
  ProductID,
  UnitPrice
  FROM
  AdventureWorks.Sales.SalesOrderDetail
  WHERE
  ProductID=ANY(
  SELECT
  ProductID
  FROM
  AdventureWorks.Production.Product
  WHERE
  (ProductID='762')OR
  (ProductID='754')OR
  (ProductID='770')
  )

  以及测试EXISTS逻辑运算符的查询语句:

以下是引用片段:
  SELECT
  a.SalesOrderID,
  a.CarrierTrackingNumber,
  a.ProductID,
  a.UnitPrice
  FROM
  AdventureWorks.Sales.SalesOrderDetaila
  WHERE
  EXISTS(
  SELECT
  b.ProductID
  FROM
  AdventureWorks.Production.Productb
  WHERE
  a.ProductID=b.ProductID
  AND(
  (b.ProductID='762')or
  (b.ProductID='754')or
  (b.ProductID='770')
  )
  )

相关文章推荐

T-SQL入門攻略之14-获取DML语句的影响信息

首先把上节没讲完的讲完: 多列更新 UPDATE testbook SET price = price * 0.3,--打折     remark = '过时图书' --备注 WHERE d...

影响SQL server性能的设计关键

  • 2011年08月11日 11:17
  • 15KB
  • 下载

数据库基础(二),t-sql(通配符、运算符、增删改查语句、重命名、排序)

T_SQL的组成 1.DML(数据操作语言)包含增insert、删delete、改update、查select等等 2.DCL(数据控制语言)用来控制存取许可,存取权限,包括有GRANT、REVO...

T-SQL概述(常量变量运算符)

每条SQL语句均由一个谓词(Verb)开始,谓词描述这条语句要产生的动作。谓词后接着一个或多个子句(Clause),子句中给出了被谓词作用的数据或提供谓词动作的详细信息,每条子句都由一个关键字开始。...

T-SQL程序设计之运算符与表达式

运算符是一种符号,用来指定在一个或多个表达式中执行的操作,SQL Server 2008的运算符有:算术运算符、位运算符、比较运算符、逻辑运算符、字符串连接运算符、赋值运算符、一元运算符等。 ---...
  • HK_5788
  • HK_5788
  • 2015年11月07日 19:37
  • 1122

js 解释器的尾调用优化机制 with 和 eval对性能的影响

前言今天浅谈一下js的解释器的尾调用优化机制,顺便提一下with 和 eval对其性能的影响。 eval和with语句都会对词法阶段的作用域产生影响。JavaScript引擎会在编译阶段进行数项的...

tempdb对SQL Server性能的影响

1.SQL Server系统数据库介绍 SQL Server有四个重要的系统级数据库:master,model,msdb,tempdb. master:记录SQL Server系统的所有系统级信息...

数据库中Sql语句,存储过程,触发器对性能影响的理解

相比inline sql,很多人更喜欢使用存储过程,为何?很多人会说:“因为存储过程是预编译并且缓存了的,因此比原生sql会更快” 这句话某个时候是正确的,先解释一下依据:sql第一次执行的时候...

影响SQL Server性能的主要原因

问:影响SQL Server性能的主要原因都有哪些?答:影响SQL Server性能的关键主要包括以下几个方面:第一方面:逻辑数据库和表的设计数据库的逻辑设计、包括表与表之间的关系是优化关系型数据库性...

XML之sql:column用法对性能影响

USE tempdb GO --Xml采用元素时,Xml文件比较小,用属性解析速度会相关较,通过查看执行计划可以 --Sql:column --元素 DECLARE @x XML SET @x= ...
  • roy_88
  • roy_88
  • 2012年12月31日 10:50
  • 2787
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:评价过滤运算符选择对T-SQL执行性能的影响机制
举报原因:
原因补充:

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